How to find the ID of a deadlock in a Mysql thread _mysql

If you encounter a deadlock, how to solve it? Find the original lock ID, then kill the thread that has been held, but how many threads can find the thread ID that caused the deadlock? MySQL has developed to the present, has been very strong, this problem is very good to solve. Search directly from the data dictionary.

Let's take a demonstration.

Thread A, we use to lock certain records, assuming that the thread has not been committed, or has forgotten to commit. Then it always exists, but the data shows only the sleep state.

 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 = ' 2022-02-23 ' where id = 19;

Query 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 we use for normal updates, but we're having a problem, and we don't know which thread is locking this line of records at this point?

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= ' 2018-01-03 ' where id = 19; ERROR 1205 (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 sec) mysql> Show engine InnoDB status\g ———— transactions-- --trx ID counter 189327 Purge do for Trx ' s N:o < 189323 undo N:o < 0 state:running but idle History list Leng Th 343 LIST of transactions for each session:-transaction 0, not started MySQL thread ID one, OS thread handle 0x7f70a0 

c98700 Query ID 994 localhost root init show engine InnoDB status-transaction 189326, ACTIVE 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, OS thread Handle 0x7f70a0bd5700, query ID 993 localhost root updating update t3 set birthday= ' 2018-01-03 ' where id = ——-TRX H  As BEEN waiting 2 SEC for this LOCK to is Granted:record LOCKS Space ID 529 Page No 3 n bits-index ' PRIMARY ' of table ' Test '. ' 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 6c7563793139;

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 thr EAD ID, OS thread handle 0x7f70a0b94700, query ID 985 localhost root cleaning up Trx read view would not? Trx with I

 D >= 189325, sees < 189325

There is so much information on it that it is not clear where it is.

But right now, all we have to do is just take this part of the information from the data dictionary.

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_trx\g

*************************** 1 row ***************************

          trx_id:189324

        trx_state: RUNNING

       trx_started:2013-04-18 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_bytes:376

     trx_rows_locked:3

    Trx_rows_modified:1

 trx_concurrency_tickets:0

   trx_isolation_level:repeatable READ

    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

1 row in Set (0.01 sec)

Mysql> 

Thread 16 forgot commit.