06 | 全局鎖和表鎖：給表加個欄位怎麼有這麼多阻礙？ Connection連接與Session會話通俗來講，會話(Session)是通信雙⽅從開始通信到通信結束期間的⼀個上下文（Context）。這個上下文是⼀段位於伺服器端的記憶體：記錄了本次連接的客戶端機器、通過哪個應用程式、哪個用戶登錄等信 ...

06 | 全局鎖和表鎖：給表加個欄位怎麼有這麼多阻礙？

Connection連接與Session會話

通俗來講，會話(Session)是通信雙⽅從開始通信到通信結束期間的⼀個上下文（Context）。這個上下文是⼀段位於伺服器端的記憶體：記錄了本次連接的客戶端機器、通過哪個應用程式、哪個用戶登錄等信息。

連接是物理上的客戶端同伺服器的通信鏈路

根據加鎖的範圍，MySQL 裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。

全局鎖

全局鎖就是對整個資料庫實例加鎖

Flush tables with read lock (FTWRL)

當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他線程的以下語句會被阻塞：數據更新語句（數據的增刪改）（DML) 、數據定義語句（包括建表、修改表結構等）（DDL）和更新類事務的提交語句。

全局鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都 select 出來存成文本。

Q：在可重覆讀隔離級別下開啟一個事務是一致性視圖，這時由於MVCC數據是可以正常更新的，所以為什麼不用這種方式？

A：官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數–single-transaction 的時候，導數據之前就會啟動一個事務，來確保拿到一致性視圖。但存在一個問題：single-transaction 方法只適用於所有的表使用事務引擎的庫。而MyISAM不支持事務

Q：既然要全庫只讀，為什麼不使用 set global readonly=true 的方式呢？

A：

一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變數的方式影響面更大，我不建議你使用。
二是，在異常處理機制上有差異。如果執行 FTWRL 命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。而將整個庫設置為 readonly 之後，如果客戶端發生異常，則資料庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。

表級鎖

表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)。

lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要註意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

另一類表級的鎖是 MDL（metadata lock)。

MDL作用是防止DDL和DML併發的衝突，

MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。

我們可以看到 session A 先啟動，這時候會對錶 t 加一個 MDL 讀鎖。由於 session B 需要的也是 MDL 讀鎖，因此可以正常執行。之後 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。並且之後所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞

MDL 會直到事務提交才釋放，在做表結構變更的時候，一定要小心不要導致鎖住線上查詢和更新。

Q：如何安全地給小表加欄位？

A：首先我們要解決長事務，事務不提交，就會一直占著 MDL 鎖。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。

Q：如果你要變更的表是一個熱點表，雖然數據量不大，但是上面的請求很頻繁，而你不得不加個欄位，你該怎麼做呢？

A：這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後開發人員或者 DBA 再通過重試命令重覆這個過程。

Q：備份一般都會在備庫上執行，你在用–single-transaction 方法做邏輯備份的過程中，如果主庫上的一個小表做了一個 DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？

A：假設這個 DDL 是針對錶 t1 的，備份過程中幾個關鍵的語句：

/* 在備份開始的時候，為了確保 RR（可重覆讀）隔離級別，再設置一次 RR 隔離級別 (Q1)*/
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* 啟動事務，這裡用 WITH CONSISTENT SNAPSHOT 確保這個語句執行完就可以得到一個一致性視圖（Q2) */
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；

/* 設置一個保存點，這個很重要（Q3）； */
Q3:SAVEPOINT sp;
/* show create 是為了拿到表結構 (Q4)    時刻 1 */
Q4:show create table `t1`;
/* 正式導數據 （Q5）   時刻 2 */
Q5:SELECT * FROM `t1`;
/* 回滾到 SAVEPOINT sp，在這裡的作用是釋放 t1 的 MDL 鎖  時刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 時刻 4 */
/* other tables */

DDL 從主庫傳過來的時間按照效果不同，分為四個時刻。

題目設定為小表，我們假定到達後，如果開始執行，則很快能夠執行完成。

如果在 Q4 語句執行之前到達，現象：沒有影響，備份拿到的是 DDL 後的表結構。
如果在“時刻 2”到達，則表結構被改過，Q5 執行的時候，報 Table definition has changed, please retry transaction，現象：mysqldump 終止；
如果在“時刻 2”和“時刻 3”之間到達，mysqldump 占著 t1 的 MDL 讀鎖，binlog 被阻塞，現象：主從延遲，直到 Q6 執行完成。
從“時刻 4”開始，mysqldump 釋放了 MDL 讀鎖，現象：沒有影響，備份拿到的是 DDL 前的表結構。

07 | 行鎖功過：怎麼減少行鎖對性能的影響？

MySQL 的行鎖

MySQL 的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支持行鎖，比如 MyISAM 引擎就不支持行鎖。

行鎖就是針對數據表中行記錄的鎖。這很好理解，比如事務 A 更新了一行，而這時候事務 B 也要更新同一行，則必須等事務 A 的操作完成後才能進行更新。

兩階段鎖

事務 A 持有的兩個記錄的行鎖，都是在 commit 的時候才釋放的。事務 B 的 update 語句會被阻塞，直到事務 A 執行 commit 之後，事務 B 才能繼續執行。

在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

如果你的事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響併發度的鎖儘量往後放。

死鎖和死鎖檢測

當併發系統中不同線程出現迴圈資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態，稱為死鎖。

這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。

當出現死鎖以後，有兩種策略：

一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數 innodb_lock_wait_timeout 來設置。
另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數 innodb_deadlock_detect 設置為 on，表示開啟這個邏輯。每當一個事務被鎖的時候，就要看看它所依賴的線程有沒有被別人鎖住 n2複雜度），如此迴圈，最後判斷是否出現了迴圈等待，也就是死鎖。(死鎖的四個必要條件 互斥,請求與保持,不可剝奪，迴圈等待)

死鎖檢測要耗費大量的 CPU 資源

Q:怎麼解決由這種熱點行更新導致的性能問題呢？

臨時把死鎖檢測關掉。可能會出現大量的超時，這是業務有損的。
控制併發度，在中間件實現，對於相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 內部就不會有大量的死鎖檢測工作了。
從設計上優化，拆分一行改為邏輯上的多行，隨機選一個加上，再求和

Q：如果你要刪除一個表裡面的前 10000 行數據，有以下三種方法可以做到：

第一種，直接執行 delete from T limit 10000;
第二種，在一個連接中迴圈執行 20 次 delete from T limit 500;
第三種，在 20 個連接中同時執行 delete from T limit 500。

選擇哪一種方法呢？為什麼呢？

A：

第二種方式是相對較好的。

第一種方式（即：直接執行 delete from T limit 10000）裡面，單個語句占用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。

第三種方式（即：在 20 個連接中同時執行 delete from T limit 500），會人為造成鎖衝突。

08 | 事務到底是隔離的還是不隔離的？

如果是可重覆讀隔離級別，事務 T 啟動的時候會創建一個視圖 read-view，之後事務 T 執行期間，即使有其他事務修改了數據，事務 T 看到的仍然跟在啟動時看到的一樣。也就是說，一個在可重覆讀隔離級別下執行的事務，好像與世無爭，不受外界影響。

一個事務要更新一行，如果剛好有另外一個事務擁有這一行的行鎖，它又不能這麼超然了，會被鎖住，進入等待狀態。

Q：既然進入了等待狀態，那麼等到這個事務自己獲取到行鎖要更新數據的時候，它讀到的值是什麼呢？

舉一個例子，下麵是一個只有兩行的表的初始化語句。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事務開始時間點，分為兩種情況：

1）start transaction 時，是第一條語句的執行時間點，就是事務開始的時間點，第一條select語句建立一致性讀的snapshot；

2）start transaction with consistent snapshot 時，則是立即建立本事務的一致性讀snapshot，當然也開始事務了；

在這個例子中，事務 C 沒有顯式地使用 begin/commit，表示這個 update 語句本身就是一個事務，語句完成的時候會自動提交。

事務 B 在更新了行之後查詢 ;

事務 A 在一個只讀事務中查詢，並且時間順序上是在事務 B 的查詢之後。

結果：事務 B 查到的 k 的值是 3，而事務 A 查到的 k 的值是 1

A：

不妨做如下假設：

事務 A 開始前，系統裡面只有一個活躍事務 ID 是 99；
事務 A、B、C 的版本號分別是 100、101、102，且當前系統里只有這四個事務；
三個事務開始前，(1,1）這一行數據的 row trx_id 是 90。

從圖中可以看到，第一個有效更新是事務 C，把數據從 (1,1) 改成了 (1,2)。這時候，這個數據的最新版本的 row trx_id 是 102，而 90 這個版本已經成為了歷史版本。

第二個有效更新是事務 B，把數據從 (1,2) 改成了 (1,3)。這時候，這個數據的最新版本（即 row trx_id）是 101，而 102 又成為了歷史版本。

在事務 A 查詢的時候，其實事務 B 還沒有提交，但是它生成的 (1,3) 這個版本已經變成當前版本了。但這個版本對事務 A 必須是不可見的，否則就變成臟讀了。

事務 A 要來讀數據了，它的視圖數組是 [99,100]。當然了，讀數據都是從當前版本讀起的。所以，事務 A 查詢語句的讀數據流程是這樣的：

找到 (1,3) 的時候，判斷出 row trx_id=101，比高水位大，處於紅色區域，不可見；
接著，找到上一個歷史版本，一看 row trx_id=102，比高水位大，處於紅色區域，不可見；
再往前找，終於找到了（1,1)，它的 row trx_id=90，比低水位小，處於綠色區域，可見。

總結：

一個數據版本，對於一個事務視圖來說，除了自己的更新總是可見以外，有三種情況：

版本未提交，不可見；
版本已提交，但是是在視圖創建後提交的，不可見；
版本已提交，而且是在視圖創建前提交的，可見。

兩個“視圖”的概念

在 MySQL 里，有兩個“視圖”的概念：

一個是 view。它是一個用查詢語句定義的虛擬表，在調用的時候執行查詢語句並生成結果。創建視圖的語法是 create view … ，而它的查詢方法與表一樣。
另一個是 InnoDB 在實現 MVCC 時用到的一致性讀視圖，即 consistent read view，用於支持 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重覆讀）隔離級別的實現。

它沒有物理結構，作用是事務執行期間用來定義“我能看到什麼數據”。

“快照”在 MVCC 里是怎麼工作的？

在可重覆讀隔離級別下，事務在啟動的時候就“拍了個快照”。註意，這個快照是基於整庫的。

InnoDB 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向 InnoDB 的事務系統申請的，是按申請順序嚴格遞增的。

而每行數據也都是有多個版本的。每次事務更新數據的時候，都會生成一個新的數據版本，並且把 transaction id 賦值給這個數據版本的事務 ID，記為 row trx_id。同時，舊的數據版本要保留，並且在新的數據版本中，能夠有信息可以直接拿到它。

也就是說，數據表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id。

圖中虛線框里是同一行數據的 4 個版本，當前最新版本是 V4，k 的值是 22，它是被 transaction id 為 25 的事務更新的，因此它的 row trx_id 也是 25。

實際上，圖 2 中的三個虛線箭頭，就是 undo log；而 V1、V2、V3 並不是物理上真實存在的，而是每次需要的時候根據當前版本和 undo log 計算出來的。比如，需要 V2 的時候，就是通過 V4 依次執行 U3、U2 算出來。

按照可重覆讀的定義，一個事務啟動的時候，能夠看到所有已經提交的事務結果。但是之後，這個事務執行期間，其他事務的更新對它不可見。因此，一個事務只需要在啟動的時候聲明說，“以我啟動的時刻為準，如果一個數據版本是在我啟動之前生成的，就認；如果是我啟動以後才生成的，我就不認，我必須要找到它的上一個版本”。當然，如果“上一個版本”也不可見，那就得繼續往前找。還有，如果是這個事務自己更新的數據，它自己還是要認的。

在實現上， InnoDB 為每個事務構造了一個數組，用來保存這個事務啟動瞬間，當前正在“活躍”的所有事務 ID。“活躍”指的就是，啟動了但還沒提交。

數組裡面事務 ID 的最小值記為低水位，當前系統裡面已經創建過的事務 ID 的最大值加 1 記為高水位。

這個視圖數組和高水位，就組成了當前事務的一致性視圖（read-view）

數據版本的可見性規則，就是基於數據的 row trx_id 和這個一致性視圖的對比結果得到的。

這個視圖數組把所有的 row trx_id 分成了幾種不同的情況。

如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個數據是可見的；
如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的；
如果落在黃色部分，那就包括兩種情況
a. 若 row trx_id 在數組中，表示這個版本是由還沒提交的事務生成的，不可見；
b. 若 row trx_id 不在數組中，表示這個版本是已經提交了的事務生成的，可見。

有了這個聲明後，系統裡面隨後發生的更新，就跟這個事務看到的內容無關了。因為之後的更新，生成的版本一定屬於上面的 2 或者 3(a) 的情況，而對它來說，這些新的數據版本是不存在的，所以這個事務的快照，就是“靜態”的了。

如果有一個事務，它的低水位是 18，那麼當它訪問這一行數據時，就會從 V4 通過 U3 計算出 V3，所以在它看來，這一行的值是 11。

InnoDB 利用了“所有數據都有多個版本”的這個特性，實現了“秒級創建快照”的能力。

更新邏輯

Q:事務 B 的 update 語句，如果按照一致性讀，好像結果不對哦？

事務 B 的視圖數組是先生成的，之後事務 C 才提交，不是應該看不見 (1,2) 嗎，怎麼能算出 (1,3) 來？

A：如果事務 B 在更新之前查詢一次數據，這個查詢返回的 k 的值確實是 1。

但是，當它要去更新數據的時候，就不能再在歷史版本上更新了，否則事務 C 的更新就丟失了。因此，事務 B 此時的 set k=k+1 是在（1,2）的基礎上進行的操作。

更新數據都是先讀後寫的，而這個讀，只能讀當前的值，稱為“當前讀”（current read）。

因此，在更新的時候，當前讀拿到的數據是 (1,2)，更新後生成了新版本的數據 (1,3)，這個新版本的 row trx_id 是 101。所以，在執行事務 B 查詢語句的時候，一看自己的版本號是 101，最新數據的版本號也是 101，是自己的更新，可以直接使用，所以查詢得到的 k 的值是 3。

除了 update 語句外，select 語句如果加鎖，也是當前讀。

所以，如果把事務 A 的查詢語句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以讀到版本號是 101 的數據，返回的 k 的值是 3。

下麵這兩個 select 語句，就是分別加了讀鎖（S 鎖，共用鎖）和寫鎖（X 鎖，排他鎖）。

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

Q：假設事務 C 不是馬上提交的，而是變成了下麵的事務 C’，會怎麼樣呢？

事務 C’的不同是，更新後並沒有馬上提交，在它提交前，事務 B 的更新語句先發起了。前面說過了，雖然事務 C’還沒提交，但是 (1,2) 這個版本也已經生成了，並且是當前的最新版本。那麼，事務 B 的更新語句會怎麼處理呢？

A：考慮兩階段鎖協議，事務 C’沒提交，也就是說 (1,2) 這個版本上的寫鎖還沒釋放。而事務 B 是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務 C’釋放這個鎖，才能繼續它的當前讀。

事務的可重覆讀的能力是怎麼實現的？

可重覆讀的核心就是一致性讀（consistent read）；而事務更新數據的時候，只能用當前讀。如果當前的記錄的行鎖被其他事務占用的話，就需要進入鎖等待。

讀提交的邏輯和可重覆讀

而讀提交的邏輯和可重覆讀的邏輯類似，它們最主要的區別是：

在可重覆讀隔離級別下，只需要在事務開始的時候創建一致性視圖，之後事務里的其他查詢都共用這個一致性視圖；
在讀提交隔離級別下，每一個語句執行前都會重新算出一個新的視圖。“start transaction with consistent snapshot; ”的意思是從這個語句開始，創建一個持續整個事務的一致性快照。所以，在讀提交隔離級別下，這個用法就沒意義了，等效於普通的 start transaction。

導致：

對於可重覆讀，查詢只承認在事務啟動前就已經提交完成的數據；
對於讀提交，查詢只承認在語句啟動前就已經提交完成的數據；

Q：在讀提交隔離級別下，事務 A 和事務 B 的查詢語句查到的 k，分別應該是多少呢？

A：

下麵是讀提交時的狀態圖，可以看到這兩個查詢語句的創建視圖數組的時機發生了變化，就是圖中的 read view 框。（註意：這裡，我們用的還是事務 C 的邏輯直接提交，而不是事務 C’）

這時，事務 A 的查詢語句的視圖數組是在執行這個語句的時候創建的，時序上 (1,2)、(1,3) 的生成時間都在創建這個視圖數組的時刻之前。但是，在這個時刻：

(1,3) 還沒提交，屬於情況 1，不可見；
(1,2) 提交了，屬於情況 3，可見。

所以，這時候事務 A 查詢語句返回的是 k=2，事務 B 查詢結果 k=3，能看到已提交的。。

Q：用下麵的表結構和初始化語句作為試驗環境，事務隔離級別是可重覆讀。現在，我要把所有“欄位 c 和 id 值相等的行”的 c 值清零，但是卻發現了一個“詭異”的、改不掉的情況。請你構造出這種情況，並說明其原理。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

A：如何構造一個“數據無法修改”的場景。

MySQL實戰45講 6,7,8

06 | 全局鎖和表鎖 ：給表加個欄位怎麼有這麼多阻礙？

全局鎖

表級鎖