查找原始MySQL死锁ID 笔记

如果遇到死锁了，怎么解决呢？找到原始的锁ID，然后KILL掉一直持有的那个线程就可以了， 但是众多线程，可怎么找到引起死锁的线程ID呢？ MySQL 发展到现在，已经非常强大了，这个问题很好解决。 直接从数据字典连查找。


我们来演示下。


线程A，我们用来锁定某些记录，假设这个线程一直没提交，或者忘掉提交了。 那么就一直存在，但是数据里面显示的只是SLEEP状态。

[sql] view plain copy

1. mysql>set@@autocommit=0;
2. QueryOK,0rowsaffected(0.00sec)
5. mysql>usetest;
6. Readingtableinformationforcompletionoftableandcolumnnames
7. Youcanturnoffthisfeaturetogetaquickerstartupwith-A
10. Databasechanged
11. mysql>showtables;
12. +----------------+
13. |Tables_in_test|
14. +----------------+
15. |demo_test|
16. |t3|
17. +----------------+
18. 2rowsinset(0.00sec)
21. mysql>select*fromt3;
22. +----+--------+--------+------------+----+----+----+
23. |id|fname|lname|birthday|c1|c2|c3|
24. +----+--------+--------+------------+----+----+----+
25. |19|lily19|lucy19|2013-04-18|19|0|0|
26. |20|lily20|lucy20|2013-03-13|20|0|0|
27. +----+--------+--------+------------+----+----+----+
28. 2rowsinset(0.00sec)
31. mysql>updatet3setbirthday='2022-02-23'whereid=19;
32. QueryOK,1rowaffected(0.00sec)
33. Rowsmatched:1Changed:1Warnings:0
36. mysql>selectconnection_id();
37. +-----------------+
38. |connection_id()|
39. +-----------------+
40. |16|
41. +-----------------+
42. 1rowinset(0.00sec)
45. mysql>

线程B， 我们用来进行普通的更新，但是遇到问题了，此时不知道是哪个线程把这行记录给锁定了？

[sql] view plain copy

1. mysql>usetest;
2. Readingtableinformationforcompletionoftableandcolumnnames
3. Youcanturnoffthisfeaturetogetaquickerstartupwith-A
6. Databasechanged
7. mysql>select@@autocommit;
8. +--------------+
9. |@@autocommit|
10. +--------------+
11. |1|
12. +--------------+
13. 1rowinset(0.00sec)
16. mysql>updatet3setbirthday='2018-01-03'whereid=19;
17. ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
18. mysql>selectconnection_id();
19. +-----------------+
20. |connection_id()|
21. +-----------------+
22. |17|
23. +-----------------+
24. 1rowinset(0.00sec)
27. mysql>showprocesslist;
28. +----+------+-----------+------+---------+------+-------+------------------+
29. |Id|User|Host|db|Command|Time|State|Info|
30. +----+------+-----------+------+---------+------+-------+------------------+
31. |10|root|localhost|NULL|Sleep|1540||NULL|
32. |11|root|localhost|NULL|Sleep|722||NULL|
33. |16|root|localhost|test|Sleep|424||NULL|
34. |17|root|localhost|test|Query|0|init|showprocesslist|
35. |18|root|localhost|NULL|Sleep|5||NULL|
36. +----+------+-----------+------+---------+------+-------+------------------+
37. 5rowsinset(0.00sec)
40. mysql>showengineinnodbstatus\G
45. ------------
46. TRANSACTIONS
47. ------------
48. Trxidcounter189327
49. Purgedonefortrx'sn:o<189323undon:o<0state:runningbutidle
50. Historylistlength343
51. LISTOFTRANSACTIONSFOREACHSESSION:
52. ---TRANSACTION0,notstarted
53. MySQLthreadid11,OSthreadhandle0x7f70a0c98700,queryid994localhostrootinit
54. showengineinnodbstatus
55. ---TRANSACTION189326,ACTIVE2secstartingindexread
56. mysqltablesinuse1,locked1
57. LOCKWAIT2lockstruct(s),heapsize376,1rowlock(s)
58. MySQLthreadid17,OSthreadhandle0x7f70a0bd5700,queryid993localhostrootupdating
59. updatet3setbirthday='2018-01-03'whereid=19
60. -------TRXHASBEENWAITING2SECFORTHISLOCKTOBEGRANTED:
61. RECORDLOCKSspaceid529pageno3nbits72index`PRIMARY`oftable`test`.`t3`trxid189326lock_modeXwaiting
62. Recordlock,heapno2PHYSICALRECORD:n_fields9;compactformat;infobits0
63. 0:len2;hex3139;asc19;;
64. 1:len6;hex00000002e38c;asc;;
65. 2:len7;hex7e00000d2827c9;asc~(';;
66. 3:len6;hex6c696c793139;asclily19;;
67. 4:len6;hex6c7563793139;asclucy19;;
68. 5:len3;hex8fcc57;ascW;;
69. 6:len4;hex80000013;asc;;
70. 7:len4;hex80000000;asc;;
71. 8:len4;hex80000000;asc;;
74. ------------------
75. ---TRANSACTION189324,ACTIVE641sec
76. 2lockstruct(s),heapsize376,3rowlock(s),undologentries1
77. MySQLthreadid16,OSthreadhandle0x7f70a0b94700,queryid985localhostrootcleaningup
78. Trxreadviewwillnotseetrxwithid>=189325,sees<189325

上面的信息很繁多，也看不清楚到底哪里是哪里。


不过现在，我们只要从数据字典里面拿出来这部分信息就OK了。

[sql] view plain copy

1. mysql>SELECT*FROMinformation_schema.INNODB_TRX\G
2. ***************************1.row***************************
3. trx_id:189324
4. trx_state:RUNNING
5. trx_started:2013-04-1817:48:14
6. trx_requested_lock_id:NULL
7. trx_wait_started:NULL
8. trx_weight:3
9. trx_mysql_thread_id:16
10. trx_query:NULL
11. trx_operation_state:NULL
12. trx_tables_in_use:0
13. trx_tables_locked:0
14. trx_lock_structs:2
15. trx_lock_memory_bytes:376
16. trx_rows_locked:3
17. trx_rows_modified:1
18. trx_concurrency_tickets:0
19. trx_isolation_level:REPEATABLEREAD
20. trx_unique_checks:1
21. trx_foreign_key_checks:1
22. trx_last_foreign_key_error:NULL
23. trx_adaptive_hash_latched:0
24. trx_adaptive_hash_timeout:10000
25. trx_is_read_only:0
26. trx_autocommit_non_locking:0
27. 1rowinset(0.01sec)
30. mysql>

原来是线程16忘掉COMMIT了。