dead_lock
背景：某日线上服务出现了死锁，紧急修复之后，对问题处理流程做如下总结
# 原因分析
代码是不会骗人的，出现死锁 99.9% 是代码写出了 bug
问题处理流程：
- 通过运维连接 mysql，查看锁信息
- 通过锁信息，分析强制删除锁会不会造成数据错乱或数据丢失
- 查找死锁代码可能出现的位置
- 找到死锁进程，在确认安全的情况下结束进程
kill 3872544
MySQL [(none)]> select * from information_schema.metadata_lock_info;
+-----------+-------------+-------------------------+------------------------+-----------------+--------------------+-------------------+
| THREAD_ID | LOCK_STATUS | LOCK_MODE | LOCK_TYPE | LOCK_DURATION | TABLE_SCHEMA | TABLE_NAME |
+-----------+-------------+-------------------------+------------------------+-----------------+--------------------+-------------------+
| 3872544 | GRANTED | MDL_EXCLUSIVE | Table metadata lock | MDL_STATEMENT | tradedesk | projects |
| 3872544 | GRANTED | MDL_EXCLUSIVE | Table metadata lock | MDL_STATEMENT | tradedesk | accounts |
| 3872544 | GRANTED | MDL_EXCLUSIVE | Table metadata lock | MDL_STATEMENT | tradedesk | account_tokens |
| 3872544 | GRANTED | MDL_EXCLUSIVE | Table metadata lock | MDL_STATEMENT | tradedesk | #sql-970_3b1720 |
| 3872544 | GRANTED | MDL_SHARED_UPGRADABLE | Table metadata lock | MDL_STATEMENT | tradedesk | users |
| 3872544 | GRANTED | MDL_SHARED_UPGRADABLE | Table metadata lock | MDL_STATEMENT | tradedesk | projects |
| 3872544 | GRANTED | MDL_SHARED_UPGRADABLE | Table metadata lock | MDL_STATEMENT | tradedesk | accounts |
| 3872544 | GRANTED | MDL_SHARED_UPGRADABLE | Table metadata lock | MDL_STATEMENT | tradedesk | account_tokens |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | MDL_STATEMENT | tradedesk | |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Backup tables lock | MDL_STATEMENT | | |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Global read lock | MDL_STATEMENT | | |
| 3872544 | GRANTED | MDL_EXCLUSIVE | Column statistics lock | MDL_TRANSACTION | tradedesk | studios |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Tablespace lock | MDL_TRANSACTION | | tradedesk/studios |
| 3872544 | GRANTED | MDL_EXCLUSIVE | Table metadata lock | MDL_TRANSACTION | tradedesk | studios |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | MDL_TRANSACTION | tradedesk | |
| 3872544 | GRANTED | MDL_INTENTION_EXCLUSIVE | Backup lock | MDL_TRANSACTION | | |
| 3872544 | PENDING | MDL_EXCLUSIVE | Table metadata lock | | tradedesk | users |
...
| 3875615 | GRANTED | MDL_SHARED_READ | Table metadata lock | MDL_TRANSACTION | mysql | tables |
| 3875615 | GRANTED | MDL_SHARED_READ | Table metadata lock | MDL_TRANSACTION | information_schema | TABLES |
| 3875615 | PENDING | MDL_SHARED_HIGH_PRIO | Table metadata lock | | tradedesk | account_tokens |
| 3873911 | PENDING | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | | tradedesk | |
| 3875711 | PENDING | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | | tradedesk | |
| 3871263 | PENDING | MDL_SHARED | Table metadata lock | | tradedesk | users |
+-----------+-------------+-------------------------+------------------------+-----------------+--------------------+-------------------+
81 rows in set (0.01 sec)
MySQL [(none)]> show processlist;
+---------+-----------+----------------------+-----------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------+----------------------+-----------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 90 | rdsRepl | 192.168.26.245:60494 | NULL | Binlog Dump GTID | 2501163 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 3852622 | tradedesk | 10.116.1.202:41762 | tradedesk | Sleep | 15006 | | NULL |
| 3853008 | tradedesk | 10.116.1.202:50804 | tradedesk | Sleep | 16095 | | NULL |
| 3871263 | tradedesk | 10.116.1.202:54082 | tradedesk | Prepare | 2799 | Waiting for table metadata lock | SELECT * FROM `users` WHERE username = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMI |
| 3871264 | tradedesk | 10.116.1.202:54084 | tradedesk | Prepare | 2656 | Waiting for table metadata lock | SELECT * FROM `users` WHERE username = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMI |
| 3872544 | tradedesk | 10.116.1.178:59984 | tradedesk | Query | 3525 | Waiting for table metadata lock | ALTER TABLE `studios` MODIFY COLUMN `casbin_key` varchar(32) |
| 3872730 | tradedesk | 10.116.1.178:34070 | NULL | Query | 3405 | Waiting for schema metadata lock | CREATE DATABASE IF NOT EXISTS tradedesk |
| 3872926 | tradedesk | 10.116.1.178:36374 | NULL | Connect | 3285 | Waiting for schema metadata lock | NULL |
| 3873107 | tradedesk | 10.116.1.178:38702 | NULL | Connect | 3165 | Waiting for schema metadata lock | NULL |
| 3873304 | tradedesk | 10.116.1.178:41066 | NULL | Connect | 3045 | Waiting for schema metadata lock | NULL |
| 3875516 | tradedesk | 10.116.1.182:54220 | NULL | Connect | 1601 | Waiting for schema metadata lock | NULL |
| 3875612 | root | 100.125.7.128:48140 | NULL | Query | 1544 | Waiting for table metadata lock | /* sql from das */select TABLE_NAME, CREATE_TIME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLA |
| 3875615 | root | 100.125.7.127:55134 | NULL | Query | 1472 | Waiting for table metadata lock | /* sql from das */select TABLE_NAME, CREATE_TIME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLA |
| 3875711 | tradedesk | 10.116.1.182:56344 | NULL | Connect | 1481 | Waiting for schema metadata lock | NULL |
| 3875773 | root | 100.125.7.127:55422 | NULL | Query | 1439 | Waiting for table metadata lock | /* sql from das */select TABLE_NAME, CREATE_TIME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLA |
| 3875795 | root | 100.125.141.68:56998 | NULL | Query | 1426 | Waiting for schema metadata lock | use `tradedesk` |
| 3875892 | tradedesk | 10.116.1.182:58388 | NULL | Connect | 1361 | Waiting for schema metadata lock | NULL |
| 3875977 | root | 10.103.101.233:56298 | NULL | Init DB | 1298 | Waiting for schema metadata lock | NULL |
| 3876820 | tradedesk | 10.116.1.202:36044 | NULL | Connect | 749 | Waiting for schema metadata lock | NULL |
| 3876908 | tradedesk | 10.116.1.202:37036 | NULL | Connect | 690 | Waiting for schema metadata lock | NULL |
| 3877544 | tradedesk | 10.116.1.202:43948 | NULL | Connect | 273 | Waiting for schema metadata lock | NULL |
| 3877547 | tradedesk | 10.116.1.202:43970 | NULL | Connect | 271 | Waiting for schema metadata lock | NULL |
| 3877554 | tradedesk | 10.116.1.202:44018 | NULL | Connect | 267 | Waiting for schema metadata lock | NULL |
| 3877857 | root | 10.103.101.233:56390 | NULL | Query | 0 | init | show processlist |
| 3877866 | root | 100.125.141.68:60558 | NULL | Sleep | 60 | | NULL |
+---------+-----------+----------------------+-----------+------------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
45 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
54
55
56
57
58
59
60
61
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
54
55
56
57
58
59
60
61
MySQL [tradedesk]> select \* from information_schema.INNODB_TRX\G
\***\*\*\*\*\*\*\***\*\*\*\***\*\*\*\*\*\*\*** 1. row \***\*\*\*\*\*\*\***\*\*\*\***\*\*\*\*\*\*\***
trx_id: 421450135639512
trx_state: RUNNING
trx_started: 2022-12-28 14:32:23
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 3852622
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
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_schedule_weight: NULL
trx_waited_time: 0
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
> kill 3872544
1
编辑 (opens new window)
上次更新: 2023/02/24, 10:34:03