8 InnoDB 事務模式與鎖定
在 InnoDB 事務處理模式中， the goal has been to combine the best properties of a multiversioning database to traditional two-phase locking. InnoDB 進行行級的鎖定，并以與 Oracle 非鎖定讀取(non-locking)類似的方式讀取數(shù)據(jù)。 InnoDB 中的鎖定表的存儲是如此(space-efficiently)而不再需要擴大鎖定： 典型特色是一些用戶可能鎖定數(shù)據(jù)庫中的任意行或任意行的子集，而不會引起 InnoDB 內存運行溢出。
在 InnoDB 中，所有的用戶操作均是以事務方式處理的。如果 MySQL 使用了自動提交(autocommit)方式，每個 SQL 語句將以一個單獨的事務來處理。MySQL 通常是以自動提交方式建立一個服務連接的。
如果使用 SET AUTOCOMMIT = 0 關閉自動提交模式，就認為用戶總是以事務方式操作。如果發(fā)出一個 COMMIT 或 ROLLBACK 的 SQL 語句，它將停止當前的事務而重新開始新事務。兩個語句將會釋放所有在當前事務中設置的 InnoDB 鎖定。COMMIT 意味著永久改變在當前事務中的更改并為其它用戶可見。ROLLBACK 正好相反，它是取消當前事務的所有更改。
如果以 AUTOCOMMIT = 1 建立一個連接，那么用戶仍然可以通過以 BEGIN 開始和 COMMIT 或 ROLLBACK 為語句結束的方式來執(zhí)行一個多語句的事務處理。
在 SQL-1992 事務隔離級(transaction isolation levels)規(guī)定的條款中，InnoDB 默認為 REPEATABLE READ。從 4.0.5 開始， InnoDB 提供了 SQL-1992 標準中所有的 4 個不同的事務隔離級。你可以 my.cnf 的 [mysqld] 區(qū)中設置所有連接的默認事務隔離級： transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED| REPEATABLE-READ | SERIALIZABLE}
用戶也可以通過下面的 SQL 語句為單個連接或所有新建的連接改變隔離級： SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL{READ UNCOMMITTED | READ COMMITTED| REPEATABLE READ | SERIALIZABLE}注意在這個 SQL 語句的語法中沒有連字符。如果你在上述語句中詳細指定關鍵字 GLOBAL ，它將決定新建連接的初始隔離級，但不會改變已有連接的隔離級。任何用戶均可以更改自身會話的隔離級，即使是在一個事務處理過程中。在 3.23.50 以前的版本中 SET TRANSACTION 對 InnoDB 表無任何效果。在 4.0.5 以前的版本中只有 REPEATABLE READ 和SERIALIZABLE 可用。
可以通過下列語句查詢全局和當前會話的事務隔離級： SELECT @@global.tx_isolation;SELECT @@tx_isolation;
在 InnoDB 的行鎖中使用所謂的 next-key locking。這就意味著，除了索引記錄外，InnoDB 還可以鎖定該索引記錄前部“間隙” (‘gap‘) 以阻塞其它用戶在索引記錄前部的直接插入。next-key lock 意思是鎖定一個索引記錄以及該記錄之前的間隙(gap)。gap lock 就是只鎖定某些索引記錄之前的間隙。
InnoDB 中的隔離級詳細描述： READ UNCOMMITTED 這通常稱為 ‘dirty read‘：non-locking SELECTs 的執(zhí)行使我們不會看到一個記錄的可能更早的版本；因而在這個隔離度下是非 ‘consistent‘ reads；另外，這級隔離的運作如同 READ COMMITTED。 READ COMMITTED 有些類似 Oracle 的隔離級。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 語句只鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。以一個唯一地搜索條件使用一個唯一索引(unique index)的 UPDATE 和 DELETE，僅僅只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。但是在范圍型的 UPDATE and DELETE 中，InnoDB 必須設置 next-key 或 gap locks 來阻塞其它用戶對范圍內的空隙插入。 自從為了 MySQL 進行復制(replication)與恢復(recovery)工作‘phantom rows‘必須被阻塞以來，這就是必須的了。Consistent reads 運作方式與 Oracle 有點類似： 每一個 consistent read，甚至是同一個事務中的，均設置并作用它自己的最新快照。 REPEATABLE READ 這是 InnoDB 默認的事務隔離級。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。 否則這些操作將使用 next-key 鎖定，以 next-key 和 gap locks 鎖定找到的索引范圍，并阻塞其它用戶的新建插入。在 consistent reads 中，與前一個隔離級相比這是一個重要的差別： 在這一級中，同一事務中所有的 consistent reads 均讀取第一次讀取時已確定的快照。這個約定就意味著如果在同一事務中發(fā)出幾個無格式(plain)的 SELECTs ，這些 SELECTs 的相互關系是一致的。 SERIALIZABLE 這一級與上一級相似，只是無格式(plain)的 SELECTs 被隱含地轉換為 SELECT ... LOCK IN SHARE MODE。
8.1 Consistent read
Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供給查詢一個數(shù)據(jù)庫在一個時間點的快照。 查詢將會檢查那些在這個時間點之前提交的事務所做的改動，以及在時間點之后改變或未提交的事務? 與這個規(guī)則相例外的是查詢將檢查查詢自身發(fā)出的事務所做的改變。
如果以默認的 REPEATABLE READ 隔離級，那么所有在同一事務中的 consistent reads 只讀取同一個在事務中第一次讀所確定的快照。 你可以通過提交當前事務并發(fā)出一個新的查詢以獲得新的數(shù)據(jù)快照。
Consistent read 在 InnoDB 處理 SELECT 中的默認模式是 READ COMMITTED 和 REPEATABLE READ 隔離級。Consistent read 對其所訪問的表不加任何鎖定，因而其它任何用戶均可以修改在 consistent read 被完成之前自由的修改這些表。
8.2 Locking reads
Consistent read 在某些情況下是不太方便的。 假設你希望在表 CHILD 中插入 一個新行，而這個子表已有一個父表 PARENT。
假設你使用 consistent read 了讀取表 PARENT 并查看子表中對應記錄。你真的能安全地在表 CHILD 中加入一個子行？不可能，因為在此期間可能有其它用戶刪除了表 PARENT 中的父行，而你并不知道它。
解決的辦法就是在鎖定的方式 LOCK IN SHARE MODE 下運行一個 SELECT 。
SELECT * FROM PARENT WHERE NAME = ‘Jones‘ LOCK IN SHARE MODE;
在共享模式下執(zhí)行讀取的意思就是讀取最新的現(xiàn)有資料，并在所讀取的行上設置一個共享模式的鎖定。如果最新的數(shù)據(jù)屬于其它用戶仍未提交的事務，那將不得不等到這個事務被 提交 。共享模式的可以防止其它用戶更新或刪除我們當前所讀取的行。當查詢獲得 ‘Jones‘后，就可以安全地向子表 CHILD 中加入子行，然后提交事務。 這個例子顯示如何在應用程序代碼中實現(xiàn)參照完整性。
另外一個例子： 在表 CHILD_CODES 有一個整型計數(shù)字段用于給在表 CHILD 中加入的每個子行賦于一個唯一的標識符。 顯而易見地，用一個 consistent read 來讀取父表中的值并不是一個好的主意，因兩個用戶有可能會讀取出同一個計數(shù)值，當以同一個標識符插入兩個字行時將會產生一個重復鍵值(duplicate key)的錯誤。如果兩個用戶同時讀取了計數(shù)器，當嘗試更新計數(shù)器時，他們中的一個必將在死鎖中結束，所以在讀取時使用 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ù)，并在所讀取的行上設置排它的鎖定。同樣在 SQL UPDATE 所訪問的行上也設置此鎖定。
8.3 Next-key locking: avoiding the ‘phantom problem‘
在 InnoDB 的行級鎖定上使用一個稱作 next-key locking 算法。在 InnoDB 在搜索或掃描表的索引時將進行行鎖，它將在所訪問到的索引上設置共享或排它的鎖定。因而行鎖是更加精確地而又稱為索引記錄鎖定。
InnoDB 在索引記錄上設置的鎖同樣會影響索引記錄之前的“間隙(gap)”。如果一個用戶對索引記錄 R 加了一個共享或排它的鎖定，那其它用戶將不能在 R 之前立即插入新的記錄。這種間隙鎖定用于防止所謂的“phantom problem”。假設需讀取和鎖定表 CHILD 中標識符大于 100 的子行，并更新所搜索到的記錄中某些字段。
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
假設表 CHILD 中有一個索引字段 ID。我們的查詢將從 ID 大于100的第一條記錄開始掃描索引記錄。 現(xiàn)在，假設加在索引記錄上的鎖定不能阻止在間隙處的插入，一個新的子記錄將可能在事務處理中被插入到表中。 如果現(xiàn)在在事務中再次執(zhí)行
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
在查詢返回的記錄集中將會有一個新的子記錄。這與事務的隔離規(guī)則相違背的：一個事務必須能夠順串(run)， 因而在事務處理中所讀取的數(shù)據(jù)將不會發(fā)生改變。而新的 ‘phantom‘ 子記錄將會打破這個隔離規(guī)則。
當 InnoDB 掃描索引時，它同樣會鎖定在索引中在結尾記錄(the last record)之后的間隙。這僅僅在上例中會發(fā)生： InnoDB 設置的鎖定將阻止任何 ID 大于 100 的插入。
在應用程序中可以通過一個 next-key locking 來實現(xiàn)一個唯一性(uniqueness)檢查：如果以一個共享模式讀取數(shù)據(jù)并沒有發(fā)現(xiàn)與將要插入的數(shù)據(jù)存在重復值， 那么在讀取過程中 next-key lock 將被設置在你的記錄的后繼者(successor)上，這將阻止其它用戶在期間插入相同的記錄，因而你可以安全地插入你的記錄。 所以， next-key locking 可以允許你 ‘lock‘ 你的表中并不存在的記錄。
8.4 InnoDB 中各 SQL 語句的鎖定設置
SELECT ... FROM ... : 這是一個 consistent read，不以鎖定方式讀取數(shù)據(jù)庫的快照，除非事務的隔離級被設置為 SERIALIZABLE，在這種情況下將在它所讀取的記錄索引上設置共享的 next-key locks。 SELECT ... FROM ... LOCK IN SHARE MODE : 在所讀取的所有記錄索引上設置同享的鎖定。 SELECT ... FROM ... FOR UPDATE : 在所讀取的所胡記錄索引上設置獨占地(exclusive)鎖定。 INSERT INTO ... VALUES (...) : 在插入的記錄行上設置一個獨占地鎖定；注意這個鎖定并不是一個 next-key lock ，并不會阻止其它用戶在所插入行之前的間隙(gap)中插入新記錄。如果產生一個重復鍵值錯誤， 在重復索引記錄上設置一個共享的鎖定。 如果在一個表中定義了一個 AUTO_INCREMENT 列，InnoDB 在初始化自增計數(shù)器時將在與自增列最后一個記錄相對應的索引上設置一個獨占的鎖定。在訪問自增計數(shù)器時， InnoDB 將設置一個特殊的表鎖定模式 AUTO-INC ，這個鎖定只持續(xù)到該 SQL 語句的結束而不是整個事務的結束。 INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每個記錄上設置一個獨占的(無 next-key)鎖定。以一個 consistent read 搜索表 S ，但是如果 MySQL 打開了日志開關將在表 S 上設置一個共享的鎖定。 在從備份中進行前滾(roll-forward)修復時，每個 SQL 語句必須嚴格按照原先所執(zhí)行的順序運行，所以 InnoDB 不得不設置鎖定。 CREATE TABLE ... SELECT ... 與上項相似，以 consistent read 或鎖定方式完成 SELECT 。 REPLACE 如果沒有一個 unique key 沖突，它的執(zhí)行與 insert 一致。否則將在它所要更新的記錄上設置一個獨占的鎖定。 UPDATE ... SET ... WHERE ... : 在搜索時所遭遇到的記錄上設置一個獨占的鎖定。 DELETE FROM ... WHERE ... : 在搜索時所遭遇到的每一個記錄上設置一個獨占的鎖定。 如果一個表上有 FOREIGN KEY 約束，所有需要檢查約束條件的 insert, update, 或 delete 將在它所要檢查約束的記錄上設置記錄共享級的鎖定。同樣在約束失敗時，InnoDB 也設置這個鎖定。 LOCK TABLES ... : 設置表鎖定。在 MySQL 的代碼層(layer of code)設置這些鎖定。InnoDB 的自動死鎖檢測無法檢測出有關下列情形的表鎖定：查看下面的一個章節(jié)。同時查看第 14 章節(jié) ‘InnoDB 限制與不足‘ 有關下列內容： 自從 MySQL 提供行鎖以來，將有可能發(fā)生當其他用戶設置了行級鎖定時你又對該表設置了鎖定。But that does not put transaction integerity into danger. 在 3.23.50 版本以前， SHOW TABLE STATUS 應用于一個自增表時將在自增列的最大記錄索引上設置一個獨占的行級鎖定。 這就意味著 SHOW TABLE STATUS 可能會引起一個事務的死鎖，這可能是我們所意想不到的。從 3.23.50 開始，在讀取自增列值時將不再設置任何鎖定，除非在某些情況下，比如在數(shù)據(jù)庫啟動后沒有任何記錄。
8.5 MySQL 什么時候隱含地提交(commit)或回滾(rollback)事務？
如果你不使用 SET AUTOCOMMIT=0，MySQL 將會在一個會話中打開自動提交模式。在自動提交模式下，如果一條 SQL 語句沒有返回任何錯誤，MySQL 將在這條 SQL 語句后立即提交。 如果一條 SQL 語句返回一個錯誤，那么 commit/rollback 依賴于這個錯誤。查看第國家13 章節(jié)詳細描述。 下列的 SQL 語句在 MySQL 引起中當前事務的隱含提交：CREATE TABLE (如果使用了 MySQL 二進制日志‘binlogging‘), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES。 在 InnoDB 中 CREATE TABLE 語句是作為一個單獨的事務來處理的。這就意味著一個用戶無法在他的事務中使用 ROLLBACK 撤銷 CREATE TABLE 語句操作。 如果你關閉了自動提交模式，而在關閉一個連接之前又未使用 COMMIT 提交你的事務，那么 MySQL 將回滾你的事務。
8.6 死鎖檢測與回滾
InnoDB 會自動檢測一個事務的死鎖并回滾一個或多個事務來防止死鎖。從 4.0.5 版開始，InnoDB 將設法提取小的事務來進行回滾。一個事務的大小由它所插入(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 中設置 innodb_lock_wait_timeout 參數(shù)來解決這些情形。
當 InnoDB 執(zhí)行一個事務完整的回滾，這個事務所有所加的鎖將被釋放。然而，如果只一句的 SQL 語句因結果返回錯誤而進行回滾的，由這條 SQL 語句所設置的鎖定可能會被保持。這是因為 InnoDB r的行鎖存儲格式無法知道鎖定是由哪個 SQL 語句所設置。
8.7 consistent read 在 InnoDB 運作示例
假設你以默認的 REPEATABLE READ 事務隔離級水平運行。當你發(fā)出一個 consistent read 時，即一個普通的 SELECT 語句，InnoDB 將依照你的查詢檢查數(shù)據(jù)庫給你的事務一個時間點(timepoint)。因而，如果事務 B 在給你指定的時間點后刪除了一行并提交，那么你并不能知道這一行已被刪除。插入(insert)與更新(update)也是一致的。
你可以通過提交你的事務并重新發(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 |
---------------------
因而，只有當用戶 B 提交了他的插入，并且用戶 A 也提交了他的事務從而使時間點越過 B 提交時的時間點之后，用戶 A 才能看到用戶 B 所插入的新行。
如果你希望查看數(shù)據(jù)庫“最新的(freshest)”狀態(tài)，你必須使用 READ COMMITTED 事務隔離級，或者你可以使用讀鎖： SELECT * FROM t LOCK IN SHARE MODE;
8.8 如何應付死鎖？
死鎖是事務處理型數(shù)據(jù)庫系統(tǒng)的一個經(jīng)典問題，但是它們并不是很危險的， 除非它們如此地頻繁以至于你根本處理不了幾個事務。 當因死鎖而產生了回滾時，你通?？梢栽谀愕膽贸绦蛑兄匦掳l(fā)出一個事務即可。
InnoDB 使用自動地行級鎖定。你可能恰好在插入或刪除單一一條記錄時產生死鎖。 這是因為這些操作并不是真正“原子(atomic)”級的：他們會自動地在鎖定 inserted/deleted 行的索引記錄(可能有幾個)。
可以通過下面所示的技巧來應付死鎖或減少死鎖的次數(shù)： 在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 來確定引起最后一個死鎖的原因。這可以幫助你調整你的應用程序來避免死鎖。 總是準備在因死鎖而發(fā)生錯誤時重新發(fā)出一個事務。死鎖并不危險。僅僅只需重試一遍。 經(jīng)常提交你的事務。小的事務有較少的碰撞可能。 如果使用鎖定讀取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，盡量使用較低的隔離級 READ COMMITTED。 以一個固定秩序(a fixed order)訪問你的表和記錄。這樣事務將形成一個較精細的隊列，而避免死鎖。 為你的表添加合適的索引。那么你的查詢只需要掃描較少的索引，因而設置較少的鎖定。使用 EXPLAIN SELECT 來確定 MySQL 為你的查詢挑選的適當?shù)乃饕?盡量少用鎖定：如果可以通過一個 SELECT 在一個較老的數(shù)據(jù)快照中獲得所需數(shù)據(jù)，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在這時使用 READ COMMITTED 隔離級是較好的主意，因為在同一個事務中的每個 consistent read 只讀取它最先確定的數(shù)據(jù)快照。 如果仍然沒有什么補救效果，使用表級鎖定連載你的事務(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表級鎖定可以使你的事務形成精細的隊列。注意 LOCK TABLES 隱含地啟動一個事務，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一樣隱含地結束一個事務。 連載事務(serialize transactions)的另一個解決辦法就是建立一個僅有一行記錄的輔助“信號量(semaphore)” 表。每一個事務在訪問其它表之前均更新這個記錄。通過這種方式所有的事務將持續(xù)執(zhí)行。注意同時 InnoDB 實時死鎖檢測算法也在工作著，因為這個持續(xù)鎖定(serializing lock)是一個行鎖定。在 MySQL 中對于表級鎖定我們必須采取超時方式。
