查找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了。