如何处理MySql死锁

当MySql发生死锁后，InnoDB将自动检测事务死锁，并立刻回滚，然后返回错误。回滚通常选择undo量最小的事务。

在MySql 5.6 之前，最近一次发生的死锁可以通过使用SHOW ENGINE INNODB STATUS 命令来返回。MySql 5.6之后，可以通过变量innodb_print_all_deadlocks 将所有InnoDB死锁信息记录在error log中。

MySql死锁涉及2个以上的事务，SHOW ENGINE INNODB STATUS返回的信息中LATEST DETECTED DEADLOCK部分所检测到最近的死锁信息仅显示最近的两个事务。此外也只展示在两个事务中所执行的最后一条语句。后文将给出一些查看更多信息的方法。

如何判断MySql死锁

接下来看两个例子，看看死锁发生后产生哪些信息。

例1

1 141013 6:06:222 *** (1) TRANSACTION:3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock4 mysql tables in use 1, locked 15 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 46 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello')8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting10 *** (2) TRANSACTION:11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting12 mysql tables in use 1, locked 113 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 100214 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more")16 *** (2) HOLDS THE LOCK(S):17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting20 *** WE ROLL BACK TRANSACTION (1)

第1行给出了死锁发生的时间，如果应用程序代码捕获到了并且记录了死锁错误，那么可以将两个时间进行比较以发现产生问题的代码。

第3行与第11行，记录了事务编号以及活跃时间。

第4行与第12行，正在使用的表的数量及被相关语句锁定的表的数量。因此1张表被使用并不能说明事务仅包含一张表。

第5行与第13行，描述了事务进行了哪些修改，这里指 undo log entries。此外还包含事务持有多少行锁，即row lock(s)。

第6行与第14行，记录了线程id,连接的主机与用户。

第9行，对于第一个事务，该行展示了它所等待的锁，本例中为在表t1上的AUTO-INC 锁。其它的可能的锁还包括S锁或X锁。

第16行与第17行，展示了第2个事务所持有的锁，该例中为AUTO-INC锁，该锁是第一个事务所等待的锁。

第18行与第19行，展示了第2个事务所等待的锁，这是一个在另一张表的主键上的共享非间隙锁。

在InnoDB中共享锁的产生有如下几种情况。
1) 使用SELECT … LOCK IN SHARE MODE
2) 外键所引用的记录
3) INSERT. INTO …SELECT 中SELECT所涉及的表。

trx(2)的当前语句是一个向表t1插入数据的插入语句，通过SHOW CREATE TABLE查看表定义可以发现等待的是外键的共享锁。

例2

1 2014-10-11 10:41:12 7f6f912d77002 *** (1) TRANSACTION:3 TRANSACTION 2164000, ACTIVE 27 sec starting index read4 mysql tables in use 1, locked 15 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 16 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating7 update t1 set name = 'b' where id = 38 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:9 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164000 lock_mode X locks rec but not gap waiting10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 011 0: len 4; hex 80000003; asc ;;12 1: len 6; hex 000000210521; asc ! !;;13 2: len 7; hex 180000122117cb; asc ! ;;14 3: len 4; hex 80000008; asc ;;15 4: len 1; hex 63; asc c;;1617 *** (2) TRANSACTION:18 TRANSACTION 2164001, ACTIVE 18 sec starting index read19 mysql tables in use 1, locked 120 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 121 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating22 update t1 set name = 'c' where id = 223 *** (2) HOLDS THE LOCK(S):24 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 026 0: len 4; hex 80000003; asc ;;27 1: len 6; hex 000000210521; asc ! !;;28 2: len 7; hex 180000122117cb; asc ! ;;29 3: len 4; hex 80000008; asc ;;30 4: len 1; hex 63; asc c;;3132 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:33 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap waiting34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 035 0: len 4; hex 80000002; asc ;;36 1: len 6; hex 000000210520; asc ! ;;37 2: len 7; hex 17000001c510f5; asc ;;38 3: len 4; hex 80000009; asc ;;39 4: len 1; hex 62; asc b;;

第9行与第10行：space id是表空间id,page no给出了记录锁所在的页面编号。 ‘n bits’ 是锁位图中的编号。而页偏移由第10行的’heap no’表示。

第11至第15行：以16进制形式展示了记录数据。0表示一个聚簇索引(主键)。忽略最高位其值为3。1是上一次修改该记录的事务ID，十进制表示为2164001，正式事务2. 2是回滚指针。从3开始剩余的为行数据。3所表示的列为整型，值为8. 4所表示的列为字符串，其后跟一个’c’。通过阅读这些信息，我们能够知道哪些行被锁定，当前值是什么。

从上述分析中所能了解到的其它信息

例1中，trx(2)在等待共享锁，所以trx(1)或者持有表t2主键上的共享锁，或者持有表t2主键上的排它锁。Col2列是一个外键，通过检查trx(1)的当前语句，我们发现trx(1)并不需要同样的记录锁，所以一定是trx(1)之前的需要表t2主键上的S或X锁的语句。Trx(1)在7秒的时间内修改了4行记录。通过分析trx(1)可知：它执行了许多处理，但只修改了很少的数据。修改包括表t1与 及对表t2插入一条记录。将这些信息结合在一起可以帮助开发者定位事务。

在哪里可以找到事务之前所执行的语句？

除了应用日志，及在之前使用SHOW ENGINE INNODB STATUS命令外，我们可以利用binlog,slow log及general query log. 通过binlog，如果binlog_format=statement,每一个binlog事件都会有thread_id。只有提交过的事务才会被记录在binlog中，因此我们可以在binlog中查询Trx(2)。在例1中，我们知道什么时候发生死锁，也知道Trx(2)在9秒前启动。因此可以通过mysqlbinlog命令来找出语句位置。

 mysqlbinlog -vvv --start-datetime=“2014-10-13 6:06:12” --stop-datatime=“2014-10-13 6:06:22” mysql-bin.000010 > binlog_1013_0606.out

如何在MySql中避免死锁

修改应用程序：某些情况下，通过将大的事务划分为若干小的事务可以减少死锁的频率，分割后锁会更快的释放。另外，死锁是在两个事务对同一数据集以不同的顺序操作时产生，要么是对同一张表要么是多个表。因此修改访问顺序，即串行化访问。当事务并发执行时最终发生锁等待而不是死锁。

修改表模式：例如删除外键，或者添加索引来减少扫描及锁定的行。

间隙锁：可以将事务的隔离级别该为提交读(read committed)以避免间隙锁。但binlog format应该修改为ROW或MIXED。

原文地址

https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/

参考

Innodb中的锁： https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html