MysqlInnoDB事務(wù)模式與鎖定

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

2、運(yùn)行溢出。在InnoDB中，所有的用戶(hù)操作均是以事務(wù)方式處理的。如果MySQL使用了自動(dòng)提交(autocommit)方式，每個(gè)SQL語(yǔ)句將以一個(gè)單獨(dú)的事務(wù)來(lái)處理。MySQL通常是以自動(dòng)提交方式建立一個(gè)服務(wù)連接的。如果使用SET AUTOCOMMIT=0關(guān)閉自動(dòng)提交模式，就認(rèn)為用戶(hù)總是以事務(wù)方式操作。如果發(fā)出一個(gè)COMMIT或ROLLBACK的SQL語(yǔ)句，它將停止當(dāng)前的事務(wù)而重新開(kāi)始新事務(wù)。兩個(gè)語(yǔ)句將會(huì)釋放所有在當(dāng)前事務(wù)中設(shè)置的InnoDB鎖定。COMMIT意味著永久改變?cè)诋?dāng)前事務(wù)中的更改并為其它用戶(hù)可見(jiàn)。ROLLBACK正好相反，它是取消當(dāng)前事務(wù)的所有更改。如果以AUTOCOMMIT=1建立一

3、個(gè)連接，那么用戶(hù)仍然可以通過(guò)以BEGIN開(kāi)始和COMMIT或ROLLBACK為語(yǔ)句結(jié)束的方式來(lái)執(zhí)行一個(gè)多語(yǔ)句的事務(wù)處理。在SQL-1992事務(wù)隔離級(jí)(transaction isolation levels)規(guī)定的條款中，InnoDB默認(rèn)為REPEATABLE READ。從4.0.5開(kāi)始，InnoDB提供了SQL-1992標(biāo)準(zhǔn)中所有的4個(gè)不同的事務(wù)隔離級(jí)。你可以f的mysqld區(qū)中設(shè)置所有連接的默認(rèn)事務(wù)隔離級(jí)：transaction-isolation=READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE用戶(hù)也可以通

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

5、BLE READ和SERIALIZABLE可用?？梢酝ㄟ^(guò)下列語(yǔ)句查詢(xún)?nèi)趾彤?dāng)前會(huì)話(huà)的事務(wù)隔離級(jí)：；SELECTtx_isolation；在InnoDB的行鎖中使用所謂的next-key locking。這就意味著，除了索引記錄外，InnoDB還可以鎖定該索引記錄前部間隙(gap)以阻塞其它用戶(hù)在索引記錄前部的直接插入。next-key lock意思是鎖定一個(gè)索引記錄以及該記錄之前的間隙(gap)。gap lock就是只鎖定某些索引記錄之前的間隙。InnoDB中的隔離級(jí)詳細(xì)描述：READ UNCOMMITTED這通常稱(chēng)為dirty read：non-locking SELECTs的執(zhí)行使我們不會(huì)

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

7、y或gap locks來(lái)阻塞其它用戶(hù)對(duì)范圍內(nèi)的空隙插入。自從為了MySQL進(jìn)行復(fù)制(replication)與恢復(fù)(recovery)工作phantom rows必須被阻塞以來(lái)，這就是必須的了。Consistent reads運(yùn)作方式與Oracle有點(diǎn)類(lèi)似：每一個(gè)consistent read，甚至是同一個(gè)事務(wù)中的，均設(shè)置并作用它自己的最新快照。REPEATABLE READ這是InnoDB默認(rèn)的事務(wù)隔離級(jí)。.SELECT.FOR UPDATE,SELECT.LOCK IN SHARE MODE,UPDATE,和DELETE，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引

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

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

10、ED和REPEATABLE READ隔離級(jí)。Consistent read對(duì)其所訪(fǎng)問(wèn)的表不加任何鎖定，因而其它任何用戶(hù)均可以修改在consistent read被完成之前自由的修改這些表。8.2 Locking readsConsistent read在某些情況下是不太方便的。假設(shè)你希望在表CHILD中插入一個(gè)新行，而這個(gè)子表已有一個(gè)父表PARENT。假設(shè)你使用consistent read了讀取表PARENT并查看子表中對(duì)應(yīng)記錄。你真的能安全地在表CHILD中加入一個(gè)子行?不可能，因?yàn)樵诖似陂g可能有其它用戶(hù)刪除了表PARENT中的父行，而你并不知道它。解決的辦法就是在鎖定的方式LOCK IN

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

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

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

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

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

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

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

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

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

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

21、沒(méi)有任何記錄。8.5 MySQL什么時(shí)候隱含地提交(commit)或回滾(rollback)事務(wù)?如果你不使用SET AUTOCOMMIT=0，MySQL將會(huì)在一個(gè)會(huì)話(huà)中打開(kāi)自動(dòng)提交模式。在自動(dòng)提交模式下，如果一條SQL語(yǔ)句沒(méi)有返回任何錯(cuò)誤，MySQL將在這條SQL語(yǔ)句后立即提交。如果一條SQL語(yǔ)句返回一個(gè)錯(cuò)誤，那么commit/rollback依賴(lài)于這個(gè)錯(cuò)誤。查看第國(guó)家13章節(jié)詳細(xì)描述。下列的SQL語(yǔ)句在MySQL引起中當(dāng)前事務(wù)的隱含提交：CREATE TABLE(如果使用了MySQL二進(jìn)制日志binlogging),ALTER TABLE,BEGIN,CREATE INDEX,DROP D

22、ATABASE,DROP TABLE,RENAME TABLE,TRUNCATE,LOCK TABLES,UNLOCK TABLES。在InnoDB中CREATE TABLE語(yǔ)句是作為一個(gè)單獨(dú)的事務(wù)來(lái)處理的。這就意味著一個(gè)用戶(hù)無(wú)法在他的事務(wù)中使用ROLLBACK撤銷(xiāo)CREATE TABLE語(yǔ)句操作。如果你關(guān)閉了自動(dòng)提交模式，而在關(guān)閉一個(gè)連接之前又未使用COMMIT提交你的事務(wù)，那么MySQL將回滾你的事務(wù)。8.6死鎖檢測(cè)與回滾InnoDB會(huì)自動(dòng)檢測(cè)一個(gè)事務(wù)的死鎖并回滾一個(gè)或多個(gè)事務(wù)來(lái)防止死鎖。從4.0.5版開(kāi)始，InnoDB將設(shè)法提取小的事務(wù)來(lái)進(jìn)行回滾。一個(gè)事務(wù)的大小由它所插入(insert)

23、、更新(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 adeadlock,that is,a cycle in the waits-for graph of transactions.InnoDB不能檢測(cè)出由MySQL的LOCK TABLES語(yǔ)句引起的死鎖，或其它的表類(lèi)型中的鎖定所引起的死鎖。你不得不通過(guò)在f中設(shè)置innodb_lock_wait_timeout參數(shù)來(lái)解決

24、這些情形。當(dāng)InnoDB執(zhí)行一個(gè)事務(wù)完整的回滾，這個(gè)事務(wù)所有所加的鎖將被釋放。然而，如果只一句的SQL語(yǔ)句因結(jié)果返回錯(cuò)誤而進(jìn)行回滾的，由這條SQL語(yǔ)句所設(shè)置的鎖定可能會(huì)被保持。這是因?yàn)镮nnoDB r的行鎖存儲(chǔ)格式無(wú)法知道鎖定是由哪個(gè)SQL語(yǔ)句所設(shè)置。8.7 consistent read在InnoDB運(yùn)作示例假設(shè)你以默認(rèn)的REPEATABLE READ事務(wù)隔離級(jí)水平運(yùn)行。當(dāng)你發(fā)出一個(gè)consistent read時(shí)，即一個(gè)普通的SELECT語(yǔ)句，InnoDB將依照你的查詢(xún)檢查數(shù)據(jù)庫(kù)給你的事務(wù)一個(gè)時(shí)間點(diǎn)(timepoint)。因而，如果事務(wù)B在給你指定的時(shí)間點(diǎn)后刪除了一行并提交，那么你并不能知

25、道這一行已被刪除。插入(insert)與更新(update)也是一致的。你可以通過(guò)提交你的事務(wù)并重新發(fā)出一個(gè)SELECT來(lái)將你的時(shí)間點(diǎn)提前。這就叫做multiversioned并發(fā)控制。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|-因而，只有當(dāng)用戶(hù)B提交了他的插入，并且用戶(hù)A

26、也提交了他的事務(wù)從而使時(shí)間點(diǎn)越過(guò)B提交時(shí)的時(shí)間點(diǎn)之后，用戶(hù)A才能看到用戶(hù)B所插入的新行。如果你希望查看數(shù)據(jù)庫(kù)最新的(freshest)狀態(tài)，你必須使用READ COMMITTED事務(wù)隔離級(jí)，或者你可以使用讀鎖：SELECT*FROM tLOCK IN SHARE MODE；8.8如何應(yīng)付死鎖?死鎖是事務(wù)處理型數(shù)據(jù)庫(kù)系統(tǒng)的一個(gè)經(jīng)典問(wèn)題，但是它們并不是很危險(xiǎn)的，除非它們?nèi)绱说仡l繁以至于你根本處理不了幾個(gè)事務(wù)。當(dāng)因死鎖而產(chǎn)生了回滾時(shí)，你通常可以在你的應(yīng)用程序中重新發(fā)出一個(gè)事務(wù)即可。InnoDB使用自動(dòng)地行級(jí)鎖定。你可能恰好在插入或刪除單一一條記錄時(shí)產(chǎn)生死鎖。這是因?yàn)檫@些操作并不是真正原子(atomic)級(jí)的：他們會(huì)自動(dòng)地在鎖定inserted/deleted行的索引記錄(可能有幾個(gè))?？梢酝ㄟ^(guò)下面所示的技巧來(lái)應(yīng)付死鎖或減少死鎖的次數(shù)：在MySQL=3.23.52和=4.0.3的版本中使用SHOW INNODB STATUS來(lái)確定引起最后一個(gè)死鎖的原因。這可以幫助你調(diào)整你的應(yīng)用程序來(lái)避免死鎖?？偸菧?zhǔn)備在因死鎖而發(fā)生錯(cuò)誤時(shí)重新發(fā)出一個(gè)事務(wù)。死鎖并不危險(xiǎn)。僅僅只需重試一遍。經(jīng)常提交你的事務(wù)。小的事務(wù)有較少的碰撞可能。如果使用鎖定讀取SELECT.FOR UPDATE或.LOCK IN SHARE MODE，盡量使用較低的隔離級(jí)READ C