存储过程未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;
推荐阅读
-
存储过程未rollback导致锁表
-
SQLSERVER查询锁表,阻塞,使用表名查询存储过程,行数,表字段类型等常规查询实例讲解
-
SQLSERVER查询锁表,阻塞,使用表名查询存储过程,行数,表字段类型等常规查询实例讲解
-
存储过程传递参数与表中字段属性相同导致错误的问题_MySQL
-
存储过程传递参数与表中字段属性相同导致错误的问题_MySQL
-
Oracle 用存储过程杀掉session解决锁表的问题
-
SQL SERVER 临时表导致存储过程重编译(recompile)的一些探讨_MySQL
-
SQL SERVER 临时表导致存储过程重编译(recompile)的一些探讨_MySQL
-
plsql事务未提交导致锁表