查找原始MySQL死锁ID 笔记

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


我们来演示下。


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

mysql>set@@autocommit=0;
QueryOK,0rowsaffected(0.00sec)


mysql>usetest;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A


Databasechanged
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|demo_test|
|t3|
+----------------+
2rowsinset(0.00sec)


mysql>select*fromt3;
+----+--------+--------+------------+----+----+----+
|id|fname|lname|birthday|c1|c2|c3|
+----+--------+--------+------------+----+----+----+
|19|lily19|lucy19|2013-04-18|19|0|0|
|20|lily20|lucy20|2013-03-13|20|0|0|
+----+--------+--------+------------+----+----+----+
2rowsinset(0.00sec)


mysql>updatet3setbirthday='2022-02-23'whereid=19;
QueryOK,1rowaffected(0.00sec)
Rowsmatched:1Changed:1Warnings:0


mysql>selectconnection_id();
+-----------------+
|connection_id()|
+-----------------+
|16|
+-----------------+
1rowinset(0.00sec)


mysql>

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

mysql>usetest;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A


Databasechanged
mysql>select@@autocommit;
+--------------+
|@@autocommit|
+--------------+
|1|
+--------------+
1rowinset(0.00sec)


mysql>updatet3setbirthday='2018-01-03'whereid=19;
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
mysql>selectconnection_id();
+-----------------+
|connection_id()|
+-----------------+
|17|
+-----------------+
1rowinset(0.00sec)


mysql>showprocesslist;
+----+------+-----------+------+---------+------+-------+------------------+
|Id|User|Host|db|Command|Time|State|Info|
+----+------+-----------+------+---------+------+-------+------------------+
|10|root|localhost|NULL|Sleep|1540||NULL|
|11|root|localhost|NULL|Sleep|722||NULL|
|16|root|localhost|test|Sleep|424||NULL|
|17|root|localhost|test|Query|0|init|showprocesslist|
|18|root|localhost|NULL|Sleep|5||NULL|
+----+------+-----------+------+---------+------+-------+------------------+
5rowsinset(0.00sec)


mysql>showengineinnodbstatus\G




------------
TRANSACTIONS
------------
Trxidcounter189327
Purgedonefortrx'sn:o<189323undon:o<0state:runningbutidle
Historylistlength343
LISTOFTRANSACTIONSFOREACHSESSION:
---TRANSACTION0,notstarted
MySQLthreadid11,OSthreadhandle0x7f70a0c98700,queryid994localhostrootinit
showengineinnodbstatus
---TRANSACTION189326,ACTIVE2secstartingindexread
mysqltablesinuse1,locked1
LOCKWAIT2lockstruct(s),heapsize376,1rowlock(s)
MySQLthreadid17,OSthreadhandle0x7f70a0bd5700,queryid993localhostrootupdating
updatet3setbirthday='2018-01-03'whereid=19
-------TRXHASBEENWAITING2SECFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid529pageno3nbits72index`PRIMARY`oftable`test`.`t3`trxid189326lock_modeXwaiting
Recordlock,heapno2PHYSICALRECORD:n_fields9;compactformat;infobits0
0:len2;hex3139;asc19;;
1:len6;hex00000002e38c;asc;;
2:len7;hex7e00000d2827c9;asc~(';;
3:len6;hex6c696c793139;asclily19;;
4:len6;hex6c7563793139;asclucy19;;
5:len3;hex8fcc57;ascW;;
6:len4;hex80000013;asc;;
7:len4;hex80000000;asc;;
8:len4;hex80000000;asc;;


------------------
---TRANSACTION189324,ACTIVE641sec
2lockstruct(s),heapsize376,3rowlock(s),undologentries1
MySQLthreadid16,OSthreadhandle0x7f70a0b94700,queryid985localhostrootcleaningup
Trxreadviewwillnotseetrxwithid>=189325,sees<189325

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


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

mysql>SELECT*FROMinformation_schema.INNODB_TRX\G
***************************1.row***************************
trx_id:189324
trx_state:RUNNING
trx_started:2013-04-1817:48:14
trx_requested_lock_id:NULL
trx_wait_started:NULL
trx_weight:3
trx_mysql_thread_id:16
trx_query:NULL
trx_operation_state:NULL
trx_tables_in_use:0
trx_tables_locked:0
trx_lock_structs:2
trx_lock_memory_bytes:376
trx_rows_locked:3
trx_rows_modified:1
trx_concurrency_tickets:0
trx_isolation_level:REPEATABLEREAD
trx_unique_checks:1
trx_foreign_key_checks:1
trx_last_foreign_key_error:NULL
trx_adaptive_hash_latched:0
trx_adaptive_hash_timeout:10000
trx_is_read_only:0
trx_autocommit_non_locking:0
1rowinset(0.01sec)


mysql>

原来是线程16忘掉COMMIT了。