环境说明:
MySQL:5.7.34 双主
OS:Redhat 7.5
问题现象:
XXX应用登录,提示数据库连接失败。
问题原因:
有一张权限表,同时执行了delete和truncate操作,并且长时间没有提交,导致metadata lock无法释放,应用登录时无法正常读取权限表,导致应用无法登录。
初步怀疑应用在执行delete操作时开启了事务,并且没有及时提交,导致锁无法释放。
解决方案:
和应用相关同事沟通,杀掉相关会话后,恢复正常。
问题重现:
1.开启锁监控。
MySQL [cjc]> select * from performance_schema.setup_instruments where name like ‘%lock%’ limit 20; ±--------------------------------------------------------±--------±------+ | NAME | ENABLED | TIMED | ±--------------------------------------------------------±--------±------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync | NO | NO | | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue | NO | NO | ±--------------------------------------------------------±--------±------+ 20 rows in set (0.00 sec) |
---|
开启监控
MySQL [cjc]> update performance_schema.setup_instruments set enabled = ‘YES’ where name like ‘%lock%’; Query OK, 173 rows affected (0.00 sec) Rows matched: 180 Changed: 173 Warnings: 0 |
---|
2.关闭自动提交
注意:关闭自动提交的用户权限需要小于启动mysql的用户权限,否则关闭自动提交不生效,而且没有任何提示。
MySQL [cjc]> set autocommit=0; Query OK, 0 rows affected (0.00 sec) MySQL [cjc]> show variables like ‘autocommit’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | autocommit | OFF | ±--------------±------+ 1 row in set (0.00 sec) |
---|
3.会话1,删除表,不提交。
delete from t2;
4.会话2,可以正常查询表数据。
MySQL [cjc]> select * from t2; ±-----±-----+ | id | age | ±-----±-----+ | 1 | 100 | | 2 | 30 | | 3 | 80 | ±-----±-----+ 3 rows in set (0.01 sec) |
---|
5.会话3,执行truncate操作,被阻塞
truncate table t2;
卡住
6.会话2,仍然可以查询表数据。
MySQL [cjc]> select * from t2; ±-----±-----+ | id | age | ±-----±-----+ | 1 | 100 | | 2 | 30 | | 3 | 80 | ±-----±-----+ 3 rows in set (0.01 sec) |
---|
7.打开另一个新的会话4,无法查询数据,被阻塞
MySQL [cjc]> select * from t2;
卡住
8.打开会话5,执行数据库备份,被阻塞。
执行备份
1 | [mysql@mysql01 backup]$ mysqldump -uroot -p cjc > /home/mysql/backup/cjc.sql |
---|
卡住
9.打开会话6,查询会话信息id 16,31,33都被阻塞
MySQL [(none)]> select id,host,user,command,time,state,info from information_schema.processlist where command !=‘sleep’; ±—±----------±-----±--------±-----±--------------------------------±--------------------------------------------------------------------------------------------------------+ | id | host | user | command | time | state | info | ±—±----------±-----±--------±-----±--------------------------------±--------------------------------------------------------------------------------------------------------+ | 34 | localhost | root | Query | 0 | executing | select id,host,user,command,time,state,info from information_schema.processlist where command !=‘sleep’ | | 31 | localhost | cjc | Query | 253 | Waiting for table metadata lock | select * from t2 | | 16 | localhost | cjc | Query | 269 | Waiting for table metadata lock | truncate table t2 | | 33 | localhost | root | Query | 171 | Waiting for table metadata lock | LOCK TABLES t1 READ /*!32311 LOCAL /,t2 READ /!32311 LOCAL */ | ±—±----------±-----±--------±-----±--------------------------------±--------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) |
---|
10.查看锁阻塞源头
MySQL [(none)]> select * from performance_schema.metadata_locks where OWNER_THREAD_ID !=sys.ps_thread_id(connection_id())\G; *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: cjc OBJECT_NAME: t2 OBJECT_INSTANCE_BEGIN: 139671541629072 LOCK_TYPE: SHARED_WRITE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 37 OWNER_EVENT_ID: 36 *************************** 2. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 139671933907680 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: STATEMENT LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 41 OWNER_EVENT_ID: 26 *************************** 3. row *************************** OBJECT_TYPE: SCHEMA OBJECT_SCHEMA: cjc OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 139671933882208 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 41 OWNER_EVENT_ID: 26 *************************** 4. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: cjc OBJECT_NAME: t2 OBJECT_INSTANCE_BEGIN: 139671933942976 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: OWNER_THREAD_ID: 41 OWNER_EVENT_ID: 26 *************************** 5. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: cjc OBJECT_NAME: t2 OBJECT_INSTANCE_BEGIN: 139671799800656 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: OWNER_THREAD_ID: 56 OWNER_EVENT_ID: 9 *************************** 6. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: cjc OBJECT_NAME: t1 OBJECT_INSTANCE_BEGIN: 139672135191008 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 58 OWNER_EVENT_ID: 10 *************************** 7. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: cjc OBJECT_NAME: t2 OBJECT_INSTANCE_BEGIN: 139672135191104 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: OWNER_THREAD_ID: 58 OWNER_EVENT_ID: 10 7 rows in set (0.10 sec) ERROR: No query specified MySQL [(none)]> select THREAD_ID,PROCESSLIST_ID,name,type from performance_schema.threads where PROCESSLIST_ID is not NULL; ±----------±---------------±-------------------------------±-----------+ | THREAD_ID | PROCESSLIST_ID | name | type | ±----------±---------------±-------------------------------±-----------+ | 28 | 3 | thread/sql/compress_gtid_table | FOREGROUND | | 37 | 12 | thread/sql/one_connection | FOREGROUND | | 41 | 16 | thread/sql/one_connection | FOREGROUND | | 56 | 31 | thread/sql/one_connection | FOREGROUND | | 57 | 32 | thread/sql/one_connection | FOREGROUND | | 58 | 33 | thread/sql/one_connection | FOREGROUND | | 59 | 34 | thread/sql/one_connection | FOREGROUND | ±----------±---------------±-------------------------------±-----------+ 7 rows in set (0.00 sec |
---|
可以看到阻塞源头是THREAD_ID=37 PROCESSLIST_ID=12
| 37 | 12 | thread/sql/one_connection | FOREGROUND | select * from performance_schema.events_statements_current where thread_id=37\G; |
---|
查看最后一次执行的命令
MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=37\G; *************************** 1. row *************************** THREAD_ID: 37 EVENT_ID: 35 END_EVENT_ID: 35 EVENT_NAME: statement/sql/delete SOURCE: TIMER_START: 6409813713486000 TIMER_END: 6409813929865000 TIMER_WAIT: 216379000 LOCK_TIME: 64000000 SQL_TEXT: delete from t2 DIGEST: 3ba93c5fbd4c5721001bc1856c74459a DIGEST_TEXT: DELETE FROM t2 CURRENT_SCHEMA: cjc OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: 00000 MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 3 ROWS_SENT: 0 ROWS_EXAMINED: 3 CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 0 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 0 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL NESTING_EVENT_LEVEL: 0 1 row in set (0.00 sec) ERROR: No query specified |
---|
在刚刚执行delete操作的窗口查询,确实是12。
MySQL [cjc]> select connection_id(); ±----------------+ | connection_id() | ±----------------+ | 12 | ±----------------+ 1 row in set (0.00 sec) |
---|
11.终止12会话
MySQL [(none)]> kill 12;
Query OK, 0 rows affected (0.00 sec)
12.验证
查询t2数据已经被清空
MySQL [(none)]> select * from cjc.t2;
Empty set (0.00 sec)
锁已释放
MySQL [(none)]> select id,host,user,command,time,state,info from information_schema.processlist where command !='sleep' and user !='root';
源码附件已经打包好上传到百度云了,大家自行下载即可~
链接: https://pan.baidu.com/s/14G-bpVthImHD4eosZUNSFA?pwd=yu27
提取码: yu27
百度云链接不稳定,随时可能会失效,大家抓紧保存哈。
如果百度云链接失效了的话,请留言告诉我,我看到后会及时更新~
码云地址: http://github.crmeb.net/u/defu
Github 地址: http://github.crmeb.net/u/defu
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。