MySQL資料庫故障分析-鎖等待(一)
環境說明：
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，執行資料庫備份，被阻塞。
執行備份
[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';
###chenjuchao 20220612###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2900124/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 故障分析 | MySQL鎖等待超時一例分析MySql
- mysql鎖等待查詢分析MySql
- 故障分析 | MySQL死鎖案例分析MySql
- MySQL鎖等待與死鎖問題分析MySql
- MySQL（一）：MySQL資料庫事務與鎖MySql資料庫
- 故障分析 | MySQL 資料庫升級後，資料庫怎麼卡住了MySql資料庫
- mysql InnoDB鎖等待的檢視及分析MySql
- MySQL 死鎖和鎖等待MySql
- MySQL資料庫鎖介紹MySql資料庫
- MySQL資料庫-鎖詳解MySql資料庫
- 資料庫監聽夯故障分析資料庫
- 【EM】鎖等待故障模擬及排查
- 故障分析 | MySQL 5.7 使用臨時表導致資料庫 CrashMySql資料庫
- 資料庫篇：mysql鎖詳解資料庫MySql
- 怎樣快速查出Oracle 資料庫中的鎖等待Oracle資料庫
- 故障分析 | DROP 大表造成資料庫假死資料庫
- 3節點RAC資料庫夯故障分析資料庫
- MySQL 資料庫鎖定機制？這篇文章為你分析清楚~MySql資料庫
- MySQL資料庫優化分析MySql資料庫優化
- 檢視MySQL鎖等待的原因MySql
- 關於資料庫事務和鎖的一些分析資料庫
- MySQL使用event等待事件進行資料庫效能診斷MySql事件資料庫
- 【資料庫——鎖】資料庫
- 故障分析 | MySQL 異地從庫複製延遲案例一則MySql
- 某物流系統資料庫故障診斷案例分析資料庫
- 【資料庫】MySQL鎖機制、熱備、分表資料庫MySql
- 資料庫系列：MySQL InnoDB鎖機制介紹資料庫MySql
- MySQL：一個死鎖分析 (未分析出來的死鎖)MySql
- 透過等待看資料庫資料庫
- MySQL鎖分析MySql
- tempdb大量閂鎖等待問題分析
- 關於資料庫故障資料庫
- 故障分析 | MySQL 使用 load data 匯入資料錯誤的一個場景MySql
- 如何透過SQLyog分析MySQL資料庫MySql資料庫
- 【MySQL】資料庫事務深入分析MySql資料庫
- mysql 資料庫效能分析工具簡介MySql資料庫
- 故障分析 | 血的教訓-由慢查詢引發的備份等待導致資料庫連線打滿資料庫
- MySQL資料庫引擎、事務隔離級別、鎖MySql資料庫