InnoDB 中文參考手冊 --- 8 InnoDB 事務模式與鎖定 (轉)

InnoDB 中文參考手冊 --- 8 InnoDB 事務模式與鎖定 (轉)[@more@] words" content="My,InnoDB,4.1.0,Shuixin13, 4.1.0,中文,中文參考手冊,犬犬(心帆)"> CSS rel=STYLESHEET>

與鎖定

在 InnoDB 事務處理模式中， the goal has been to combine the best properties of a multiversioning database to traditional two-phase locking. InnoDB 進行行級的鎖定，並以與 非鎖定讀取(non-locking)類似的方式讀取資料。 InnoDB 中的鎖定表的是如此(space-efficiently)而不再需要擴大鎖定： 典型特色是一些可能鎖定中的任意行或任意行的子集，而不會引起 InnoDB 執行。

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

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

如果以 AUTOCOMMIT = 1 建立一個連線，那麼使用者仍然可以透過以 BEGIN 開始和 COMMIT 或 ROLLBACK 為語句結束的方式來一個多語句的事務處理。

在 SQL-1992 事務隔離級(transaction isolation levels)規定的條款中，InnoDB 預設為 REPEATABLE READ。從 4.0.5 開始， InnoDB 提供了 SQL-1992 標準中所有的 4 個不同的事務隔離級。你可以 my.cnf 的 [mysqld] 區中設定所有連線的預設事務隔離級：

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 可用。

　

可以透過下列語句查詢全域性和當前會話的事務隔離級：

@@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 的執行使我們不會看到一個記錄的可能更早的版本；因而在這個隔離度下是非 '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 均讀取第一次讀取時已確定的快照。這個約定就意味著如果在同一事務中發出幾個無格式(plain)的 SELECTs ，這些 SELECTs 的相互關係是一致的。
• SERIALIZABLE 這一級與上一級相似，只是無格式(plain)的 SELECTs 被隱含地轉換為 SELECT ... LOCK IN SHARE MODE。

　

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

如果以預設的 REPEATABLE READ 隔離級，那麼所有在同一事務中的 consistent reads 只讀取同一個在事務中第一次讀所確定的快照。 你可以透過提交當前事務併發出一個新的查詢以獲得新的資料快照。

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

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

假設你使用 consistent read 了讀取表 PARENT 並檢視子表中對應記錄。你真的能地在表 CHILD 中加入一個子行？不可能，因為在此期間可能有其它使用者刪除了表 PARENT 中的父行，而你並不知道它。

解決的辦法就是在鎖定的方式 LOCK IN SHARE MODE 下執行一個 SELECT 。

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

在共享模式下執行讀取的意思就是讀取最新的現有資料，並在所讀取的行上設定一個共享模式的鎖定。如果最新的資料屬於其它使用者仍未提交的事務，那將不得不等到這個事務被 提交 。共享模式的可以防止其它使用者或刪除我們當前所讀取的行。當查詢獲得 'Jones'後，就可以安全地向子表 CHILD 中加入子行，然後提交事務。 這個例子顯示如何在應用程式碼中實現參照完整性。

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

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

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

SELECT ... FOR UPDATE 將讀取最新的現有資料，並在所讀取的行上設定排它的鎖定。同樣在 SQL UPDATE 所訪問的行上也設定此鎖定。

在 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的第一條記錄開始掃描索引記錄。 現在，假設加在索引記錄上的鎖定不能阻止在間隙處的插入，一個新的子記錄將可能在事務處理中被插入到表中。 如果現在在事務中再次執行

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

在查詢返回的記錄集中將會有一個新的子記錄。這與事務的隔離規則相違背的：一個事務必須能夠順串(run)， 因而在事務處理中所讀取的資料將不會發生改變。而新的 'phantom' 子記錄將會打破這個隔離規則。

當 InnoDB 掃描索引時，它同樣會鎖定在索引中在結尾記錄(the last record)之後的間隙。這僅僅在上例中會發生： InnoDB 設定的鎖定將阻止任何 ID 大於 100 的插入。

在應用程式中可以透過一個 next-key locking 來實現一個唯一性(uniqueness)檢查：如果以一個共享模式讀取資料並沒有發現與將要插入的資料存在重複值， 那麼在讀取過程中 next-key lock 將被設定在你的記錄的後繼者(successor)上，這將阻止其它使用者在期間插入相同的記錄，因而你可以安全地插入你的記錄。 所以， next-key locking 可以允許你 'lock' 你的表中並不存在的記錄。

中各 SQL 語句的鎖定設定

• SELECT ... FROM ... : 這是一個 consistent read，不以鎖定方式讀取資料庫的快照，除非事務的隔離級被設定為 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 在初始化自增計數器時將在與自增列最後一個記錄相對應的索引上設定一個獨佔的鎖定。在訪問自增計數器時， InnoDB 將設定一個特殊的表鎖定模式 AUTO-INC ，這個鎖定只持續到該 SQL 語句的結束而不是整個事務的結束。
• INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每個記錄上設定一個獨佔的(無 next-key)鎖定。以一個 consistent read 搜尋表 S ，但是如果 MySQL 開啟了日誌開關將在表 S 上設定一個共享的鎖定。 在從中進行前滾(roll-forward)修復時，每個 SQL 語句必須嚴格按照原先所執行的順序執行，所以 InnoDB 不得不設定鎖定。
• CREATE TABLE ... SELECT ... 與上項相似，以 consistent read 或鎖定方式完成 SELECT 。
• REPLACE 如果沒有一個 unique key 衝突，它的執行與 insert 一致。否則將在它所要更新的記錄上設定一個獨佔的鎖定。
• UPDATE ... SET ... WHERE ... : 在搜尋時所遭遇到的記錄上設定一個獨佔的鎖定。
• DELETE FROM ... WHERE ... : 在搜尋時所遭遇到的每一個記錄上設定一個獨佔的鎖定。
• 如果一個表上有 FOREIGN KEY ，所有需要檢查約束條件的 insert, update, 或 delete 將在它所要檢查約束的記錄上設定記錄共享級的鎖定。同樣在約束失敗時，InnoDB 也設定這個鎖定。
• LOCK TABLES ... : 設定表鎖定。在 MySQL 的程式碼層(layer of code)設定這些鎖定。InnoDB 的自動死鎖檢測無法檢測出有關下列情形的表鎖定：檢視下面的一個章節。同時檢視第 14 章節 'InnoDB 限制與不足' 有關下列內容： 自從 MySQL 提供行鎖以來，將有可能發生當其他使用者設定了行級鎖定時你又對該表設定了鎖定。But that does not put transaction integerity into danger.
• 在 3.23.50 版本以前， SHOW TABLE STATUS 應用於一個自增表時將在自增列的最大記錄索引上設定一個獨佔的行級鎖定。 這就意味著 SHOW TABLE STATUS 可能會引起一個事務的死鎖，這可能是我們所意想不到的。從 3.23.50 開始，在讀取自增列值時將不再設定任何鎖定，除非在某些情況下，比如在資料庫啟動後沒有任何記錄。

• 如果你不使用 SET AUTOCOMMIT=0，MySQL 將會在一個會話中開啟自動提交模式。在自動提交模式下，如果一條 SQL 語句沒有返回任何錯誤，MySQL 將在這條 SQL 語句後立即提交。
• 如果一條 SQL 語句返回一個錯誤，那麼 commit/rollback 依賴於這個錯誤。檢視第國家13 章節詳細描述。
• 下列的 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 將回滾你的事務。

InnoDB 會自動檢測一個事務的死鎖並回滾一個或多個事務來防止死鎖。從 4.0.5 版開始，InnoDB 將設法提取小的事務來進行回滾。一個事務的大小由它所插入(insert)、更新(update)和刪除(delete)的資料行數決定。 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 引數來解決這些情形。

當 InnoDB 執行一個事務完整的回滾，這個事務所有所加的鎖將被釋放。然而，如果只一句的 SQL 語句因結果返回錯誤而進行回滾的，由這條 SQL 語句所設定的鎖定可能會被保持。這是因為 InnoDB r的行鎖儲存格式無法知道鎖定是由哪個 SQL 語句所設定。

運作示例

假設你以預設的 REPEATABLE READ 事務隔離級水平執行。當你發出一個 consistent read 時，即一個普通的 SELECT 語句，InnoDB 將依照你的查詢檢查資料庫給你的事務一個時間點(timepoint)。因而，如果事務 B 在給你指定的時間點後刪除了一行並提交，那麼你並不能知道這一行已被刪除。插入(insert)與更新(update)也是一致的。

你可以透過提交你的事務並重新發出一個 SELECT 來將你的時間點提前。

這就叫做 multiversioned 併發控制。

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 所插入的新行。

如果你希望檢視資料庫“最新的(freshest)”狀態，你必須使用 READ COMMITTED 事務隔離級，或者你可以使用讀鎖：

SELECT * FROM t LOCK IN SHARE MODE;

　

死鎖是事務處理型資料庫的一個經典問題，但是它們並不是很危險的， 除非它們如此地頻繁以至於你根本處理不了幾個事務。 當因死鎖而產生了回滾時，你通常可以在你的應用程式中重新發出一個事務即可。

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

可以透過下面所示的技巧來應付死鎖或減少死鎖的次數：

• 在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 來確定引起最後一個死鎖的原因。這可以幫助你調整你的應用程式來避免死鎖。
• 總是準備在因死鎖而發生錯誤時重新發出一個事務。死鎖並不危險。僅僅只需重試一遍。
• 經常提交你的事務。小的事務有較少的碰撞可能。
• 如果使用鎖定讀取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，儘量使用較低的隔離級 READ COMMITTED。
• 以一個固定秩序(a fixed order)訪問你的表和記錄。這樣事務將形成一個較精細的佇列，而避免死鎖。
• 為你的表新增合適的索引。那麼你的查詢只需要掃描較少的索引，因而設定較少的鎖定。使用 EXPLAIN SELECT 來確定 MySQL 為你的查詢挑選的適當的索引。
• 儘量少用鎖定：如果可以透過一個 SELECT 在一個較老的資料快照中獲得所需資料，就不要再新增子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在這時使用 READ COMMITTED 隔離級是較好的主意，因為在同一個事務中的每個 consistent read 只讀取它最先確定的資料快照。
• 如果仍然沒有什麼補救效果，使用表級鎖定連載你的事務(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)” 表。每一個事務在訪問其它表之前均更新這個記錄。透過這種方式所有的事務將持續執行。注意同時 InnoDB 實時死鎖檢測演算法也在工作著，因為這個持續鎖定(serializing lock)是一個行鎖定。在 MySQL 中對於表級鎖定我們必須採取超時方式。