筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 7） --行鎖功能：怎麼減少行鎖對性能的影響？ MySQL的行鎖是在引擎層由各個引擎自己實現的。因此，並不是所有的引擎都支持行鎖，如MyISAM引擎就不支持行鎖。對於不支持行鎖的引擎，只能使用表鎖來進行併發控制。對於這種引擎的表，同一張表上任何 ...
筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》
7） --行鎖功能：怎麼減少行鎖對性能的影響？
MySQL的行鎖是在引擎層由各個引擎自己實現的。因此，並不是所有的引擎都支持行鎖，如MyISAM引擎就不支持行鎖。對於不支持行鎖的引擎，只能使用表鎖來進行併發控制。對於這種引擎的表，同一張表上任何時刻只能有一個更新在執行，這就會影響到業務併發度。InnoDB是支持行鎖的，這也是MyISAM被它替代的一個重要原因。
顧名思義，行鎖就是針對數據表中行記錄的鎖。比如事務A要更新某一行，同時事務B也要更新這一行，則B必須等待A的操作完成後才能進行更新。
兩階段鎖協議：
在InnoDB事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。因此，如果你的事務中需要鎖多個行，要把最可能造成鎖衝突，最可能影響併發度的鎖儘量往後放。
舉一個具體的例子：
客戶A要在電影院B購買電影票，這可能涉及三個操作：
- 在A的賬戶餘額中扣除電影票的票價
- 在影院B的餘額中增加這張電影票的票價
- 記錄一條交易記錄。
當然為了保證交易的原子性，我們要把這三個操作放在同一個事務中。那麼你應該怎樣安排上面的語句呢？（兩條Update，一條Insert）。根據兩階段鎖協議，不管你怎樣安排順序，所有的鎖都是在需要時加上，事務提交時釋放。因此，如果你把更新影院B餘額的操作放在最後，那麼影院餘額這一行的鎖的時間就最少，最大程度地減少了事務直接的鎖等待，提升了併發度。
死鎖和死鎖檢測：
當併發系統中不同線程出現迴圈資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致幾個線程都在進入無限等待的狀態，稱為死鎖。當出現死鎖後我們有兩種策略：
- 直接進入等待，直到超時，這個超時時間可以通過參數innodb_lock_wait_timeout來設置。
- 另一種策略是，發起死鎖監測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數innodb_deadlock_detect設置為on,表示開啟這個邏輯。
第一種策略的預設值是50s，顯然在生產環境這個值是無法接受的。但如果你把這個值設置的很小，如1s，當出現死鎖時確實可以很快解開，但也會出現很多誤傷。因此我們常採用第二種策略：主動死鎖檢測。並且，innodb_deadlock_detect的預設值本身就是on。主動死鎖檢測在發生死鎖的時候能夠快速發現併進行處理，但是它也有額外負擔。例如有1000個線程併發更新同一行，那麼死鎖檢測會這樣工作，這1000個線程每個的事務被鎖的時候都要去看一下它所依賴的線程有沒有被別人鎖住，如此迴圈來判斷是否出現了迴圈等待。那麼此時死鎖檢測的就會消耗大量CPU資源，你就會看到CPU利用率很高，但是每秒執行不了幾個事務。
那麼怎麼解決這種熱點行更新導致的性能問題呢？問題的根源在於，死鎖檢測要耗費大量的CPU資源。一種治標的方式是，如果你能確保事務一定不會出現死鎖，可以臨時把死鎖檢測關掉。當然這個操作伴隨著一定的風險，另一個思路是控制併發度。根據上面的分析，你會發現如果併發能夠控制住，比如同一行在同一時間最多只有10個線程在更新，那麼死鎖檢測的成本就是可以接受的。當然你也可以從設計上來優化這裡的邏輯。
上篇問題：
備份一般都會在備庫上執行，你在用-single-transaction方法做邏輯備份的過程中，如果主庫上的一個小表做了一個DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？
（DML: Data manipulation language,數據操縱語言，增刪改查等；DDL:Data definition language,資料庫定義語言，修改表結構等;DCL:Data control language資料庫控制語言，修改用戶許可權等）
假設這個DDL是針對錶t1的，這裡把備份過程中幾個關鍵的語句列出來：
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT； /* other tables */ Q3:SAVEPOINT sp; /* 時刻 1 */ Q4:show create table `t1`; /* 時刻 2 */ Q5:SELECT * FROM `t1`; /* 時刻 3 */ Q6:ROLLBACK TO SAVEPOINT sp; /* 時刻 4 */ /* other tables */
在備份開始的時候，為了確保可重覆讀隔離級別，再設置一次隔離級別(Q1時刻)。啟動事務，使用with consistent snapshot確保這個語句執行完成後就可以得到一個一致性視圖。（Q2時刻）設置一個保存點.（Q3時刻）。拿到t1的表結構(Q4時刻)。正式導數據(Q5時刻)。回滾到SAVEPOINT sp,在這裡的作用是釋放t1的MDL鎖。
我們題目設定中t1是小表，假定DDL到達後如果開始執行則很快就能執行完成。
參考答案如下：
- 如果在Q4語句執行之前主庫的DDL到達，現象：沒有影響，備份拿到的是DDL後的表結構。
- 如果在‘時刻2’到達，則表結構被改過，Q5執行的時候，報Table defination has changed，please retry transaction，現象mysqldump終止。
- 如果在“時刻2”和“時刻3”之間到達，mysqldump占著t1的MDL讀鎖，binlog被阻塞，現象：主從延遲，直到Q6執行完成。
- 從“時刻4”開始，mysqldump釋放了MDL讀鎖，現象：沒有影響，備份拿到的是DDL之前的表結構。
問題：
如果你要刪除一個表裡面的10000行數據，有以下三種方式：
- 直接執行 delete from T limit 10000;
- 在一個連接中迴圈執行20次delete from T limit 500;
- 在20個連接中同時執行delete from T limilt 500;
你會選擇哪種方式，為什麼呢？
