MySQL鎖問題分析-全域性讀鎖
MySQL鎖分類
MySQL鎖的分類有很多種，其中根據影響範圍來劃分主要分為全域性鎖、表鎖、行鎖。
MySQL鎖實現
MySQL資料庫裡面的鎖是基於索引實現的，在Innodb中鎖都是作用在索引上面的，當SQL命中索引時，那麼鎖住的就是命中條件內的索引節點(行鎖)，如果沒有命中索引的話，那我們鎖的就是整個索引樹（表鎖）。
全域性讀鎖
MySQL 全域性鎖會申請一個全域性的讀鎖，對整個庫加鎖。
1.備份時為了得到一致性備份，可能會新增全域性讀鎖。
2.主從複製架構下，主備切換可能會用到全域性讀鎖。
全域性鎖的實現方式有兩種：
//第一種方法Flush tables with read lock（FTWRL）
//第二種方法set global readonly=true
當資料庫處於全域性鎖的狀態時，其他執行緒的以下語句會被阻塞：
資料更新語句（資料的增刪改）、資料定義語句（建表、索引變更、修改表結構等）和更新類事務的提交語句。
釋放全域性鎖
unlock tables;
全域性讀鎖問題分析
在MySQL5.7之前的版本，要排查誰持有全域性讀鎖，通常在資料庫層面是很難直接查詢到有用資料的(innodb_locks表也只能記錄InnoDB層的鎖資訊，而全域性讀鎖是Server層的鎖，所以無法查詢到)。
從MySQL5.7版本開始提供了performance_schema.metadata_locks表，用來記錄一下Server層的鎖資訊(包括全域性讀鎖和MDL鎖等)。
下面透過示例演示如何找出誰持有全域性讀鎖：
資料庫版本MySQL5.7.35
MySQL [cjcdb]> select version(); +------------+ | version() | +------------+ | 5.7.35-log | +------------+ 1 row in set (0.00 sec)
建立測試資料
MySQL [cjcdb]> create table t1(id int,age int); Query OK, 0 rows affected (0.04 sec) MySQL [cjcdb]> insert into t1 values(1,100),(2,30),(3,80); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
開啟鎖監控
performance_schema引數：
在MySQL 5.7中該引數預設關係，需要手動開啟該引數並重啟資料庫例項生效，MySQL 8.0 開始預設開始該引數。
setup_instruments表：
事件採集配置項表，可指定具體採集項單獨開啟進行開啟，MySQL對指定事件進行監控採集。
setup_consumers表：
事件儲存配置表，可指定具體的監控採集專案進行開啟，MySQL對setup_instruments表控制採集到的監控資料進行消費儲存
PS可以監控哪些類別的事件資訊
語句事件監控：有助於分析資料庫SQL層面的資源消耗與瓶頸
等待事件監控：有助於分析鎖等待相關的問題排查
階段事件監控：有助於分析資料庫各事件資源消耗與瓶頸點
事務事件監控：有助於分析事務相關的資訊
檔案系統呼叫事件監控：有助於分析
記憶體使用事件監控：有助於分析資料庫記憶體的消耗
針對以上各類事件監控，資料庫對於這些監控資料的儲存主要包括：
當前事件記錄資訊表：
events_xxx_current
歷史事件記錄資訊表：events_xxx_history
長曆事件記錄資訊表：events_xxx_history_long
各維度進行分組聚合後的事件統計表:
events_xxx_summary_by_account_by_event_name events_xxx_summary_by_host_by_event_name events_xxx_summary_by_instance events_xxx_summary_by_thread_by_event_name events_xxx_summary_by_user_by_event_name events_xxx_summary_global_by_event_name
開啟鎖相關監控
檢查鎖監控是否啟用
MySQL [(none)]> 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.01 sec)
如未開啟，需要手動啟動
MySQL [(none)]> update performance_schema.setup_instruments set enabled = 'YES' where name like '%lock%'; Query OK, 173 rows affected (6.27 sec) Rows matched: 180 Changed: 173 Warnings: 0
開啟一個會話
MySQL [cjcdb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 11 | +-----------------+ 1 row in set (0.00 sec)
全域性鎖
MySQL [cjcdb]> flush table with read lock; Query OK, 0 rows affected (0.00 sec)
開啟第 二個會 話
mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec)
執行update語句，被阻塞
mysql> update t1 set age=200 where id=2;
檢視阻塞詳細資訊
開啟第三個會話
MySQL [(none)]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 10 | +-----------------+ 1 row in set (0.00 sec)
檢視當前會話資訊
MySQL [(none)]> show processlist; +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | 8 | root | localhost | cjcdb | Query | 27 | Waiting for global read lock | update t1 set age=200 where id=2 | | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | | 11 | root | localhost | cjcdb | Sleep | 43 | | NULL | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ 3 rows in set (0.00 sec)
可以看到,process id為8的執行緒State為Waiting for global read lock，表示正在等待全域性讀鎖。
如何查詢阻塞的源頭：
MySQL [cjcdb]> select * from information_schema.innodb_locks; Empty set, 1 warning (0.00 sec) MySQL [cjcdb]> select * from information_schema.innodb_lock_waits; Empty set, 1 warning (0.00 sec) MySQL [cjcdb]> select * from information_schema.innodb_trx\G; Empty set (0.00 sec) MySQL [cjcdb]> show engine innodb status\G; ...... ------------ TRANSACTIONS ------------ Trx id counter 104240 Purge done for trx's n:o < 104240 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422053759651440, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422053759649616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422053759650528, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- ......
透過上面的常規手段查詢，沒有任何有用的資訊。
在MySQL 5.7版本開始提供了performance_schema.metadata_locks表。
MySQL [(none)]> select * from performance_schema.metadata_locks where OWNER_THREAD_ID !=sys.ps_thread_id(connection_id())\G; *************************** 1. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140577980262000 LOCK_TYPE: SHARED ###共享鎖 LOCK_DURATION: EXPLICIT ###顯示 LOCK_STATUS: GRANTED ###已授權 SOURCE: OWNER_THREAD_ID: 36 ###持有鎖的內部執行緒ID為36 OWNER_EVENT_ID: 9 *************************** 2. row *************************** OBJECT_TYPE: COMMIT OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140577970655552 LOCK_TYPE: SHARED ###共享鎖 LOCK_DURATION: EXPLICIT ###顯示 LOCK_STATUS: GRANTED ###已授權 SOURCE: OWNER_THREAD_ID: 36 ###持有鎖的內部執行緒ID為36 OWNER_EVENT_ID: 9 *************************** 3. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140578306233392 LOCK_TYPE: INTENTION_EXCLUSIVE ###意向排他鎖 LOCK_DURATION: STATEMENT ###語句 LOCK_STATUS: PENDING ###狀態為PENDING,表示正在等待被授權 SOURCE: OWNER_THREAD_ID: 33 ###被阻塞的內部執行緒ID為33 OWNER_EVENT_ID: 12 3 rows in set (0.00 sec) ERROR: No query specified
透過查詢可知，執行緒ID為33的會話，被執行緒ID為36的阻塞了。
那麼如何透過執行緒ID找到對應的會話ID呢？
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 | | 33 | 8 | thread/sql/one_connection | FOREGROUND | | 35 | 10 | thread/sql/one_connection | FOREGROUND | | 36 | 11 | thread/sql/one_connection | FOREGROUND | +-----------+----------------+--------------------------------+------------+ 4 rows in set (0.00 sec)
或者透過process id查詢thread id。
MySQL [(none)]> select sys.ps_thread_id(8); +---------------------+ | sys.ps_thread_id(8) | +---------------------+ | 33 | +---------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select sys.ps_thread_id(11); +----------------------+ | sys.ps_thread_id(11) | +----------------------+ | 36 | +----------------------+ 1 row in set (0.00 sec)
可以看到，正式process id 11 阻塞了 process id 8，當前process id的Command為Sleep。
MySQL [(none)]> show processlist; +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ | 8 | root | localhost | cjcdb | Query | 934 | Waiting for global read lock | update t1 set age=200 where id=2 | | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | | 11 | root | localhost | cjcdb | Sleep | 950 | | NULL | +----+------+-----------+-------+---------+------+------------------------------+----------------------------------+ 3 rows in set (0.00 sec)
那麼如何知道process id 11執行了什麼操作？
透過performance_schema.events_statements_current可以找到process id 11正在執行和最後一次執行完成的語句資訊。
這個資訊並不一定可靠，因為該表只記錄每個執行緒正在執行和最近一次執行完成的語句資訊，一旦這個執行緒有新的語句執行，原來的語句會被覆蓋。
MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G; *************************** 1. row *************************** THREAD_ID: 36 EVENT_ID: 8 END_EVENT_ID: 8 EVENT_NAME: statement/sql/flush SOURCE: TIMER_START: 3250383126081000 TIMER_END: 3250383282239000 TIMER_WAIT: 156158000 LOCK_TIME: 0 SQL_TEXT: flush table with read lock DIGEST: 172f9471494a101656e6cb75da3e2726 DIGEST_TEXT: FLUSH TABLE WITH READ LOCK CURRENT_SCHEMA: cjcdb OBJECT_TYPE: NULL
在process id 11裡執行一次select version();後。
再次查詢performance_schema.events_statements_current，SQL資訊已經變了。
MySQL [(none)]> select * from performance_schema.events_statements_current where thread_id=36\G; *************************** 1. row *************************** THREAD_ID: 36 EVENT_ID: 9 END_EVENT_ID: 9 EVENT_NAME: statement/sql/select SOURCE: TIMER_START: 4631392503744000 TIMER_END: 4631392573894000 TIMER_WAIT: 70150000 LOCK_TIME: 0 SQL_TEXT: select version() DIGEST: 54cf1693fbf9ac7057b89a6958fe9519 DIGEST_TEXT: SELECT `version` ( ) CURRENT_SCHEMA: cjcdb OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL
釋放全域性讀鎖：
在產生阻塞的會話內釋放全域性讀鎖。
MySQL [cjcdb]> unlock tables; Query OK, 0 rows affected (0.00 sec)
###chenjuchao 20220416###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2887472/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 全域性鎖和表鎖MySql
- MySQL全域性鎖、表鎖以及行鎖MySql
- MySQL鎖等待與死鎖問題分析MySql
- MySQL學習之全域性鎖和表鎖MySql
- 全域性鎖、表鎖、行鎖
- MySQL 死鎖問題分析MySql
- 什麼是鎖？深入分析解讀MySQL鎖，解決幻讀問題！MySql
- 全域性鎖和表鎖
- Seata 全域性鎖等待超時 問題排查
- MySQL Online DDL導致全域性鎖表案例分析MySql
- MySQL死鎖問題MySql
- MySQL鎖（讀鎖、共享鎖、寫鎖、S鎖、排它鎖、獨佔鎖、X鎖、表鎖、意向鎖、自增鎖、MDL鎖、RL鎖、GL鎖、NKL鎖、插入意向鎖、間隙鎖、頁鎖、悲觀鎖、樂觀鎖、隱式鎖、顯示鎖、全域性鎖、死鎖）MySql
- MySQL5.5加主鍵鎖讀問題MySql
- python GIL 全域性鎖Python
- MySQL鎖（一）全域性鎖：如何做全庫的邏輯備份？MySql
- Sqlserver分析死鎖問題SQLServer
- 線上死鎖問題分析
- MySQL鎖分析MySql
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- mysql加鎖讀MySql
- 測試MySQL鎖的問題MySql
- 手把手教你分析解決MySQL死鎖問題MySql
- python之GIL全域性直譯器鎖，自定義互斥鎖，死鎖與遞迴鎖Python遞迴
- 什麼是Python全域性直譯器鎖(GIL)?全域性直譯器鎖的好處！Python
- Mysql(MyISAM)的讀寫互斥鎖問題的解決方法MySql
- MySQL MyISAM引擎的讀鎖與寫鎖MySql
- 【Mysql】記一次鎖問題MySql
- tempdb大量閂鎖等待問題分析
- Python GIL（全域性直譯器鎖）Python
- Python與全域性直譯器鎖Python
- RAC全域性死鎖檢測時間
- Gil全域性解釋鎖和執行緒互斥鎖的關係執行緒
- Mysql鎖機制分析MySql
- 【問答分享第一彈】MySQL鎖總結:MySQL行鎖、表鎖、排他鎖、共享鎖的特點MySql
- MySQL鎖表相關問題查詢思路MySql
- MySQL中鎖的相關問題DTQUMySql
- mysql insert into ... select的鎖問題MySql
- 一個MySQL死鎖問題的反思MySql