前言
本文介紹鎖表和執行慢的例子，以及發生鎖表時的排查方法，
鎖表
1. 等MDL鎖
比如像 select * from t where id=1; 長時間不回傳，可能是因為等 MDL 鎖而阻塞，
排查方法：
使用 show processlist; 查看執行緒狀態
由上圖可知，其他執行緒正在表上請求或者持有 MDL 寫鎖，所以阻塞了 select 獲取 MDL 讀鎖，
如果想模擬獲取 MDL 寫鎖，可以通過
lock table t write;實作，
解決辦法：
只需要找到阻塞 select 獲取 MDL 讀鎖的執行緒，kill 掉就可以了，因為 MDL 是表級鎖，所以查詢 sys 表的 schema_table_lock_waits 欄位，就可以得到阻塞的 process id，
2. 等待flush
MySQL里面對表做flush操作的用法，一般有以下兩個：
# 關閉表 t
flush tables t with read lock;
# 關閉所有打開的表
flush tables with read lock;
等待 flush 表示一個執行緒正要對表做 flush 操作，但是被其他執行緒阻塞，而 flush 操作又會阻塞后來的 select 查詢操作，
排查方法：
很簡單，通過 show processlist; 就可以發現，
3. 等行鎖
如上圖，session A 對 id=1 的行上了行鎖，且事務一直沒有提交，導致阻塞 session B 對該行執行當前讀，
排查方法：
同樣通過 show processlist; 方法排查，
其中，id=8 行就是被阻塞的查詢，執行緒被阻塞狀態為 statistics，
解決辦法：
同樣是找到阻塞的執行緒 id，然后 kill 掉，行鎖是 innodb 引擎提供的，通過查詢 sys 表的 innodb_lock_waits 行可以得到 process id，
執行慢
session A 中第二條查詢陳述句比第一條查詢快很多，因為第二條加鎖查詢是當前讀，直接獲取當前行資料；第一條查詢是快照讀，因為 session B 在之前對 id=1 的行更新了 100 萬次，所以需要根據 undo log 日志對當前行執行 100 萬次回滾操作，
總結
對于阻塞問題的排查，使用 show processlist;，對于阻塞問題的解決，通過在 sys 表中查詢阻塞執行緒的 process id，比如 schema_table_lock_waits、innodb_lock_waits，
提問
存在索引的欄位進行當前讀，我們知道會在索引樹上對符合的行上鎖，那對于 select * from t where c = 5 for update; （欄位 c 不存在索引）陳述句如何上鎖，什么時候釋放鎖？
回答：
（1）提交讀 rc 隔離級別下
因為欄位 c 不存在索引，所以進入存盤引擎后，會在主鍵索引樹上對全表加排他鎖，在此優化器做了一些優化，回傳 MySQL server 后，會判斷出不符合條件的行，即 c != 5 的行，釋放這些行上的排他鎖，最后，在事務提交的時候釋放鎖，
所以，在一個事務執行完這條 SQL 后，在另一個事務可以對 c != 5 的行進行更新和洗掉操作，并且可以在任何位置執行插入操作（因為 rc 模式下，沒有使用間隙鎖），
（2）可重復讀 rr 隔離級別下
因為欄位 c 不存在索引，所以進入存盤引擎后，會在主鍵索引樹上對全表加排他和間隙鎖，最后，在事務提交的時候釋放鎖，
所以，在一個事務執行完這條 SQL 后，在另一個事務對全表任何一行的更新操作都需要等待，在全表任何位置的插入操作也需要等待，
參考
- [1] 為什么我只查一行的陳述句，也執行這么慢
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/301652.html
標籤：MySQL
下一篇：MySQL45講之查詢慢或者阻塞