这两天业务同事反馈,线上任务在执行时总是卡住,哪怕是单独跑个 UPDATE 或 INSERT 也会莫名其妙地卡死。翻了下数据库日志,居然一点报错都没有,真让人摸不着头脑。
用 SHOW PROCESSLIST 查了下当前线程,发现一堆会话都在等元数据锁:

心里咯噔一下,赶紧去数据库里跑了语句
show engine innodb status \G
[BEGIN] 2025/6/11 15:21:05
MySQL localhost:33060+ ssl performance_schema SQL > show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-06-11 15:21:11 140691319379712 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5777826 srv_active, 0 srv_shutdown, 2709 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2075061
OS WAIT ARRAY INFO: signal count 4838943
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-06-11 00:27:49 140671689156352
*** (1) TRANSACTION:
TRANSACTION 1164427027, ACTIVE 6 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 206 lock struct(s), heap size 24696, 9181 row lock(s)
MySQL thread id 2764567, OS thread handle 140634509567744, query id 722262863 ops-mysql-cluster-3 10.0.53.12 rolling-forecasting updating
UPDATE project_ledger SET project_switch=1
WHERE is_delete=1
AND (wait_project_code IN ('00220244F2E1CC0HvjGe') AND on_landing = 0)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 4309 page no 233 n bits 120 index PRIMARY of table `rolling_forecasting_prod`.`project_ledger` trx id 1164427027 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 84; compact format; info bits 64
0: len 8; hex 80000000000022f5; asc " ;;
1: len 6; hex 0000453166f6; asc E1f ;;
2: len 7; hex 02000001e91bf2; asc ;;
3: SQL NULL;
4: SQL NULL;
5: len 6; hex 436f66666565; asc Coffee;;
6: len 14; hex 504d323032333034313230303033; asc PM202304120003;;
7: len 0; hex ; asc ;;
8: SQL NULL;
9: len 8; hex 800000000000eb18; asc ;;
10: len 12; hex e8819ae5889be699bae8a18c; asc ;;
11: len 8; hex 8000000000002d81; asc - ;;
12: len 9; hex e69da8e6aca7e6a2a6; asc ;;
13: len 15; hex e6aca7e88fb2e58589e99b86e59ba2; asc ;;
14: SQL NULL;
15: len 1; hex 80; asc ;;
16: len 12; hex e9a1b9e79baee7ab8be9a1b9; asc ;;
17: len 6; hex e4b8ade59bbd; asc ;;
18: len 1; hex 31; asc 1;;
19: len 3; hex 313030; asc 100;;
20: len 1; hex 80; asc ;;
21: len 15; hex e4baa4e4bb98e59e8be4b89ae58aa1; asc ;;
22: len 1; hex 80; asc ;;
23: len 21; hex e8bdafe4bbb6e4baa4e4bb98efbc884e5245efbc89; asc NRE ;;
24: len 1; hex 85; asc ;;
25: len 12; hex e7a094e58f91e7bb93e9a1b9; asc ;;
26: len 3; hex 8fce74; asc t;;
27: len 3; hex 8fd0bf; asc ;;
28: SQL NULL;
29: len 6; hex 8000066a9350; asc j P;;
30: len 5; hex 8000002d00; asc - ;;
31: len 3; hex 801400; asc ;;
32: len 6; hex 8000038f974a; asc J;;
33: SQL NULL;
34: SQL NULL;
35: len 1; hex 89; asc ;;
36: len 15; hex e5ae8ce5b7a5e799bee58886e6af94; asc ;;
37: len 1; hex 81; asc ;;
38: len 1; hex 80; asc ;;
39: len 8; hex 8000000000002d81; asc - ;;
40: len 9; hex e69da8e6aca7e6a2a6; asc ;;
41: len 8; hex 8000000000002d81; asc - ;;
42: len 9; hex e69da8e6aca7e6a2a6; asc ;;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: len 6; hex 800000000000; asc ;;
48: len 6; hex 800000c5a942; asc B;;
49: len 6; hex 8000006db24c; asc m L;;
50: len 12; hex 32303233e5b9b43034e69c88; asc 2023 04 ;;
51: len 6; hex 800000c5a942; asc B;;
52: len 6; hex 8000006db24c; asc m L;;
53: len 4; hex 80000001; asc ;;
54: len 6; hex 800003917d4d; asc }M;;
55: len 12; hex 32303233e5b9b43034e69c88; asc 2023 04 ;;
56: SQL NULL;
57: len 8; hex 97c11adf0e210091; asc ! ;;
58: len 4; hex 8000000d; asc ;;
59: len 4; hex 80000104; asc ;;
60: len 4; hex 80000008; asc ;;
61: len 7; hex 4a6f75726e6579; asc Journey;;
62: len 4; hex 80000002; asc ;;
63: len 7; hex 4a6f75726e6579; asc Journey;;
64: len 4; hex 8000002c; asc ,;;
65: SQL NULL;
66: SQL NULL;
67: len 4; hex 80000000; asc ;;
68: len 4; hex 80000000; asc ;;
69: len 4; hex 80000000; asc ;;
70: len 4; hex 80000001; asc ;;
71: len 6; hex 8000066a9350; asc j P;;
72: len 6; hex 8000038f974a; asc J;;
73: len 4; hex 80000000; asc ;;
74: len 4; hex 80000000; asc ;;
75: len 1; hex 81; asc ;;
76: SQL NULL;
77: len 8; hex 8000000000000000; asc ;;
78: len 4; hex 65a179ec; asc e y ;;
79: len 4; hex 68473273; asc hG2s;;
80: len 1; hex 30; asc 0;;
81: SQL NULL;
82: SQL NULL;
83: SQL NULL;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 84; compact format; info bits 64
0: len 8; hex 80000000000022f6; asc " ;;
1: len 6; hex 0000453166f6; asc E1f ;;
2: len 7; hex 020000014f38bd; asc O8 ;;
3: SQL NULL;
4: SQL NULL;
5: len 16; hex 53616c65735f454d4b414d5f32303234; asc Sales_EMKAM_2024;;
6: len 14; hex 504d323032333132323930303236; asc PM202312290026;;
7: len 0; hex ; asc ;;
8: SQL NULL;
9: len 8; hex 8000000000000420; asc ;;
10: len 24; hex e696b0e585b4e5b882e59cbae5aea2e688b7e4b8ade5bf83; asc ;;
11: len 8; hex 8000000000011f71; asc q;;
12: len 9; hex e5a79ce4bd9ce8b685; asc ;;
13: SQL NULL;
14: SQL NULL;
15: len 1; hex 82; asc ;;
16: len 12; hex e5bfabe9809fe7ab8be9a1b9; asc ;;
17: SQL NULL;
18: len 1; hex 31; asc 1;;
19: len 3; hex 313030; asc 100;;
20: len 1; hex 85; asc ;;
21: len 0; hex ; asc ;;
22: SQL NULL;
23: len 0; hex ; asc ;;
24: len 1; hex 82; asc ;;
25: len 6; hex e7bb93e9a1b9; asc ;;
26: len 3; hex 8fcf99; asc ;;
27: len 3; hex 8fd198; asc ;;
28: len 3; hex 8fd198; asc ;;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: len 1; hex 8c; asc ;;
36: len 9; hex e697a0e694b6e585a5; asc ;;
37: len 1; hex 81; asc ;;
38: len 1; hex 80; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 8; hex 800000000000281c; asc ( ;;
42: len 6; hex e8969be6b4aa; asc ;;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: len 6; hex 800000000000; asc ;;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: len 4; hex 80000000; asc ;;
54: len 6; hex 800000000000; asc ;;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: len 4; hex 80000019; asc ;;
59: len 4; hex 800000d2; asc ;;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: len 6; hex 800000000000; asc ;;
66: len 6; hex 800000000000; asc ;;
67: len 4; hex 80000005; asc ;;
68: len 4; hex 80000008; asc ;;
69: len 4; hex 80000003; asc ;;
70: len 4; hex 80000001; asc ;;
71: SQL NULL;
72: SQL NULL;
73: len 4; hex 80000000; asc ;;
74: len 4; hex 80000000; asc ;;
75: len 1; hex 81; asc ;;
76: SQL NULL;
77: len 8; hex 8000000000000000; asc ;;
78: len 4; hex 65a179ec; asc e y ;;
79: len 4; hex 6847327e; asc hG2~;;
80: len 1; hex 30; asc 0;;
81: SQL NULL;
82: SQL NULL;
83: SQL NULL;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 84; compact format; info bits 64
0: len 8; hex 80000000000022f7; asc " ;;
1: len 6; hex 0000453166f6; asc E1f ;;
2: len 7; hex 02000001540f26; asc T &;;
3: SQL NULL;
4: SQL NULL;
5: len 16; hex 53616c65735f436c6f75645f32303234; asc Sales_Cloud_2024;;
6: len 14; hex 504d323032333132323730303034; asc PM202312270004;;
7: len 0; hex ; asc ;;
8: SQL NULL;
9: len 8; hex 800000000000034d; asc M;;
10: len 27; hex e8a18ce4b89ae8bdafe4bbb6e69c8de58aa1e4ba8be4b89ae983a8; asc ;;
11: len 8; hex 8000000000002f78; asc /x;;
12: len 6; hex e69cb1e790b3; asc ;;
13: SQL NULL;
14: SQL NULL;
15: len 1; hex 82; asc ;;
16: len 12; hex e5bfabe9809fe7ab8be9a1b9; asc ;;
17: SQL NULL;
18: len 1; hex 31; asc 1;;
19: len 3; hex 313030; asc 100;;
20: len 1; hex 85; asc ;;
21: len 0; hex ; asc ;;
22: SQL NULL;
23: len 0; hex ; asc ;;
24: len 1; hex 85; asc ;;
25: len 12; hex e7a094e58f91e7bb93e9a1b9; asc ;;
26: len 3; hex 8fcf99; asc ;;
27: len 3; hex 8fd199; asc ;;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: len 1; hex 8c; asc ;;
36: len 9; hex e697a0e694b6e585a5; asc ;;
37: len 1; hex 81; asc ;;
38: len 1; hex 80; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 8; hex 80000000000025b1; asc % ;;
42: len 9; hex e4ba8ee79dbfe5b3b0; asc ;;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: len 6; hex 800000000000; asc ;;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: len 4; hex 80000000; asc ;;
54: len 6; hex 800000000000; asc ;;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: len 4; hex 80000019; asc ;;
59: len 4; hex 800000d2; asc ;;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: len 6; hex 800000000000; asc ;;
66: len 6; hex 800000000000; asc ;;
67: len 4; hex 80000005; asc ;;
68: len 4; hex 80000008; asc ;;
69: len 4; hex 80000003; asc ;;
70: len 4; hex 80000001; asc ;;
71: SQL NULL;
72: SQL NULL;
73: len 4; hex 80000000; asc ;;
74: len 4; hex 80000000; asc ;;
75: len 1; hex 81; asc ;;
76: SQL NULL;
77: len 8; hex 8000000000000000; asc ;;
78: len 4; hex 65a179ec; asc e y ;;
79: len 4; hex 68473280; asc hG2 ;;
80: len 1; hex 30; asc 0;;
81: SQL NULL;
82: SQL NULL;
83: SQL NULL;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 84; compact format; info bits 64
0: len 8; hex 80000000000022f8; asc " ;;
1: len 6; hex 0000453166f6; asc E1f ;;
2: len 7; hex 0200000160300e; asc `0 ;;
3: SQL NULL;
4: SQL NULL;
5: len 15; hex 53616c65735f535643445f32303234; asc Sales_SVCD_2024;;
6: len 14; hex 504d323032333132323730303437; asc PM202312270047;;
7: len 0; hex ; asc ;;
8: SQL NULL;
9: len 8; hex 800000000000004f; asc O;;
10: len 21; hex e699bae883bde6b1bde8bda6e5aea2e688b7e983a8; asc ;;
11: len 8; hex 80000000000026eb; asc & ;;
12: len 6; hex e794b0e7a38a; asc ;;
13: SQL NULL;
14: SQL NULL;
15: len 1; hex 82; asc ;;
16: len 12; hex e5bfabe9809fe7ab8be9a1b9; asc ;;
17: SQL NULL;
18: len 1; hex 31; asc 1;;
19: len 3; hex 313030; asc 100;;
20: len 1; hex 85; asc ;;
21: len 0; hex ; asc ;;
22: SQL NULL;
23: len 0; hex ; asc ;;
24: len 1; hex 82; asc ;;
25: len 6; hex e7bb93e9a1b9; asc ;;
26: len 3; hex 8fcf99; asc ;;
27: len 3; hex 8fd199; asc ;;
28: len 3; hex 8fd198; asc ;;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: len 1; hex 8c; asc ;;
36: len 9; hex e697a0e694b6e585a5; asc ;;
37: len 1; hex 81; asc ;;
38: len 1; hex 80; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 8; hex 80000000000026eb; asc & ;;
42: len 6; hex e794b0e7a38a; asc ;;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: len 6; hex 800000000000; asc ;;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: len 4; hex 80000000; asc ;;
54: len 6; hex 800000000000; asc ;;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: len 4; hex 80000011; asc ;;
59: len 4; hex 800000af; asc ;;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: len 6; hex 800000000000; asc ;;
66: len 6; hex 800000000000; asc ;;
67: len 4; hex 80000005; asc ;;
68: len 4; hex 80000008; asc ;;
69: len 4; hex 80000003; asc ;;
70: len 4; hex 80000001; asc ;;
71: SQL NULL;
72: SQL NULL;
73: len 4; hex 80000000; asc ;;
74: len 4; hex 80000000; asc ;;
75: len 1; hex 81; asc ;;
76: SQL NULL;
77: len 8; hex 8000000000000000; asc ;;
78: len 4; hex 65a179ec; asc e y ;;
79: len 4; hex 68473286; asc hG2 ;;
80: len 1; hex 30; asc 0;;
81: SQL NULL;
82: SQL NULL;
83: SQL NULL;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 84; compact format; info bits 64
0: len 8; hex 80000000000022f9; asc " ;;
1: len 6; hex 0000453166f6; asc E1f ;;
2: len 7; hex 02000001dc3194; asc 1 ;;
3: len 8; hex 8000000000001600; asc ;;
4: len 5; hex 99b4f4f890; asc ;;
5: len 13; hex 57696e646d696c6c5f32303234; asc Windmill_2024;;
6: len 14; hex 504d323032333132323830303038; asc PM202312280008;;
7: len 0; hex ; asc ;;
8: len 30; hex e8949ae69da5e6b1bde8bda6202d20e6aca7e6b4b2e4ba94e59bbde699ba; asc - ; (total 45 bytes);
9: len 8; hex 800000000007c869; asc i;;
10: len 27; hex e699bae883bde6b1bde8bda6e58d8ee4b89ce4ba8be4b89ae983a8; asc ;;
11: len 8; hex 8000000000090090; asc ;;
12: len 20; hex e586afe790b3efbc884a656e6e69666572efbc89; asc Jennifer ;;
13: len 12; hex e8949ae69da5e6b1bde8bda6; asc ;;
14: SQL NULL;
15: len 1; hex 80; asc ;;
16: len 12; hex e9a1b9e79baee7ab8be9a1b9; asc ;;
17: len 6; hex e4b8ade59bbd; asc ;;
18: len 1; hex 31; asc 1;;
19: len 3; hex 313030; asc 100;;
20: len 1; hex 81; asc ;;
21: len 15; hex e4babae58a9be59e8be4b89ae58aa1; asc ;;
22: len 1; hex 91; asc ;;
23: len 18; hex e78eb0e59cbae4babae58a9be69c8de58aa1; asc ;;
24: len 1; hex 85; asc ;;
25: len 12; hex e7a094e58f91e7bb93e9a1b9; asc ;;
26: len 3; hex 8fcf99; asc ;;
27: len 3; hex 8fd198; asc ;;
28: SQL NULL;
29: len 6; hex 80000d674000; asc g@ ;;
30: len 5; hex 8000001e00; asc ;;
31: len 3; hex 801400; asc ;;
32: len 6; hex 80000969ab17; asc i ;;
33: SQL NULL;
34: SQL NULL;
35: len 1; hex 8a; asc ;;
36: len 9; hex e7bb93e7ae97e58d95; asc ;;
37: len 1; hex 81; asc ;;
38: len 1; hex 80; asc ;;
39: len 8; hex 800000000000008b; asc ;;
40: len 9; hex e8b0a2e79c9fe99396; asc ;;
41: len 8; hex 80000000000001ad; asc ;;
42: len 9; hex e78e8be98791e9be99; asc ;;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: len 6; hex 800000000000; asc ;;
48: len 6; hex 800000e96b0a; asc k ;;
49: len 6; hex 800000a3ec1e; asc ;;
50: len 12; hex 32303233e5b9b43132e69c88; asc 2023 12 ;;
51: len 6; hex 800000e96b0a; asc k ;;
52: len 6; hex 800000a3ec1e; asc ;;
53: len 4; hex 80000001; asc ;;
54: len 6; hex 80000b8be350; asc P;;
55: len 12; hex 32303233e5b9b43132e69c88; asc 2023 12 ;;
56: SQL NULL;
57: len 8; hex 97c11ae02761000a; asc 'a ;;
58: len 4; hex 8000000a; asc ;;
59: len 4; hex 800000a9; asc ;;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: len 4; hex 8000001e; asc ;;
65: SQL NULL;
66: SQL NULL;
67: len 4; hex 80000000; asc ;;
68: len 4; hex 80000001; asc ;;
69: len 4; hex 80000000; asc ;;
70: len 4; hex 80000002; asc ;;
71: len 6; hex 80000d674000; asc g@ ;;
72: len 6; hex 80000969ab17; asc i ;;
73: len 4; hex 80000000; asc ;;
74: len 4; hex 80000000; asc ;;
75: len 1; hex 81; asc ;;
76: SQL NULL;
77: len 8; hex 8000000000000000; asc ;;
78: len 4; hex 65a179ec; asc e y ;;
79: len 4; hex 68473269; asc hG2i;;
80: len 1; hex 30; asc 0;;
81: SQL NULL;
82: SQL NULL;
83: SQL NULL;
死锁是有,但问题不止于此。因为不光是这张表,别的表的操作也都卡着,明显不是单纯的死锁能解释的。继续排查,发现其实更多的是行级锁在作怪。
又查了下当前数据库的锁情况

(顺便贴一张问题解决后的截图,心情舒畅多了)

从现象来看,整个库都被锁得死死的。对比了下其他库,发现人家 DML 操作都很顺畅,没半点卡顿。初步怀疑是不是有啥批量任务在跑,赶紧去问开发,结果人家说啥都没动,这下更迷糊了。
翻了下 error.log,依然没有任何异常信息。

没办法,只能硬着头皮一个个排查。随便挑了一个在等元数据锁的会话,查查它到底在等谁。用下面这条 SQL:
SELECT
l.OBJECT_TYPE, l.OBJECT_SCHEMA, l.OBJECT_NAME, l.LOCK_TYPE, l.LOCK_STATUS,
t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST, t.PROCESSLIST_COMMAND, t.PROCESSLIST_INFO
FROM
performance_schema.metadata_locks l
JOIN performance_schema.threads t ON l.OWNER_THREAD_ID = t.THREAD_ID
WHERE
l.OBJECT_NAME = 'ResourceEmployeeBindDepartment'
AND l.OBJECT_SCHEMA = 'prod_resource_system'
AND l.LOCK_STATUS = 'GRANTED';
输出结果如下
| TABLE | prod_resource_system | resourceemployeebinddepartment | SHARED_READ | GRANTED | 47122 | prod-resource-system | ops-mysql-cluster-2 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `resourceemployeejoinproject` |
一看是个查询语句持有元数据锁,心想这不科学啊,正常查表怎么会锁住元数据?管不了那么多,先 kill 47122 试试。
kill 47122
结果所有的元数据锁等待瞬间消失,业务同事再跑任务,十秒钟就搞定,卡顿全无。
回头再看这个 SQL:
| SELECT /*!40001 SQL_NO_CACHE */ * FROM `resourceemployeejoinproject` |
不就是个普通的全表查询吗?突然灵光一闪,会不会是 mysqldump 导出惹的祸?赶紧去问开发,果然,这两天他们在跑 mysqldump 备份。更要命的是,脚本里啥参数都没加。
查了下 mysqldump 的机制,默认会先全局加读锁(FLUSH TABLES WITH READ LOCK),防止 DDL 和新事务写入,然后对每个表再加读锁:
LOCK TABLES `table_name` READ;
这样一来,表就被锁住了,写操作全都得等着,难怪业务全线卡死。
问题终于水落石出:一次看似普通的 mysqldump 导出,居然能把整个系统锁到停摆。以后生产环境备份 InnoDB 表,一定要加上 --single-transaction 参数,否则真是“坑”到怀疑人生。
小结:
遇到数据库卡顿、锁等待,别忘了排查有没有备份、导出等操作在后台搞事情。mysqldump 虽小,威力不容小觑。




