MYSQL deadlock search

How can I solve the deadlock issue in MYSQL? 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 = '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 is used for normal updates, but has 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='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 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, 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 17, OS thread handle 0x7f70a0bd5700, query id 993 localhost root updating  update t3 set birthday='2018-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 `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 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  

 

The information above is huge, 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: 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>