Deadlock

• mysql 5.7

查看事务的阻塞关系

SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM
    information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread
FROM
    information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

mysql> SELECT   r.trx_id AS waiting_trx_id,   r.trx_mysql_thread_id AS waiting_thread,   r.trx_query AS waiting_query,   b.trx_id AS blocking_trx_id,   b.trx_mysql_thread_id AS blocking_thread,   b.trx_query AS blocking_query FROM   information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 858786370
 waiting_thread: 17665463
  waiting_query: DELETE FROM `members` WHERE member_type = 'User' AND member_id in (13,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,55,56,57,58,61,62,63,64,73,85,87,89,91,92,93,95,96,115,116,117,118,120,121,122,125,127,128,129,131,144,146,148,150,159,161,176,192,193,195,196,198,199,201,202,203,205,207,209,210,211,212,213,215,216,218,219,220,221,222,223,225,226,228,229,230,231,311,313,315,317,341,342,343,344,345,346,349,350,355,356,358,359,360,362,376,377,380,382,383,384,406,407,408,420,422,424,426,428,430,435,436,437,438,452,476,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,498,499,501,502,505,506,508,509,510,511,512,513,514,516,517,518,520,521,523,524,525,526,534,535,536,537,538,539,540,542,543,544,545,546,547,548,549,550,551,567,574,576,578,580,582,583,584,586,587,588,589,590,592,599,601,602,604,606,608,609,611,612,614,615,617,619,620,622,625,627,630,632,633,634,635,637,638,640,641,642,643,644,646,647,650,651,654,655,657,658,661,662,663,665,669,670,671,672,674,6
blocking_trx_id: 858786366
blocking_thread: 17665678
 blocking_query: DELETE FROM `members` WHERE member_type = 'User' AND member_id in (13,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,55,56,57,58,61,62,63,64,73,85,87,89,91,92,93,95,96,115,116,117,118,120,121,122,125,127,128,129,131,144,146,148,150,159,161,176,192,193,195,196,198,199,201,202,203,205,207,209,210,211,212,213,215,216,218,219,220,221,222,223,225,226,228,229,230,231,311,313,315,317,341,342,343,344,345,346,349,350,355,356,358,359,360,362,376,377,380,382,383,384,406,407,408,420,422,424,426,428,430,435,436,437,438,452,476,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,498,499,501,502,505,506,508,509,510,511,512,513,514,516,517,518,520,521,523,524,525,526,534,535,536,537,538,539,540,542,543,544,545,546,547,548,549,550,551,567,574,576,578,580,582,583,584,586,587,588,589,590,592,599,601,602,604,606,608,609,611,612,614,615,617,619,620,622,625,627,630,632,633,634,635,637,638,640,641,642,643,644,646,647,650,651,654,655,657,658,661,662,663,665,669,670,671,672,674,6
*************************** 2. row ***************************
 waiting_trx_id: 858786370
 waiting_thread: 17665463
  waiting_query: DELETE FROM `members` WHERE member_type = 'User' AND member_id in (13,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,55,56,57,58,61,62,63,64,73,85,87,89,91,92,93,95,96,115,116,117,118,120,121,122,125,127,128,129,131,144,146,148,150,159,161,176,192,193,195,196,198,199,201,202,203,205,207,209,210,211,212,213,215,216,218,219,220,221,222,223,225,226,228,229,230,231,311,313,315,317,341,342,343,344,345,346,349,350,355,356,358,359,360,362,376,377,380,382,383,384,406,407,408,420,422,424,426,428,430,435,436,437,438,452,476,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,498,499,501,502,505,506,508,509,510,511,512,513,514,516,517,518,520,521,523,524,525,526,534,535,536,537,538,539,540,542,543,544,545,546,547,548,549,550,551,567,574,576,578,580,582,583,584,586,587,588,589,590,592,599,601,602,604,606,608,609,611,612,614,615,617,619,620,622,625,627,630,632,633,634,635,637,638,640,641,642,643,644,646,647,650,651,654,655,657,658,661,662,663,665,669,670,671,672,674,6
blocking_trx_id: 858583918
blocking_thread: 17645230
 blocking_query: NULL
*************************** 3. row ***************************
 waiting_trx_id: 858786366
 waiting_thread: 17665678
  waiting_query: DELETE FROM `members` WHERE member_type = 'User' AND member_id in (13,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,55,56,57,58,61,62,63,64,73,85,87,89,91,92,93,95,96,115,116,117,118,120,121,122,125,127,128,129,131,144,146,148,150,159,161,176,192,193,195,196,198,199,201,202,203,205,207,209,210,211,212,213,215,216,218,219,220,221,222,223,225,226,228,229,230,231,311,313,315,317,341,342,343,344,345,346,349,350,355,356,358,359,360,362,376,377,380,382,383,384,406,407,408,420,422,424,426,428,430,435,436,437,438,452,476,480,481,482,483,485,486,487,488,489,490,491,492,493,494,495,496,498,499,501,502,505,506,508,509,510,511,512,513,514,516,517,518,520,521,523,524,525,526,534,535,536,537,538,539,540,542,543,544,545,546,547,548,549,550,551,567,574,576,578,580,582,583,584,586,587,588,589,590,592,599,601,602,604,606,608,609,611,612,614,615,617,619,620,622,625,627,630,632,633,634,635,637,638,640,641,642,643,644,646,647,650,651,654,655,657,658,661,662,663,665,669,670,671,672,674,6
blocking_trx_id: 858583918
blocking_thread: 17645230
 blocking_query: NULL
3 rows in set, 1 warning (0.07 sec)

For MySQL 8

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM
    performance_schema.data_lock_waits w
JOIN performance_schema.data_locks bl ON bl.engine_lock_id = w.blocking_engine_lock_id
JOIN performance_schema.data_locks rl ON rl.engine_lock_id = w.requesting_engine_lock_id
JOIN information_schema.innodb_trx b ON b.trx_id = bl.engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = rl.engine_transaction_id
;

查看事务信息

SELECT * FROM information_schema.innodb_trx WHERE trx_id = 858583918\G

*************************** 1. row ***************************
                    trx_id: 858583918
                 trx_state: RUNNING
               trx_started: 2025-04-17 07:01:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 591
       trx_mysql_thread_id: 17645230
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 591
     trx_lock_memory_bytes: 73936
           trx_rows_locked: 93883
         trx_rows_modified: 0
   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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

trx_rows_locked: 93883 这一行有锁。

字段	解释
trx_state: RUNNING	当前事务正在运行中，不是等待状态。如果是死锁，应该是 LOCK WAIT
trx_requested_lock_id: NULL	没有正在等待获取的锁，表示此事务没有阻塞在等待锁
trx_wait_started: NULL	没有进入等待状态的时间，说明没有等待其他事务释放锁
trx_rows_locked: 93883	锁住了 93,883 行，说明这个事务持有大量锁
trx_rows_modified: 0	没有修改任何行，只是加了锁（可能是 SELECT ... FOR UPDATE 或者 REPEATABLE READ 下的 consistent read 导致的大量锁）
trx_tables_locked: 1	锁定了 1 张表
trx_lock_structs: 591	有 591 个锁结构体（锁的单元），也印证锁量很大
trx_isolation_level: REPEATABLE READ	事务隔离级别是默认的 REPEATABLE READ
trx_query: NULL	没有正在运行的 SQL（可能是客户端长时间没有提交事务）

查看 Processlist

SELECT * FROM information_schema.processlist WHERE ID = 17645230

+----------+------+----------------------+------------+---------+------+-------+------+
| ID       | USER | HOST                 | DB         | COMMAND | TIME | STATE | INFO |
+----------+------+----------------------+------------+---------+------+-------+------+
| 17645230 | root | 192.168.192.26:43445 | foobar_code | Sleep   | 4273 |       | NULL |
+----------+------+----------------------+------------+---------+------+-------+------+

• 执行了 4273 秒。
• INFO 为 NULL 表示没有 SQL，可能是事务未提交。

Kill 掉 Process

KILL 17645230;

关系

SELECT
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    COUNT(r.trx_id) AS waiting_count
FROM
    information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
GROUP BY
    b.trx_id, b.trx_mysql_thread_id;

查看具体锁信息

SELECT * FROM information_schema.innodb_locks;

mysql> SELECT * FROM information_schema.innodb_locks;
+--------------------+-------------+-----------+-----------+-----------------------------------------+------------+------------+-----------+----------+-----------+
| lock_id            | lock_trx_id | lock_mode | lock_type | lock_table                              | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------------+-------------+-----------+-----------+-----------------------------------------+------------+------------+-----------+----------+-----------+
| 875452846:4608:3:2 | 875452846   | X         | RECORD    | `foobar_code`.`branch_hide_rule_assigns` | PRIMARY    |       4608 |         3 |        2 | 1         |
| 875452845:4608:3:2 | 875452845   | X         | RECORD    | `foobar_code`.`branch_hide_rule_assigns` | PRIMARY    |       4608 |         3 |        2 | 1         |
| 875452845:2879:7:2 | 875452845   | X         | RECORD    | `foobar_code`.`members`                  | PRIMARY    |       2879 |         7 |        2 | 1         |
| 875395059:2879:7:2 | 875395059   | X         | RECORD    | `foobar_code`.`members`                  | PRIMARY    |       2879 |         7 |        2 | 1         |
+--------------------+-------------+-----------+-----------+-----------------------------------------+------------+------------+-----------+----------+-----------+