查找MySQL线程中死锁的ID的方法

这篇文章主要介绍了查找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了。