【mysql】存储过程事务未提交/回滚，导致应用异常

时间:2015-09-01 04:09:38      作者:beebol      标签: commit deadlock procedure rollback      分类: mysql

由于XX情况，上线一个应用，平时很少访问数据库，只有特定的时候存在访问。每次进行这个应用测试的时候，很大概率会产生DB死锁，导致业务异常。经后面检查发现是由于存储过程中，执行insert失败退出，没有提交，也没有回滚。导致其它应用访问这个记录时，出现Lock wait timeout exceeded; try restarting transaction。

如下是具体的模拟线上情况：

CREATE TABLE `test` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test values(1,10035,1003501,0);
insert into test values(2,10035,1003502,1);
insert into test values(3,10035,1003503,2);
insert into test values(4,10035,1003504,3);
insert into test values(5,10035,1003505,4);
insert into test values(6,10035,1003506,5);

-- 创建一个存储过程进行insert
delimiter $$
drop procedure if exists save_test$$
create procedure save_test(nb int,nc int,nd int)
begin
    start transaction;
    insert into test (b,c,d)values(nb,nc,nd);
    select 'insert over';
    commit;
end$$delimiter ;
delimiter ;

session  1:

start transaction;insert into test (b,c,d)values(10035,1003509,0);
insert into test (b,c,d)values(10035,1003509,0);

session 2:

call save_test(10035,1003509,1);

session 3:

call save_test(10035,1003509,2);

看到这种情况，都会第一感觉到deadlock很常会出现的，这次不讨论deadlock，关于insert死锁的情况可以参考下官方的文档“http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html”，这里主要讨论存储过程中出现死锁后的情况。

这三个session执行的三个语句，在session 1没有提交或回滚时，session 2 和session 3都在等待S锁,当session 1提交后，其中session 2 和3都会插入失败，报唯一键冲突，插入失败，存储过程失败退出，此时事务还没有未提交，也没有回滚。

问题来了，由于insert报唯一键冲突后，还获得了一个S锁，并没有释放，因为此时的事务并没有提交。此时，如果有其它session访问这条记录，就会导致锁等待，一值如此。直到session 2和session 3提交、回滚，这是做不到的，另外一种情况是新的事务开始，就会提交这个事务。

Trx id counter 2847
Purge done for trx's n:o < 2844 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6, OS thread handle 0x41c93940, query id 43 localhost root init
show engine innodb status
---TRANSACTION 2844, not started
MySQL thread id 1, OS thread handle 0x407e9940, query id 42 localhost root cleaning up
---TRANSACTION 2846, ACTIVE 55 sec
2 lock struct(s), heap size 360, 1 row lock(s)   #这里看，还锁着一行数据
MySQL thread id 3, OS thread handle 0x41c62940, query id 38 localhost root cleaning up
---TRANSACTION 2845, ACTIVE 68 sec
2 lock struct(s), heap size 360, 1 row lock(s)   #这里看，还锁着一行数据MySQL thread id 2, OS thread handle 0x41c31940, query id 34 localhost root cleaning up
MySQL thread id 2, OS thread handle 0x41c31940, query id 34 localhost root cleaning up

如下可以看出，事务还在：

[information_schema]> select * from INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 2859
                 trx_state: RUNNING
               trx_started: 2015-05-25 23:21:25
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 3
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 2858
                 trx_state: RUNNING
               trx_started: 2015-05-25 23:21:13
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 2
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 02 rows in set (0.00 sec)
2 rows in set (0.00 sec)

这两个事务一直卡着，不会提交，主要原因是新增加的应用，平时不会访问数据库，只有特定的时间及测试时有访问数据库，期间不会有事务提交、回滚、新创建事务的操作。当然，这种情况也会出现在其它应用连接中，如果卡在其它忙的连接中，就会有新的事务开始，那个这个事务也就会自动提交，也就很难感觉到。

如果解决这个问题呢？

1、临时解决，都是把thread 给kill掉，让应用自动 再重连；

2、彻底解决，存储过程中添加异常捕获，sql执行失败后，执行rollback，成功后执行commit,然后统一返回一个值给应用，反馈整个过程中执行失败或成功。如0，1