问题描述
我们经常会碰到这样的情况,某个事务执行完了未提交,后续再来一个DDL和DML操作,导致后面的session要么处于waiting for metadata lock,要么是锁等待超时。这时我们往往只能找到这个未提交的事务的事务id和session id,但是一般都处于sleep状态,不好分析事务内容到底是什么,所以通常都是粗鲁地kill这个session后解决问题,但是应用层的研发人员往往找不到到底是哪个事务引起的,后面再出现问题时还要重复kill。那这个情况下,怎么办呢
下面我先模拟两种情况
Session1
mysql> select* from test_lock;
+----+-----------+
| id | name |
+----+-----------+
| 1 | jiangjian |
+----+-----------+
1 row in set(0.00 sec)
mysql> begin;
Query OK, 0 rowsaffected (0.00 sec)
mysql> insertinto test_lock values(2,'jiangjian1');
Query OK, 1 rowaffected (0.00 sec)
mysql> insertinto test_lock values(3,'song');
Query OK, 1 rowaffected (0.00 sec)
mysql> insertinto test_lock values(4,'luocheng');
Query OK, 1 rowaffected (0.00 sec)
mysql> updatetest_lock set id=123 where id=1;
Query OK, 1 rowaffected (0.00 sec)
Rows matched:1 Changed: 1 Warnings: 0
Session2
mysql> showvariables like '%innodb_lock_w%';
+--------------------------+-------+
|Variable_name | Value |
+--------------------------+-------+
|innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set(0.00 sec)
mysql> updatetest_lock set id=1234 where id=1;
ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction
mysql> altertable test_lock add column name2 varchar(50);
这时session2一直卡住,我们再开一个窗口session3
Session3<