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

2、運行溢出。在InnoDB中，所有的用戶操作均是以事務方式處理的。如果MySQL使用了自動提交(autocommit)方式，每個SQL語句將以一個單獨的事務來處理。MySQL通常是以自動提交方式建立一個服務連接的。如果使用SET AUTOCOMMIT=0關閉自動提交模式，就認為用戶總是以事務方式操作。如果發出一個COMMIT或ROLLBACK的SQL語句，它將停止當前的事務而重新開始新事務。兩個語句將會釋放所有在當前事務中設置的InnoDB鎖定。COMMIT意味著永久改變在當前事務中的更改并為其它用戶可見。ROLLBACK正好相反，它是取消當前事務的所有更改。如果以AUTOCOMMIT=1建立一

3、個連接，那么用戶仍然可以通過以BEGIN開始和COMMIT或ROLLBACK為語句結束的方式來執行一個多語句的事務處理。在SQL-1992事務隔離級(transaction isolation levels)規定的條款中，InnoDB默認為REPEATABLE READ。從4.0.5開始，InnoDB提供了SQL-1992標準中所有的4個不同的事務隔離級。你可以f的mysqld區中設置所有連接的默認事務隔離級：transaction-isolation=READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE用戶也可以通

4、過下面的SQL語句為單個連接或所有新建的連接改變隔離級：SETSESSION|GLOBALTRANSACTION ISOLATION LEVELREAD UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE注意在這個SQL語句的語法中沒有連字符。如果你在上述語句中詳細指定關鍵字GLOBAL，它將決定新建連接的初始隔離級，但不會改變已有連接的隔離級。任何用戶均可以更改自身會話的隔離級，即使是在一個事務處理過程中。在3.23.50以前的版本中SET TRANSACTION對InnoDB表無任何效果。在4.0.5以前的版本中只有REPEATA

5、BLE READ和SERIALIZABLE可用。可以通過下列語句查詢全局和當前會話的事務隔離級：；SELECTtx_isolation；在InnoDB的行鎖中使用所謂的next-key locking。這就意味著，除了索引記錄外，InnoDB還可以鎖定該索引記錄前部間隙(gap)以阻塞其它用戶在索引記錄前部的直接插入。next-key lock意思是鎖定一個索引記錄以及該記錄之前的間隙(gap)。gap lock就是只鎖定某些索引記錄之前的間隙。InnoDB中的隔離級詳細描述：READ UNCOMMITTED這通常稱為dirty read：non-locking SELECTs的執行使我們不會

6、看到一個記錄的可能更早的版本；因而在這個隔離度下是非consistentreads；另外，這級隔離的運作如同READ COMMITTED。READ COMMITTED有些類似Oracle的隔離級。所有SELECT.FOR UPDATE和SELECT.LOCK IN SHARE MODE語句只鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。以一個唯一地搜索條件使用一個唯一索引(unique index)的UPDATE和DELETE，僅僅只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。但是在范圍型的UPDATE and DELETE中，InnoDB必須設置next-ke

7、y或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，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引

8、之前的間隙。否則這些操作將使用next-key鎖定，以next-key和gap locks鎖定找到的索引范圍，并阻塞其它用戶的新建插入。在consistent reads中，與前一個隔離級相比這是一個重要的差別：在這一級中，同一事務中所有的consistent reads均讀取第一次讀取時已確定的快照。這個約定就意味著如果在同一事務中發出幾個無格式(plain)的SELECTs，這些SELECTs的相互關系是一致的。SERIALIZABLE這一級與上一級相似，只是無格式(plain)的SELECTs被隱含地轉換為SELECT.LOCK IN SHARE MODE。8.1 Consistent

9、readConsistent read就是InnoDB使用它的多版本(multiversioning)方式提供給查詢一個數據庫在一個時間點的快照。查詢將會檢查那些在這個時間點之前提交的事務所做的改動，以及在時間點之后改變或未提交的事務?與這個規則相例外的是查詢將檢查查詢自身發出的事務所做的改變。如果以默認的REPEATABLE READ隔離級，那么所有在同一事務中的consistent reads只讀取同一個在事務中第一次讀所確定的快照。你可以通過提交當前事務并發出一個新的查詢以獲得新的數據快照。Consistent read在InnoDB處理SELECT中的默認模式是READ COMMITT

10、ED和REPEATABLE READ隔離級。Consistent read對其所訪問的表不加任何鎖定，因而其它任何用戶均可以修改在consistent read被完成之前自由的修改這些表。8.2 Locking readsConsistent read在某些情況下是不太方便的。假設你希望在表CHILD中插入一個新行，而這個子表已有一個父表PARENT。假設你使用consistent read了讀取表PARENT并查看子表中對應記錄。你真的能安全地在表CHILD中加入一個子行?不可能，因為在此期間可能有其它用戶刪除了表PARENT中的父行，而你并不知道它。解決的辦法就是在鎖定的方式LOCK IN

11、 SHARE MODE下運行一個SELECT。SELECT*FROM PARENT WHERE NAME=JonesLOCK IN SHARE MODE；在共享模式下執行讀取的意思就是讀取最新的現有資料，并在所讀取的行上設置一個共享模式的鎖定。如果最新的數據屬于其它用戶仍未提交的事務，那將不得不等到這個事務被提交。共享模式的可以防止其它用戶更新或刪除我們當前所讀取的行。當查詢獲得Jones后，就可以安全地向子表CHILD中加入子行，然后提交事務。這個例子顯示如何在應用程序代碼中實現參照完整性。另外一個例子：在表CHILD_CODES有一個整型計數字段用于給在表CHILD中加入的每個子行賦于一個

12、唯一的標識符。顯而易見地，用一個consistent read來讀取父表中的值并不是一個好的主意，因兩個用戶有可能會讀取出同一個計數值，當以同一個標識符插入兩個字行時將會產生一個重復鍵值(duplicate key)的錯誤。如果兩個用戶同時讀取了計數器，當嘗試更新計數器時，他們中的一個必將在死鎖中結束，所以在讀取時使用LOCK IN SHARE MODE也并不是一個好的解決辦法。在這和情況下有兩種方法來實現讀取并增加計數器：(1)首先更新計數器然后再讀取它；(2)首先以一個FOR UPDATE方式鎖定后再讀取，然后再增加它：SELECT COUNTER_FIELD FROM CHILD_COD

13、ES FOR UPDATE；UPDATE CHILD_CODES SET COUNTER_FIELD=COUNTER_FIELD+1；SELECT.FOR UPDATE將讀取最新的現有數據，并在所讀取的行上設置排它的鎖定。同樣在SQL UPDATE所訪問的行上也設置此鎖定。8.3 Next-key locking：avoiding thephantom problem在InnoDB的行級鎖定上使用一個稱作next-key locking算法。在InnoDB在搜索或掃描表的索引時將進行行鎖，它將在所訪問到的索引上設置共享或排它的鎖定。因而行鎖是更加精確地而又稱為索引記錄鎖定。InnoDB在索引記

14、錄上設置的鎖同樣會影響索引記錄之前的間隙(gap)。如果一個用戶對索引記錄R加了一個共享或排它的鎖定，那其它用戶將不能在R之前立即插入新的記錄。這種間隙鎖定用于防止所謂的phantom problem。假設需讀取和鎖定表CHILD中標識符大于100的子行，并更新所搜索到的記錄中某些字段。SELECT*FROM CHILD WHERE ID 100 FOR UPDATE；假設表CHILD中有一個索引字段ID。我們的查詢將從ID大于100的第一條記錄開始掃描索引記錄。現在，假設加在索引記錄上的鎖定不能阻止在間隙處的插入，一個新的子記錄將可能在事務處理中被插入到表中。如果現在在事務中再次執行SELE

15、CT*FROM CHILD WHERE ID 100 FOR UPDATE；在查詢返回的記錄集中將會有一個新的子記錄。這與事務的隔離規則相違背的：一個事務必須能夠順串(run)，因而在事務處理中所讀取的數據將不會發生改變。而新的phantom子記錄將會打破這個隔離規則。當InnoDB掃描索引時，它同樣會鎖定在索引中在結尾記錄(the last record)之后的間隙。這僅僅在上例中會發生：InnoDB設置的鎖定將阻止任何ID大于100的插入。在應用程序中可以通過一個next-key locking來實現一個唯一性(uniqueness)檢查：如果以一個共享模式讀取數據并沒有發現與將要插入的數

16、據存在重復值，那么在讀取過程中next-key lock將被設置在你的記錄的后繼者(successor)上，這將阻止其它用戶在期間插入相同的記錄，因而你可以安全地插入你的記錄。所以，next-key locking可以允許你lock你的表中并不存在的記錄。8.4 InnoDB中各SQL語句的鎖定設置SELECT.FROM.：這是一個consistent read，不以鎖定方式讀取數據庫的快照，除非事務的隔離級被設置為SERIALIZABLE，在這種情況下將在它所讀取的記錄索引上設置共享的next-key locks。SELECT.FROM.LOCK IN SHARE MODE：在所讀取的所有記

17、錄索引上設置同享的鎖定。SELECT.FROM.FOR UPDATE：在所讀取的所胡記錄索引上設置獨占地(exclusive)鎖定。INSERT INTO.VALUES(.)：在插入的記錄行上設置一個獨占地鎖定；注意這個鎖定并不是一個next-key lock，并不會阻止其它用戶在所插入行之前的間隙(gap)中插入新記錄。如果產生一個重復鍵值錯誤，在重復索引記錄上設置一個共享的鎖定。如果在一個表中定義了一個AUTO_INCREMENT列，InnoDB在初始化自增計數器時將在與自增列最后一個記錄相對應的索引上設置一個獨占的鎖定。在訪問自增計數器時，InnoDB將設置一個特殊的表鎖定模式AUTO-

18、INC，這個鎖定只持續到該SQL語句的結束而不是整個事務的結束。INSERT INTO TSELECT.FROM SWHERE.在已插入到表T中的每個記錄上設置一個獨占的(無next-key)鎖定。以一個consistent read搜索表S，但是如果MySQL打開了日志開關將在表S上設置一個共享的鎖定。在從備份中進行前滾(roll-forward)修復時，每個SQL語句必須嚴格按照原先所執行的順序運行，所以InnoDB不得不設置鎖定。CREATE TABLE.SELECT.與上項相似，以consistent read或鎖定方式完成SELECT。REPLACE如果沒有一個unique key沖

19、突，它的執行與insert一致。否則將在它所要更新的記錄上設置一個獨占的鎖定。UPDATE.SET.WHERE.：在搜索時所遭遇到的記錄上設置一個獨占的鎖定。DELETE FROM.WHERE.：在搜索時所遭遇到的每一個記錄上設置一個獨占的鎖定。如果一個表上有FOREIGN KEY約束，所有需要檢查約束條件的insert,update,或delete將在它所要檢查約束的記錄上設置記錄共享級的鎖定。同樣在約束失敗時，InnoDB也設置這個鎖定。LOCK TABLES.：設置表鎖定。在MySQL的代碼層(layer of code)設置這些鎖定。InnoDB的自動死鎖檢測無法檢測出有關下列情形的表

20、鎖定：查看下面的一個章節。同時查看第14章節InnoDB限制與不足有關下列內容：自從MySQL提供行鎖以來，將有可能發生當其他用戶設置了行級鎖定時你又對該表設置了鎖定。But that does not put transaction integerity into danger.在3.23.50版本以前，SHOW TABLE STATUS應用于一個自增表時將在自增列的最大記錄索引上設置一個獨占的行級鎖定。這就意味著SHOW TABLE STATUS可能會引起一個事務的死鎖，這可能是我們所意想不到的。從3.23.50開始，在讀取自增列值時將不再設置任何鎖定，除非在某些情況下，比如在數據庫啟動后

21、沒有任何記錄。8.5 MySQL什么時候隱含地提交(commit)或回滾(rollback)事務?如果你不使用SET AUTOCOMMIT=0，MySQL將會在一個會話中打開自動提交模式。在自動提交模式下，如果一條SQL語句沒有返回任何錯誤，MySQL將在這條SQL語句后立即提交。如果一條SQL語句返回一個錯誤，那么commit/rollback依賴于這個錯誤。查看第國家13章節詳細描述。下列的SQL語句在MySQL引起中當前事務的隱含提交：CREATE TABLE(如果使用了MySQL二進制日志binlogging),ALTER TABLE,BEGIN,CREATE INDEX,DROP D

22、ATABASE,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)

23、、更新(update)和刪除(delete)的數據行數決定。Previous to 4.0.5,InnoDB always rolled back the transaction whose lock request was the last one to build adeadlock,that is,a cycle in the waits-for graph of transactions.InnoDB不能檢測出由MySQL的LOCK TABLES語句引起的死鎖，或其它的表類型中的鎖定所引起的死鎖。你不得不通過在f中設置innodb_lock_wait_timeout參數來解決

24、這些情形。當InnoDB執行一個事務完整的回滾，這個事務所有所加的鎖將被釋放。然而，如果只一句的SQL語句因結果返回錯誤而進行回滾的，由這條SQL語句所設置的鎖定可能會被保持。這是因為InnoDB r的行鎖存儲格式無法知道鎖定是由哪個SQL語句所設置。8.7 consistent read在InnoDB運作示例假設你以默認的REPEATABLE READ事務隔離級水平運行。當你發出一個consistent read時，即一個普通的SELECT語句，InnoDB將依照你的查詢檢查數據庫給你的事務一個時間點(timepoint)。因而，如果事務B在給你指定的時間點后刪除了一行并提交，那么你并不能知

25、道這一行已被刪除。插入(insert)與更新(update)也是一致的。你可以通過提交你的事務并重新發出一個SELECT來將你的時間點提前。這就叫做multiversioned并發控制。time|v User AUser Bset autocommit=0；set autocommit=0；SELECT*FROM t；empty set INSERT INTO tVALUES(1,2)；SELECT*FROM t；empty set COMMIT；SELECT*FROM t；empty set；COMMIT；SELECT*FROM t；-|1|2|-因而，只有當用戶B提交了他的插入，并且用戶A

26、也提交了他的事務從而使時間點越過B提交時的時間點之后，用戶A才能看到用戶B所插入的新行。如果你希望查看數據庫最新的(freshest)狀態，你必須使用READ COMMITTED事務隔離級，或者你可以使用讀鎖：SELECT*FROM tLOCK IN SHARE MODE；8.8如何應付死鎖?死鎖是事務處理型數據庫系統的一個經典問題，但是它們并不是很危險的，除非它們如此地頻繁以至于你根本處理不了幾個事務。當因死鎖而產生了回滾時，你通常可以在你的應用程序中重新發出一個事務即可。InnoDB使用自動地行級鎖定。你可能恰好在插入或刪除單一一條記錄時產生死鎖。這是因為這些操作并不是真正原子(atomic)級的：他們會自動地在鎖定inserted/deleted行的索引記錄(可能有幾個)。可以通過下面所示的技巧來應付死鎖或減少死鎖的次數：在MySQL=3.23.52和=4.0.3的版本中使用SHOW INNODB STATUS來確定引起最后一個死鎖的原因。這可以幫助你調整你的應用程序來避免死鎖。總是準備在因死鎖而發生錯誤時重新發出一個事務。死鎖并不危險。僅僅只需重試一遍。經常提交你的事務。小的事務有較少的碰撞可能。如果使用鎖定讀取SELECT.FOR UPDATE或.LOCK IN SHARE MODE，盡量使用較低的隔離級READ C