這篇文章主要介紹了查找MySQL線程中死鎖的ID的方法,文中介紹的方法主要是從數據字典里查找,需要的朋友可以參考下

如果遇到死鎖了，怎么解決呢?找到原始的鎖ID，然后KILL掉一直持有的那個線程就可以了， 但是眾多線程，可怎么找到引起死鎖的線程ID呢? MySQL 發展到現在，已經非常強大了，這個問題很好解決。 直接從數據字典連查找。

我們來演示下。

線程A，我們用來鎖定某些記錄，假設這個線程一直沒提交，或者忘掉提交了。 那么就一直存在，但是數據里面顯示的只是SLEEP狀態。

1. mysql> set @@autocommit=0; 
2.  
3. Query OK, 0 rows affected (0.00 sec) 
4.  
5. mysql> use test; 
6.  
7. Reading table information for completion of table and column names 
8.  
9. You can turn off this feature to get a quicker startup with -A 
10.  
11. Database changed 
12.  
13. mysql> show tables; 
14.  
15. +—————-+ 
16.  
17. | Tables_in_test | 
18.  
19. +—————-+ 
20.  
21. | demo_test | 
22.  
23. | t3 | 
24.  
25. +—————-+ 
26.  
27. 2 rows in set (0.00 sec) 
28.  
29. mysql> select * from t3; 
30.  
31. +—-+——–+——–+————+—-+—-+—-+ 
32.  
33. | id | fname | lname | birthday | c1 | c2 | c3 | 
34.  
35. +—-+——–+——–+————+—-+—-+—-+ 
36.  
37. | 19 | lily19 | lucy19 | 2013-04-18 | 19 | 0 | 0 | 
38.  
39. | 20 | lily20 | lucy20 | 2013-03-13 | 20 | 0 | 0 | 
40.  
41. +—-+——–+——–+————+—-+—-+—-+ 
42.  
43. 2 rows in set (0.00 sec) 
44.  
45. mysql> update t3 set birthday = '2022-02-23' where id = 19; 
46.  
47. Query OK, 1 row affected (0.00 sec) 
48.  
49. Rows matched: 1 Changed: 1 Warnings: 0 
50.  
51. mysql> select connection_id(); 
52.  
53. +—————–+ 
54.  
55. | connection_id() | 
56.  
57. +—————–+ 
58.  
59. | 16 | 
60.  
61. +—————–+ 
62.  
63. 1 row in set (0.00 sec) 
64.  
65. mysql>  

線程B， 我們用來進行普通的更新，但是遇到問題了，此時不知道是哪個線程把這行記錄給鎖定了?

1. mysql> use test; 
2.  
3. Reading table information for completion of table and column names 
4.  
5. You can turn off this feature to get a quicker startup with -A 
6.  
7. Database changed 
8.  
9. mysql> select @@autocommit; 
10.  
11. +————–+ 
12.  
13. | @@autocommit | 
14.  
15. +————–+ 
16.  
17. | 1 | 
18.  
19. +————–+ 
20.  
21. 1 row in set (0.00 sec) 
22.  
23. mysql> update t3 set birthday='2018-01-03' where id = 19; 
24.  
25. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 
26.  
27. mysql> select connection_id(); 
28.  
29. +—————–+ 
30.  
31. | connection_id() | 
32.  
33. +—————–+ 
34.  
35. | 17 | 
36.  
37. +—————–+ 
38.  
39. 1 row in set (0.00 sec) 
40.  
41. mysql> show processlist; 
42.  
43. +—-+——+———–+——+———+——+——-+——————+ 
44.  
45. | Id | User | Host | db | Command | Time | State | Info | 
46.  
47. +—-+——+———–+——+———+——+——-+——————+ 
48.  
49. | 10 | root | localhost | NULL | Sleep | 1540 | | NULL | 
50.  
51. | 11 | root | localhost | NULL | Sleep | 722 | | NULL | 
52.  
53. | 16 | root | localhost | test | Sleep | 424 | | NULL | 
54.  
55. | 17 | root | localhost | test | Query | 0 | init | show processlist | 
56.  
57. | 18 | root | localhost | NULL | Sleep | 5 | | NULL | 
58.  
59. +—-+——+———–+——+———+——+——-+——————+ 
60.  
61. 5 rows in set (0.00 sec) 
62.  
63. mysql> show engine innodb status/G 
64.  
65. ———— 
66.  
67. TRANSACTIONS 
68.  
69. ———— 
70.  
71. Trx id counter 189327 
72.  
73. Purge done for trx's n:o < 189323 undo n:o < 0 state: running but idle 
74.  
75. History list length 343 
76.  
77. LIST OF TRANSACTIONS FOR EACH SESSION: 
78.  
79. —TRANSACTION 0, not started 
80.  
81. MySQL thread id 11, OS thread handle 0x7f70a0c98700, query id 994 localhost root init 
82.  
83. show engine innodb status 
84.  
85. —TRANSACTION 189326, ACTIVE 2 sec starting index read 
86.  
87. mysql tables in use 1, locked 1 
88.  
89. LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) 
90.  
91. MySQL thread id 17, OS thread handle 0x7f70a0bd5700, query id 993 localhost root updating 
92.  
93. update t3 set birthday='2018-01-03' where id = 19 
94.  
95. ——- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: 
96.  
97. RECORD LOCKS space id 529 page no 3 n bits 72 index `PRIMARY` of table `test`.`t3` trx id 189326 lock_mode X waiting 
98.  
99. Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0 
100.  
101. 0: len 2; hex 3139; asc 19;; 
102.  
103. 1: len 6; hex 00000002e38c; asc ;; 
104.  
105. 2: len 7; hex 7e00000d2827c9; asc ~ (' ;; 
106.  
107. 3: len 6; hex 6c696c793139; asc lily19;; 
108.  
109. 4: len 6; hex 6c7563793139; asc lucy19;; 
110.  
111. 5: len 3; hex 8fcc57; asc W;; 
112.  
113. 6: len 4; hex 80000013; asc ;; 
114.  
115. 7: len 4; hex 80000000; asc ;; 
116.  
117. 8: len 4; hex 80000000; asc ;; 
118.  
119. —————— 
120.  
121. —TRANSACTION 189324, ACTIVE 641 sec 
122.  
123. 2 lock struct(s), heap size 376, 3 row lock(s), undo log entries 1 
124.  
125. MySQL thread id 16, OS thread handle 0x7f70a0b94700, query id 985 localhost root cleaning up 
126.  
127. Trx read view will not see trx with id >= 189325, sees < 189325 

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

不過現在，我們只要從數據字典里面拿出來這部分信息就OK了。

1. mysql> SELECT * FROM information_schema.INNODB_TRX/G 
2.  
3. *************************** 1. row *************************** 
4.  
5. trx_id: 189324 
6.  
7. trx_state: RUNNING 
8.  
9. trx_started: 2013-04-18 17:48:14 
10.  
11. trx_requested_lock_id: NULL 
12.  
13. trx_wait_started: NULL 
14.  
15. trx_weight: 3 
16.  
17. trx_mysql_thread_id: 16 
18.  
19. trx_query: NULL 
20.  
21. trx_operation_state: NULL 
22.  
23. trx_tables_in_use: 0 
24.  
25. trx_tables_locked: 0 
26.  
27. trx_lock_structs: 2 
28.  
29. trx_lock_memory_bytes: 376 
30.  
31. trx_rows_locked: 3 
32.  
33. trx_rows_modified: 1 
34.  
35. trx_concurrency_tickets: 0 
36.  
37. trx_isolation_level: REPEATABLE READ 
38.  
39. trx_unique_checks: 1 
40.  
41. trx_foreign_key_checks: 1 
42.  
43. trx_last_foreign_key_error: NULL 
44.  
45. trx_adaptive_hash_latched: 0 
46.  
47. trx_adaptive_hash_timeout: 10000 
48.  
49. trx_is_read_only: 0 
50.  
51. trx_autocommit_non_locking: 0 
52.  
53. 1 row in set (0.01 sec) 
54.  
55. mysql>  

原來是線程16忘掉COMMIT了。