8 InnoDB 事務(wù)模式與鎖定

在 InnoDB 事務(wù)處理模式中， the goal has been to combine the best properties of a multiversioning database to traditional two-phase locking. InnoDB 進(jìn)行行級的鎖定，并以與 Oracle 非鎖定讀取(non-locking)類似的方式讀取數(shù)據(jù)。 InnoDB 中的鎖定表的存儲是如此(space-efficiently)而不再需要擴(kuò)大鎖定： 典型特色是一些用戶可能鎖定數(shù)據(jù)庫中的任意行或任意行的子集，而不會引起 InnoDB 內(nèi)存運(yùn)行溢出。

在 InnoDB 中，所有的用戶操作均是以事務(wù)方式處理的。如果 MySQL 使用了自動提交(autocommit)方式，每個 SQL 語句將以一個單獨(dú)的事務(wù)來處理。MySQL 通常是以自動提交方式建立一個服務(wù)連接的。

如果使用 SET AUTOCOMMIT = 0 關(guān)閉自動提交模式，就認(rèn)為用戶總是以事務(wù)方式操作。如果發(fā)出一個 COMMIT 或 ROLLBACK 的 SQL 語句，它將停止當(dāng)前的事務(wù)而重新開始新事務(wù)。兩個語句將會釋放所有在當(dāng)前事務(wù)中設(shè)置的 InnoDB 鎖定。COMMIT 意味著永久改變在當(dāng)前事務(wù)中的更改并為其它用戶可見。ROLLBACK 正好相反，它是取消當(dāng)前事務(wù)的所有更改。

如果以 AUTOCOMMIT = 1 建立一個連接，那么用戶仍然可以通過以 BEGIN 開始和 COMMIT 或 ROLLBACK 為語句結(jié)束的方式來執(zhí)行一個多語句的事務(wù)處理。

在 SQL-1992 事務(wù)隔離級(transaction isolation levels)規(guī)定的條款中，InnoDB 默認(rèn)為 REPEATABLE READ。從 4.0.5 開始， InnoDB 提供了 SQL-1992 標(biāo)準(zhǔn)中所有的 4 個不同的事務(wù)隔離級。你可以 my.cnf 的 [mysqld] 區(qū)中設(shè)置所有連接的默認(rèn)事務(wù)隔離級：

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}

用戶也可以通過下面的 SQL 語句為單個連接或所有新建的連接改變隔離級：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}

可以通過下列語句查詢?nèi)趾彤?dāng)前會話的事務(wù)隔離級：

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

在 InnoDB 的行鎖中使用所謂的 next-key locking。這就意味著，除了索引記錄外，InnoDB 還可以鎖定該索引記錄前部“間隙” (‘gap‘) 以阻塞其它用戶在索引記錄前部的直接插入。next-key lock 意思是鎖定一個索引記錄以及該記錄之前的間隙(gap)。gap lock 就是只鎖定某些索引記錄之前的間隙。

InnoDB 中的隔離級詳細(xì)描述：

• READ UNCOMMITTED 這通常稱為 ‘dirty read‘：non-locking SELECTs 的執(zhí)行使我們不會看到一個記錄的可能更早的版本；因而在這個隔離度下是非 ‘consistent‘ reads；另外，這級隔離的運(yùn)作如同 READ COMMITTED。
• READ COMMITTED 有些類似 Oracle 的隔離級。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 語句只鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。以一個唯一地搜索條件使用一個唯一索引(unique index)的 UPDATE 和 DELETE，僅僅只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。但是在范圍型的 UPDATE and DELETE 中，InnoDB 必須設(shè)置 next-key 或 gap locks 來阻塞其它用戶對范圍內(nèi)的空隙插入。 自從為了 MySQL 進(jìn)行復(fù)制(replication)與恢復(fù)(recovery)工作‘phantom rows‘必須被阻塞以來，這就是必須的了。Consistent reads 運(yùn)作方式與 Oracle 有點類似： 每一個 consistent read，甚至是同一個事務(wù)中的，均設(shè)置并作用它自己的最新快照。
• REPEATABLE READ 這是 InnoDB 默認(rèn)的事務(wù)隔離級。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。 否則這些操作將使用 next-key 鎖定，以 next-key 和 gap locks 鎖定找到的索引范圍，并阻塞其它用戶的新建插入。在 consistent reads 中，與前一個隔離級相比這是一個重要的差別： 在這一級中，同一事務(wù)中所有的 consistent reads 均讀取第一次讀取時已確定的快照。這個約定就意味著如果在同一事務(wù)中發(fā)出幾個無格式(plain)的 SELECTs ，這些 SELECTs 的相互關(guān)系是一致的。
• SERIALIZABLE 這一級與上一級相似，只是無格式(plain)的 SELECTs 被隱含地轉(zhuǎn)換為 SELECT ... LOCK IN SHARE MODE。

8.1 Consistent read

Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供給查詢一個數(shù)據(jù)庫在一個時間點的快照。 查詢將會檢查那些在這個時間點之前提交的事務(wù)所做的改動，以及在時間點之后改變或未提交的事務(wù)? 與這個規(guī)則相例外的是查詢將檢查查詢自身發(fā)出的事務(wù)所做的改變。

如果以默認(rèn)的 REPEATABLE READ 隔離級，那么所有在同一事務(wù)中的 consistent reads 只讀取同一個在事務(wù)中第一次讀所確定的快照。 你可以通過提交當(dāng)前事務(wù)并發(fā)出一個新的查詢以獲得新的數(shù)據(jù)快照。

Consistent read 在 InnoDB 處理 SELECT 中的默認(rèn)模式是 READ COMMITTED 和 REPEATABLE READ 隔離級。Consistent read 對其所訪問的表不加任何鎖定，因而其它任何用戶均可以修改在 consistent read 被完成之前自由的修改這些表。

8.2 Locking reads

Consistent read 在某些情況下是不太方便的。 假設(shè)你希望在表 CHILD 中插入 一個新行，而這個子表已有一個父表 PARENT。

假設(shè)你使用 consistent read 了讀取表 PARENT 并查看子表中對應(yīng)記錄。你真的能安全地在表 CHILD 中加入一個子行？不可能，因為在此期間可能有其它用戶刪除了表 PARENT 中的父行，而你并不知道它。

解決的辦法就是在鎖定的方式 LOCK IN SHARE MODE 下運(yùn)行一個 SELECT 。

SELECT * FROM PARENT WHERE NAME = ‘Jones‘ LOCK IN SHARE MODE;

在共享模式下執(zhí)行讀取的意思就是讀取最新的現(xiàn)有資料，并在所讀取的行上設(shè)置一個共享模式的鎖定。如果最新的數(shù)據(jù)屬于其它用戶仍未提交的事務(wù)，那將不得不等到這個事務(wù)被 提交 。共享模式的可以防止其它用戶更新或刪除我們當(dāng)前所讀取的行。當(dāng)查詢獲得 ‘Jones‘后，就可以安全地向子表 CHILD 中加入子行，然后提交事務(wù)。 這個例子顯示如何在應(yīng)用程序代碼中實現(xiàn)參照完整性。

另外一個例子： 在表 CHILD_CODES 有一個整型計數(shù)字段用于給在表 CHILD 中加入的每個子行賦于一個唯一的標(biāo)識符。 顯而易見地，用一個 consistent read 來讀取父表中的值并不是一個好的主意，因兩個用戶有可能會讀取出同一個計數(shù)值，當(dāng)以同一個標(biāo)識符插入兩個字行時將會產(chǎn)生一個重復(fù)鍵值(duplicate key)的錯誤。如果兩個用戶同時讀取了計數(shù)器，當(dāng)嘗試更新計數(shù)器時，他們中的一個必將在死鎖中結(jié)束，所以在讀取時使用 LOCK IN SHARE MODE 也并不是一個好的解決辦法。

在這和情況下有兩種方法來實現(xiàn)讀取并增加計數(shù)器：(1) 首先更新計數(shù)器然后再讀取它；(2) 首先以一個 FOR UPDATE 方式鎖定后再讀取，然后再增加它：

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

SELECT ... FOR UPDATE 將讀取最新的現(xiàn)有數(shù)據(jù)，并在所讀取的行上設(shè)置排它的鎖定。同樣在 SQL UPDATE 所訪問的行上也設(shè)置此鎖定。

8.3 Next-key locking: avoiding the ‘phantom problem‘

在 InnoDB 的行級鎖定上使用一個稱作 next-key locking 算法。在 InnoDB 在搜索或掃描表的索引時將進(jìn)行行鎖，它將在所訪問到的索引上設(shè)置共享或排它的鎖定。因而行鎖是更加精確地而又稱為索引記錄鎖定。

InnoDB 在索引記錄上設(shè)置的鎖同樣會影響索引記錄之前的“間隙(gap)”。如果一個用戶對索引記錄 R 加了一個共享或排它的鎖定，那其它用戶將不能在 R 之前立即插入新的記錄。這種間隙鎖定用于防止所謂的“phantom problem”。假設(shè)需讀取和鎖定表 CHILD 中標(biāo)識符大于 100 的子行，并更新所搜索到的記錄中某些字段。

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

假設(shè)表 CHILD 中有一個索引字段 ID。我們的查詢將從 ID 大于100的第一條記錄開始掃描索引記錄。 現(xiàn)在，假設(shè)加在索引記錄上的鎖定不能阻止在間隙處的插入，一個新的子記錄將可能在事務(wù)處理中被插入到表中。 如果現(xiàn)在在事務(wù)中再次執(zhí)行

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

在查詢返回的記錄集中將會有一個新的子記錄。這與事務(wù)的隔離規(guī)則相違背的：一個事務(wù)必須能夠順串(run)， 因而在事務(wù)處理中所讀取的數(shù)據(jù)將不會發(fā)生改變。而新的 ‘phantom‘ 子記錄將會打破這個隔離規(guī)則。

當(dāng) InnoDB 掃描索引時，它同樣會鎖定在索引中在結(jié)尾記錄(the last record)之后的間隙。這僅僅在上例中會發(fā)生： InnoDB 設(shè)置的鎖定將阻止任何 ID 大于 100 的插入。

在應(yīng)用程序中可以通過一個 next-key locking 來實現(xiàn)一個唯一性(uniqueness)檢查：如果以一個共享模式讀取數(shù)據(jù)并沒有發(fā)現(xiàn)與將要插入的數(shù)據(jù)存在重復(fù)值， 那么在讀取過程中 next-key lock 將被設(shè)置在你的記錄的后繼者(successor)上，這將阻止其它用戶在期間插入相同的記錄，因而你可以安全地插入你的記錄。 所以， next-key locking 可以允許你 ‘lock‘ 你的表中并不存在的記錄。

8.4 InnoDB 中各 SQL 語句的鎖定設(shè)置

• SELECT ... FROM ... : 這是一個 consistent read，不以鎖定方式讀取數(shù)據(jù)庫的快照，除非事務(wù)的隔離級被設(shè)置為 SERIALIZABLE，在這種情況下將在它所讀取的記錄索引上設(shè)置共享的 next-key locks。
• SELECT ... FROM ... LOCK IN SHARE MODE : 在所讀取的所有記錄索引上設(shè)置同享的鎖定。
• SELECT ... FROM ... FOR UPDATE : 在所讀取的所胡記錄索引上設(shè)置獨(dú)占地(exclusive)鎖定。
• INSERT INTO ... VALUES (...) : 在插入的記錄行上設(shè)置一個獨(dú)占地鎖定；注意這個鎖定并不是一個 next-key lock ，并不會阻止其它用戶在所插入行之前的間隙(gap)中插入新記錄。如果產(chǎn)生一個重復(fù)鍵值錯誤， 在重復(fù)索引記錄上設(shè)置一個共享的鎖定。
• 如果在一個表中定義了一個 AUTO_INCREMENT 列，InnoDB 在初始化自增計數(shù)器時將在與自增列最后一個記錄相對應(yīng)的索引上設(shè)置一個獨(dú)占的鎖定。在訪問自增計數(shù)器時， InnoDB 將設(shè)置一個特殊的表鎖定模式 AUTO-INC ，這個鎖定只持續(xù)到該 SQL 語句的結(jié)束而不是整個事務(wù)的結(jié)束。
• INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每個記錄上設(shè)置一個獨(dú)占的(無 next-key)鎖定。以一個 consistent read 搜索表 S ，但是如果 MySQL 打開了日志開關(guān)將在表 S 上設(shè)置一個共享的鎖定。 在從備份中進(jìn)行前滾(roll-forward)修復(fù)時，每個 SQL 語句必須嚴(yán)格按照原先所執(zhí)行的順序運(yùn)行，所以 InnoDB 不得不設(shè)置鎖定。
• CREATE TABLE ... SELECT ... 與上項相似，以 consistent read 或鎖定方式完成 SELECT 。
• REPLACE 如果沒有一個 unique key 沖突，它的執(zhí)行與 insert 一致。否則將在它所要更新的記錄上設(shè)置一個獨(dú)占的鎖定。
• UPDATE ... SET ... WHERE ... : 在搜索時所遭遇到的記錄上設(shè)置一個獨(dú)占的鎖定。
• DELETE FROM ... WHERE ... : 在搜索時所遭遇到的每一個記錄上設(shè)置一個獨(dú)占的鎖定。
• 如果一個表上有 FOREIGN KEY 約束，所有需要檢查約束條件的 insert, update, 或 delete 將在它所要檢查約束的記錄上設(shè)置記錄共享級的鎖定。同樣在約束失敗時，InnoDB 也設(shè)置這個鎖定。
• LOCK TABLES ... : 設(shè)置表鎖定。在 MySQL 的代碼層(layer of code)設(shè)置這些鎖定。InnoDB 的自動死鎖檢測無法檢測出有關(guān)下列情形的表鎖定：查看下面的一個章節(jié)。同時查看第 14 章節(jié) ‘InnoDB 限制與不足‘ 有關(guān)下列內(nèi)容： 自從 MySQL 提供行鎖以來，將有可能發(fā)生當(dāng)其他用戶設(shè)置了行級鎖定時你又對該表設(shè)置了鎖定。But that does not put transaction integerity into danger.
• 在 3.23.50 版本以前， SHOW TABLE STATUS 應(yīng)用于一個自增表時將在自增列的最大記錄索引上設(shè)置一個獨(dú)占的行級鎖定。 這就意味著 SHOW TABLE STATUS 可能會引起一個事務(wù)的死鎖，這可能是我們所意想不到的。從 3.23.50 開始，在讀取自增列值時將不再設(shè)置任何鎖定，除非在某些情況下，比如在數(shù)據(jù)庫啟動后沒有任何記錄。

8.5 MySQL 什么時候隱含地提交(commit)或回滾(rollback)事務(wù)？

• 如果你不使用 SET AUTOCOMMIT=0，MySQL 將會在一個會話中打開自動提交模式。在自動提交模式下，如果一條 SQL 語句沒有返回任何錯誤，MySQL 將在這條 SQL 語句后立即提交。
• 如果一條 SQL 語句返回一個錯誤，那么 commit/rollback 依賴于這個錯誤。查看第國家13 章節(jié)詳細(xì)描述。
• 下列的 SQL 語句在 MySQL 引起中當(dāng)前事務(wù)的隱含提交：CREATE TABLE (如果使用了 MySQL 二進(jìn)制日志‘binlogging‘), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES。 在 InnoDB 中 CREATE TABLE 語句是作為一個單獨(dú)的事務(wù)來處理的。這就意味著一個用戶無法在他的事務(wù)中使用 ROLLBACK 撤銷 CREATE TABLE 語句操作。
• 如果你關(guān)閉了自動提交模式，而在關(guān)閉一個連接之前又未使用 COMMIT 提交你的事務(wù)，那么 MySQL 將回滾你的事務(wù)。

8.6 死鎖檢測與回滾

InnoDB 會自動檢測一個事務(wù)的死鎖并回滾一個或多個事務(wù)來防止死鎖。從 4.0.5 版開始，InnoDB 將設(shè)法提取小的事務(wù)來進(jìn)行回滾。一個事務(wù)的大小由它所插入(insert)、更新(update)和刪除(delete)的數(shù)據(jù)行數(shù)決定。 Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.

InnoDB 不能檢測出由 MySQL 的 LOCK TABLES 語句引起的死鎖，或其它的表類型中的鎖定所引起的死鎖。你不得不通過在 my.cnf 中設(shè)置 innodb_lock_wait_timeout 參數(shù)來解決這些情形。

當(dāng) InnoDB 執(zhí)行一個事務(wù)完整的回滾，這個事務(wù)所有所加的鎖將被釋放。然而，如果只一句的 SQL 語句因結(jié)果返回錯誤而進(jìn)行回滾的，由這條 SQL 語句所設(shè)置的鎖定可能會被保持。這是因為 InnoDB r的行鎖存儲格式無法知道鎖定是由哪個 SQL 語句所設(shè)置。

8.7 consistent read 在 InnoDB 運(yùn)作示例

假設(shè)你以默認(rèn)的 REPEATABLE READ 事務(wù)隔離級水平運(yùn)行。當(dāng)你發(fā)出一個 consistent read 時，即一個普通的 SELECT 語句，InnoDB 將依照你的查詢檢查數(shù)據(jù)庫給你的事務(wù)一個時間點(timepoint)。因而，如果事務(wù) B 在給你指定的時間點后刪除了一行并提交，那么你并不能知道這一行已被刪除。插入(insert)與更新(update)也是一致的。

你可以通過提交你的事務(wù)并重新發(fā)出一個 SELECT 來將你的時間點提前。

這就叫做 multiversioned 并發(fā)控制。

            time
            |
            |
            |
            |
            |
            v 

User A

User B

set autocommit=0;   

set autocommit=0;

SELECT * FROM t;
empty set 

INSERT INTO t VALUES (1, 2);

SELECT * FROM t;
empty set

COMMIT;

SELECT * FROM t; 
empty set; 
COMMIT; 
SELECT * FROM t; 

            ---------------------
 |    1     |    2     | 
 ---------------------

如果你希望查看數(shù)據(jù)庫“最新的(freshest)”狀態(tài)，你必須使用 READ COMMITTED 事務(wù)隔離級，或者你可以使用讀鎖：

SELECT * FROM t LOCK IN SHARE MODE;

8.8 如何應(yīng)付死鎖？

死鎖是事務(wù)處理型數(shù)據(jù)庫系統(tǒng)的一個經(jīng)典問題，但是它們并不是很危險的， 除非它們?nèi)绱说仡l繁以至于你根本處理不了幾個事務(wù)。 當(dāng)因死鎖而產(chǎn)生了回滾時，你通?？梢栽谀愕膽?yīng)用程序中重新發(fā)出一個事務(wù)即可。

InnoDB 使用自動地行級鎖定。你可能恰好在插入或刪除單一一條記錄時產(chǎn)生死鎖。 這是因為這些操作并不是真正“原子(atomic)”級的：他們會自動地在鎖定 inserted/deleted 行的索引記錄(可能有幾個)。

可以通過下面所示的技巧來應(yīng)付死鎖或減少死鎖的次數(shù)：

• 在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 來確定引起最后一個死鎖的原因。這可以幫助你調(diào)整你的應(yīng)用程序來避免死鎖。
• 總是準(zhǔn)備在因死鎖而發(fā)生錯誤時重新發(fā)出一個事務(wù)。死鎖并不危險。僅僅只需重試一遍。
• 經(jīng)常提交你的事務(wù)。小的事務(wù)有較少的碰撞可能。
• 如果使用鎖定讀取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，盡量使用較低的隔離級 READ COMMITTED。
• 以一個固定秩序(a fixed order)訪問你的表和記錄。這樣事務(wù)將形成一個較精細(xì)的隊列，而避免死鎖。
• 為你的表添加合適的索引。那么你的查詢只需要掃描較少的索引，因而設(shè)置較少的鎖定。使用 EXPLAIN SELECT 來確定 MySQL 為你的查詢挑選的適當(dāng)?shù)乃饕?
• 盡量少用鎖定：如果可以通過一個 SELECT 在一個較老的數(shù)據(jù)快照中獲得所需數(shù)據(jù)，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在這時使用 READ COMMITTED 隔離級是較好的主意，因為在同一個事務(wù)中的每個 consistent read 只讀取它最先確定的數(shù)據(jù)快照。
• 如果仍然沒有什么補(bǔ)救效果，使用表級鎖定連載你的事務(wù)(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表級鎖定可以使你的事務(wù)形成精細(xì)的隊列。注意 LOCK TABLES 隱含地啟動一個事務(wù)，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一樣隱含地結(jié)束一個事務(wù)。
• 連載事務(wù)(serialize transactions)的另一個解決辦法就是建立一個僅有一行記錄的輔助“信號量(semaphore)” 表。每一個事務(wù)在訪問其它表之前均更新這個記錄。通過這種方式所有的事務(wù)將持續(xù)執(zhí)行。注意同時 InnoDB 實時死鎖檢測算法也在工作著，因為這個持續(xù)鎖定(serializing lock)是一個行鎖定。在 MySQL 中對于表級鎖定我們必須采取超時方式。