存储过程未rollback导致锁表

    整个系统业务阻塞,jstack -l pid查看堆栈,发现都阻塞在socket read,还以为是网络问题,最后居然是因为一个简单的存储过程导致锁表。

DROP PROCEDURE IF EXISTS P_COUNT;
CREATE PROCEDURE P_COUNT(OUT o_ret VARCHAR(50))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION  SET o_retsult=-1; 
 START TRANSACTION;  
  update tb_count set val= val+ 1; 
 COMMIT;
END

在调用这个存储过程异常之后（十分纳闷怎么会异常？），因为没有commit，导致事务没有提交而一直锁住表（如果是按主键更新会是行数，锁住某记录行）。其他连接再调用该存储过程的时候会无法获取锁而一直等待而导致事务超时,不断的重启事务。

解决:在异常handler里增加rollback

DROP PROCEDURE IF EXISTS P_COUNT;
CREATE PROCEDURE P_COUNT(OUT o_ret VARCHAR(50))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
  ROLLBACK;
  SET o_retsult=-1;
 END;  
 START TRANSACTION;  
  update tb_count set val= val+ 1; 
 COMMIT;
END

或者在调用存储过程的代码里增加rollback;

这样以后就不会再锁表了,但现在表还在锁着,还得锁表:

1、通过information_schema.innodb_trx表找到这个sql的事物ID ( trx_id )
mysql> select trx_id,trx_started,trx_requested_lock_id,trx_mysql_thread_id,trx_query from information_schema.innodb_trx  where trx_query like '%p_count%';
mysql> desc information_schema.innodb_trx;       
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事物状态
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事物开始时间
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#事物请求锁ID
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事物开始等待时间
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事物线程ID，即show processlist看到ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事物当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事物中有多少个表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#使用拥有多少个锁
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事物锁住的内存大小
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事物锁住的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#使用修改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事物并发票数
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事物隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_is_read_only           | int(1)              | NO   |     | 0                   |       |#
| trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |#
+----------------------------+---------------------+------+-----+---------------------+-------+
2、通过上面步骤1找到的事物ID ( trx_id )，找到占有锁的事务ID ( blocking_trx_id )
mysql> select * from information_schema.innodb_lock_waits  where requesting_trx_id= ;
关于innodb_lock_waits 表的字段含义的解释：
mysql> desc information_schema.innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事物ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事物ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+-------------------+-------------+------+-----+---------+-------+
3、通过步骤2找到的占有锁的事物ID ( blocking_trx_id ),找到占有锁的事物线程ID trx_id
mysql> select * from  information_schema.innodb_trx where trx_id=1234  \G
4、通过步骤3找的事物ID，可以查看下这个事物发起的账号和主机信息，提供给开发人员查找异常的真正原因，并kill这个事物ID，这条数据就可以正常删除了

#查看下这个事物发起的账号和主机信息
mysql> select * from  information_schema.processlist where ID=1234;
#kill 这个未提交的事物线程ID
mysql> kill 1234;