Find the original MySQL deadlock ID

Find the original MySQL deadlock ID. If a deadlock occurs, how can this problem be solved? Find the original lock ID and KILL the thread that has been held all the time. But how can we find the thread ID that causes the deadlock among many threads? MySQL has been very powerful since its development. This problem is well solved. Directly link the data dictionary for search. Let's demonstrate it. Thread A, which is used to lock some records. Suppose this thread has never been committed, or forget to commit. It will always exist, but only the SLEEP status is displayed in the data. [SQL] mysql> set @ autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> use test; reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Database changed mysql> show tables; + ---------------- + | Tables_in_test | + ---------------- + | demo_test | t3 | + ---------------- + 2 rows in set (0.00 sec) mysql> select * from t3; + ---- + -------- + ------------ + ---- + | 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 | + ---- + -------- + -------- + ------------ + ---- + 2 rows in set (0.00 sec) mysql> update t3 set birthday = '2017-02-23 'where id = 19; Quer Y OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select connection_id (); + --------------- + | connection_id () | + ----------------- + | 16 | + ----------------- + 1 row in set (0.00 sec) mysql> thread B, which is used for normal updates, but encountered a problem, at this time, I don't know which thread has locked this line of records? [SQL] mysql> use test; reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Database changed mysql> select @ autocommit; + -------------- + | @ autocommit | + -------------- + | 1 | + -------------- + 1 row in set (0.00 sec) mysql> update t3 set birthday = '2017-01-03 'where id = 19; ERROR 2018 (HY000): Lock wait timeout exceeded; Try restarting transaction mysql> select connection_id (); + ----------------- + | connection_id () | + ------------------- + | 17 | + --------------- + 1 row in set (0.00 sec) mysql> show processlist; + ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ + | 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 | show processlist | 18 | root | localhost | NULL | Sleep | 5 | NULL | + ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ + 5 rows in set (0.00 s Ec) mysql> show engine innodb status \ G ------------ TRANSACTIONS ------------ Trx id counter 189327 Purge done for trx's n: o <189323 undo n: o <0 state: running but idle History list length 343 list of transactions for each session: --- TRANSACTION 0, not started MySQL thread id 11, OS thread handle 0x7f70a0c98700, query id 994 localhost root init show engine innodb status --- TRANSACTION 189326, CTIVE 2 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct (s), heap size 376, 1 row lock (s) MySQL thread id 17, OS thread handle 0x7f70a0bd5700, query id 993 localhost root updating update t3 set birthday = '2017-01-03 'where id = 19 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: record locks space id 529 page no 3 n bits 72 index 'primary' of table' tes T '. 't3 'trx id 189326 lock_mode X waiting Record lock, heap no 2 physical record: n_fields 9; compact format; info bits 0 0: len 2; hex 3139; asc 19 ;; 1: len 6; hex 00000002e38c; asc; 2: len 7; hex 7e00000d2827c9; asc ~ ('; 3: len 6; hex 6c696c793139; asc lily19; 4: len 6; hex 6c7562133139; asc lucy19; 5: len 3; hex 8fcc57; asc W ;; 6: len 4; hex 80000013; asc; 7: len 4; hex 80000000; asc; 8: len 4; hex 80000000; asc; ---------------- --- TRANSACTION 189324, ACTIVE 641 sec 2 lock struct (s), heap size 376, 3 row lock (s), undo log entries 1 MySQL thread id 16, OS thread handle 0x7f70a0b94700, query id 985 Localhost root cleaning up Trx read view will not see trx with id> = 189325, sees <189325 contains a large amount of information, and you cannot see where it is. But now, we only need to extract this part of information from the data dictionary. [SQL] mysql> SELECT * FROM information_schema.INNODB_TRX \ G ***************************** 1. row ************************** trx_id: 189324 trx_state: RUNNING trx_started: 17: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 _ Tes: 376 trx_rows_locked: 3 trx_rows_modified: 1 partition: 0 trx_isolation_level: repeatable read trx_unique_checks: 1 partition: 1 partition: NULL partition: 0 partition: 10000 trx_is_read_only: 0 Partition: 0 1 row in set (0.01 sec) mysql> it turns out that thread 16 has forgotten the COMMIT.