【Mysql】死锁-Lock wait timeout exceeded; try restarting transaction

  • 小亮520cl

    2016-03-23 16:08:41
  • MySQL
  • 原创
问题描述：在mysql的gameshop数据库上报Lock wait timeout exceeded;try restarting transaction; 执行update语句删除失败。

1、锁等待超时。是当前事务在等待其它事务释放锁资源造成的。可以找出锁资源竞争的表和语句，优化你的SQL，创建索引等，如果还是不行，可以适当减少并发线程数。

2、你的事务在等待给某个表加锁时超时了，估计是表正被另的进程锁住一直没有释放。
可以用 SHOW INNODB STATUS/G; 看一下锁的情况。


查看innodb status分析死锁
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-04-08 15:58:59 7fcf9aa0c700
*** (1) TRANSACTION:
TRANSACTION 21116319864, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 99 lock struct(s), heap size 13864, 348 row lock(s)
MySQL thread id 5555530, OS thread handle 0x7fcf9a741700, query id 1017474406 192.168.1.233 fkonguser Searching rows for update
UPDATE `data_info` SET `status`=3 WHERE `status`= 2 && curltime<1460102279 && curltotal<3     ---先锁住二级索引在锁定聚族索引 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 137 page no 739314 n bits 80 index `PRIMARY` of table `fengkong`.`data_info` trx id 21116319864 lock_mode X locks rec but not gap waiting （行锁x排它锁）
Record lock, heap no 8 PHYSICAL RECORD: n_fields 34; compact format; info bits 0
 0: len 4; hex 00451d1f; asc  E  ;;
 1: len 6; hex 0004eaa1787a; asc     xz;;
 2: len 7; hex 530000105b1a45; asc S   [ E;;
 3: len 4; hex 80000001; asc     ;;
 4: len 30; hex e69d8ee5869b202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 5: len 30; hex 313430313033313936393035323031353133202020202020202020202020; asc 140103196905201513            ; (total 50 bytes);
 6: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 7: len 4; hex 80000205; asc     ;;
 8: len 4; hex d707622d; asc   b-;;
 9: len 15; hex 36312e35352e3136342e3235202020; asc 61.55.164.25   ;;
 10: len 4; hex 80000001; asc     ;;
 11: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 12: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 13: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 14: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 15: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 16: len 12; hex 5b227378627a78726378225d; asc ["sxbzxrcx"];;
 17: len 4; hex 80000000; asc     ;;
 18: len 14; hex 7b227378627a78726378223a307d; asc {"sxbzxrcx":0};;
 19: len 4; hex 80355bae; asc  5[ ;;
 20: len 4; hex 804bbabb; asc  K  ;;
 21: len 4; hex 80000002; asc     ;;
 22: len 4; hex d70764c3; asc   d ;;
 23: len 4; hex d70764c3; asc   d ;;
 24: len 0; hex ; asc ;;
 25: len 0; hex ; asc ;;
 26: len 0; hex ; asc ;;
 27: len 0; hex ; asc ;;
 28: len 0; hex ; asc ;;
 29: len 0; hex ; asc ;;
 30: len 0; hex ; asc ;;
 31: len 30; hex 356166616637363362636261643834623365396163393737636363323431; asc 5afaf763bcbad84b3e9ac977ccc241; (total 32 bytes);
 32: len 1; hex 80; asc  ;;
 33: len 0; hex ; asc ;;


*** (2) TRANSACTION:
TRANSACTION 21116319866, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 5555528, OS thread handle 0x7fcf9aa0c700, query id 1017474408 192.168.1.233 fkonguser updating
UPDATE `data_info` SET `status` = 1, `successtime` = 1460102339, `rsid` = 4963003 WHERE `infoid` =  '4529439'   ---先锁住聚族索引，在锁定二级索引  
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 137 page no 739314 n bits 80 index `PRIMARY` of table `fengkong`.`data_info` trx id 21116319866 lock_mode X locks rec but not gap   行锁（x）
Record lock, heap no 8 PHYSICAL RECORD: n_fields 34; compact format; info bits 0
 0: len 4; hex 00451d1f; asc  E  ;;
 1: len 6; hex 0004eaa1787a; asc     xz;;
 2: len 7; hex 530000105b1a45; asc S   [ E;;
 3: len 4; hex 80000001; asc     ;;
 4: len 30; hex e69d8ee5869b202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 5: len 30; hex 313430313033313936393035323031353133202020202020202020202020; asc 140103196905201513            ; (total 50 bytes);
 6: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 7: len 4; hex 80000205; asc     ;;
 8: len 4; hex d707622d; asc   b-;;
 9: len 15; hex 36312e35352e3136342e3235202020; asc 61.55.164.25   ;;
 10: len 4; hex 80000001; asc     ;;
 11: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 12: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 13: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 14: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 15: len 30; hex 202020202020202020202020202020202020202020202020202020202020; asc                               ; (total 255 bytes);
 16: len 12; hex 5b227378627a78726378225d; asc ["sxbzxrcx"];;
 17: len 4; hex 80000000; asc     ;;
 18: len 14; hex 7b227378627a78726378223a307d; asc {"sxbzxrcx":0};;
 19: len 4; hex 80355bae; asc  5[ ;;
 20: len 4; hex 804bbabb; asc  K  ;;
 21: len 4; hex 80000002; asc     ;;
 22: len 4; hex d70764c3; asc   d ;;
 23: len 4; hex d70764c3; asc   d ;;
 24: len 0; hex ; asc ;;
 25: len 0; hex ; asc ;;
 26: len 0; hex ; asc ;;
 27: len 0; hex ; asc ;;
 28: len 0; hex ; asc ;;
 29: len 0; hex ; asc ;;
 30: len 0; hex ; asc ;;
 31: len 30; hex 356166616637363362636261643834623365396163393737636363323431; asc 5afaf763bcbad84b3e9ac977ccc241; (total 32 bytes);
 32: len 1; hex 80; asc  ;;
 33: len 0; hex ; asc ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 137 page no 639834 n bits 1000 index `idx_sta_curltotal` of table `fengkong`.`data_info` trx id 21116319866 lock_mode X locks rec but not gap waiting
Record lock, heap no 677 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;
 2: len 4; hex 00451d1f; asc  E  ;;


*** WE ROLL BACK TRANSACTION (2)





1初步分析

并发事务，每个事务只有一条SQL语句：给定二级索引键值，更新记录。

T1（事务一）这个update语句会执行以下步骤：

  1. 1、由于用到了非主键索引，首先需要获取status上的行级锁，然后加上该条语句与上下条之间的GAP锁
  2. 2、紧接着根据主键进行更新，所以需要获取主键上的行级锁；
  3. 3、更新完毕后，提交，并释放所有锁。

如果在步骤1和2之间突然插入一条语句：UPDATE `data_info` SET `status` = 1, `successtime` = 1466596429, `rsid` = 9056958 WHERE `infoid` =  11578404,也就是事务T2 这条语句会先锁住主键索引，然后等待锁住status。

蛋疼的情况出现了，一条语句获取了status上的锁，等待主键索引上的锁；另一条语句获取了主键上的锁，等待status上的锁，这样就出现了死锁




通过物理页分析也看得出来

  1. T1：等待RECORD LOCKS space id 137 page no 739314 n bits 80
  2. T2：持有RECORD LOCKS space id 137 page no 739314 n bits 80 等待RECORD LOCKS space id 137 page no 639834 n bits 1000
  3. t1等待的是t2持有的

我们考虑上述两种 SQL 的混合场景，一个是先锁住二级索引记录，再锁聚集索引；另一个是先锁聚集索引，再检查二级索引冲突，因此在这类并发更新场景下，可能会发生死锁。




解决办法：
引用：InnoDB会自动的检测死锁进行回滚，或者终止死锁的情况。
InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

你应该设置innodb_lock_wait_timeout来解决这种情况。
innodb_lock_wait_timeout是Innodb放弃行级锁与死锁的超时时间。


2：事务的隔离级别设置为Read Committed，同时设置innodb_locks_unsafe_for_binlog参数(此参数默认为FALSE)；(Read Committed隔离级别，由于不会加Gap锁，不会有next key，因此也不会产生死锁)，这种设置可能会破坏事物的隔离性，可能导致主从数据的不一致不推荐使用






查看堵塞sql语句

  1. SELECT
      b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query,
      b.trx_state,
      b.trx_started,
      d.user,
      d.host,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
      r.trx_state,
      r.trx_wait_started

    FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
    INNER JOIN information_schema.`PROCESSLIST` d on b.trx_mysql_thread_Id = d.id
    -- INNER JOIN information_schema.INNODB_LOCKS e on r.trx_id=e.lock_trx_id
    ;






其它几种判定锁的方法：
  1. 3.3 使用mysqladmin debug查看
  2. # mysqladmin -S /tmp/mysql3306.sock debug

  4. 然后在error日志中，会看到：
  5. [html] view plain copy print?
  6. Thread database.table_name Locked/Waiting Lock_type
  7.   
  8.   
  9. 3 test.t3 Locked - read Low priority read lock
  10. 7 test.emp Locked - write High priority write lock

  12. 这种方法中，能找到线程ID=3和7是阻塞者，但还是不太准确，判断不出来线程7也是被线程ID=3阻塞的。

  14. 3.4 使用innodb_lock_monitor来获取阻塞锁线程
  15. [sql] view plain copy print?
  16. MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## 随便在一个数据库中创建这个表，就会打开lock monitor
  17. Query OK, 0 rows affected, 1 warning (0.07 sec)
  18.   
  19. MySQL [test]> show warnings\G
  20. *************************** 1. row ***************************
  21.   Level: Warning
  22.    Code: 131
  23. Message: Using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON.
  24. 1 row in set (0.00 sec)

  26. 说明：这个在5.6中有一个warning，但不影响使用。

  28. 然后再使用show engine innodb status查看：



推荐这几篇博客：和这个bug
http://www.xuchunyang.com/2016/01/13/deak_lock/
http://hidba.org/?p=1080
http://hedengcheng.com/?p=844
http://blog.jobbole.com/99208/
http://mysqllover.com/?p=437
bug：https://bugs.mysql.com/bug.php?id=77209
请使用浏览器的分享功能分享到微信等