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)行行級(jí)的鎖定，并以與 Oracle 非鎖定讀取(non-locking)類似的方式讀取數(shù)據(jù)。 InnoDB 中的鎖定表的存儲(chǔ)是如此(space-efficiently)而不再需要擴(kuò)大鎖定： 典型特色是一些用戶可能鎖定數(shù)據(jù)庫(kù)中的任意行或任意行的子集，而不會(huì)引起 InnoDB 內(nèi)存運(yùn)行溢出。

在 InnoDB 中，所有的用戶操作均是以事務(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)為用戶總是以事務(wù)方式操作。如果發(fā)出一個(gè) COMMIT 或 ROLLBACK 的 SQL 語(yǔ)句，它將停止當(dāng)前的事務(wù)而重新開始新事務(wù)。兩個(gè)語(yǔ)句將會(huì)釋放所有在當(dāng)前事務(wù)中設(shè)置的 InnoDB 鎖定。COMMIT 意味著永久改變?cè)诋?dāng)前事務(wù)中的更改并為其它用戶可見。ROLLBACK 正好相反，它是取消當(dāng)前事務(wù)的所有更改。

如果以 AUTOCOMMIT = 1 建立一個(gè)連接，那么用戶仍然可以通過(guò)以 BEGIN 開始和 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 開始， InnoDB 提供了 SQL-1992 標(biāo)準(zhǔn)中所有的 4 個(gè)不同的事務(wù)隔離級(jí)。你可以 my.cnf 的 [mysqld] 區(qū)中設(shè)置所有連接的默認(rèn)事務(wù)隔離級(jí)：

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

用戶也可以通過(guò)下面的 SQL 語(yǔ)句為單個(gè)連接或所有新建的連接改變隔離級(jí)：

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

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

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

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

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

• READ UNCOMMITTED 這通常稱為 ‘dirty read‘：non-locking SELECTs 的執(zhí)行使我們不會(huì)看到一個(gè)記錄的可能更早的版本；因而在這個(gè)隔離度下是非 ‘consistent‘ reads；另外，這級(jí)隔離的運(yùn)作如同 READ COMMITTED。
• READ COMMITTED 有些類似 Oracle 的隔離級(jí)。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 語(yǔ)句只鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。以一個(gè)唯一地搜索條件使用一個(gè)唯一索引(unique index)的 UPDATE 和 DELETE，僅僅只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。但是在范圍型的 UPDATE and DELETE 中，InnoDB 必須設(shè)置 next-key 或 gap locks 來(lái)阻塞其它用戶對(duì)范圍內(nèi)的空隙插入。 自從為了 MySQL 進(jìn)行復(fù)制(replication)與恢復(fù)(recovery)工作‘phantom rows‘必須被阻塞以來(lái)，這就是必須的了。Consistent reads 運(yùn)作方式與 Oracle 有點(diǎn)類似： 每一個(gè) consistent read，甚至是同一個(gè)事務(wù)中的，均設(shè)置并作用它自己的最新快照。
• REPEATABLE READ 這是 InnoDB 默認(rèn)的事務(wù)隔離級(jí)。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。 否則這些操作將使用 next-key 鎖定，以 next-key 和 gap locks 鎖定找到的索引范圍，并阻塞其它用戶的新建插入。在 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 read

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

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

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

8.2 Locking reads

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

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

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

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

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

另外一個(gè)例子： 在表 CHILD_CODES 有一個(gè)整型計(jì)數(shù)字段用于給在表 CHILD 中加入的每個(gè)子行賦于一個(gè)唯一的標(biāo)識(shí)符。 顯而易見地，用一個(gè) consistent read 來(lái)讀取父表中的值并不是一個(gè)好的主意，因兩個(gè)用戶有可能會(huì)讀取出同一個(gè)計(jì)數(shù)值，當(dāng)以同一個(gè)標(biāo)識(shí)符插入兩個(gè)字行時(shí)將會(huì)產(chǎn)生一個(gè)重復(fù)鍵值(duplicate key)的錯(cuò)誤。如果兩個(gè)用戶同時(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_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

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

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

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

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

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

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

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

在查詢返回的記錄集中將會(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ù)據(jù)存在重復(fù)值， 那么在讀取過(guò)程中 next-key lock 將被設(shè)置在你的記錄的后繼者(successor)上，這將阻止其它用戶在期間插入相同的記錄，因而你可以安全地插入你的記錄。 所以， 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 : 在所讀取的所有記錄索引上設(shè)置同享的鎖定。
• SELECT ... FROM ... FOR UPDATE : 在所讀取的所胡記錄索引上設(shè)置獨(dú)占地(exclusive)鎖定。
• INSERT INTO ... VALUES (...) : 在插入的記錄行上設(shè)置一個(gè)獨(dú)占地鎖定；注意這個(gè)鎖定并不是一個(gè) next-key lock ，并不會(huì)阻止其它用戶在所插入行之前的間隙(gap)中插入新記錄。如果產(chǎn)生一個(gè)重復(fù)鍵值錯(cuò)誤， 在重復(fù)索引記錄上設(shè)置一個(gè)共享的鎖定。
• 如果在一個(gè)表中定義了一個(gè) AUTO_INCREMENT 列，InnoDB 在初始化自增計(jì)數(shù)器時(shí)將在與自增列最后一個(gè)記錄相對(duì)應(yīng)的索引上設(shè)置一個(gè)獨(dú)占的鎖定。在訪問(wèn)自增計(jì)數(shù)器時(shí)， InnoDB 將設(shè)置一個(gè)特殊的表鎖定模式 AUTO-INC ，這個(gè)鎖定只持續(xù)到該 SQL 語(yǔ)句的結(jié)束而不是整個(gè)事務(wù)的結(jié)束。
• INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每個(gè)記錄上設(shè)置一個(gè)獨(dú)占的(無(wú) next-key)鎖定。以一個(gè) consistent read 搜索表 S ，但是如果 MySQL 打開了日志開關(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 沖突，它的執(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)下列情形的表鎖定：查看下面的一個(gè)章節(jié)。同時(shí)查看第 14 章節(jié) ‘InnoDB 限制與不足‘ 有關(guān)下列內(nèi)容： 自從 MySQL 提供行鎖以來(lái)，將有可能發(fā)生當(dāng)其他用戶設(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 開始，在讀取自增列值時(shí)將不再設(shè)置任何鎖定，除非在某些情況下，比如在數(shù)據(jù)庫(kù)啟動(dòng)后沒(méi)有任何記錄。

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

• 如果你不使用 SET AUTOCOMMIT=0，MySQL 將會(huì)在一個(gè)會(huì)話中打開自動(dòng)提交模式。在自動(dòng)提交模式下，如果一條 SQL 語(yǔ)句沒(méi)有返回任何錯(cuò)誤，MySQL 將在這條 SQL 語(yǔ)句后立即提交。
• 如果一條 SQL 語(yǔ)句返回一個(gè)錯(cuò)誤，那么 commit/rollback 依賴于這個(gè)錯(cuò)誤。查看第國(guó)家13 章節(jié)詳細(xì)描述。
• 下列的 SQL 語(yǔ)句在 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 語(yǔ)句是作為一個(gè)單獨(dú)的事務(wù)來(lái)處理的。這就意味著一個(gè)用戶無(wú)法在他的事務(wù)中使用 ROLLBACK 撤銷 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 版開始，InnoDB 將設(shè)法提取小的事務(wù)來(lái)進(jìn)行回滾。一個(gè)事務(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 不能檢測(cè)出由 MySQL 的 LOCK TABLES 語(yǔ)句引起的死鎖，或其它的表類型中的鎖定所引起的死鎖。你不得不通過(guò)在 my.cnf 中設(shè)置 innodb_lock_wait_timeout 參數(shù)來(lái)解決這些情形。

當(dāng) InnoDB 執(zhí)行一個(gè)事務(wù)完整的回滾，這個(gè)事務(wù)所有所加的鎖將被釋放。然而，如果只一句的 SQL 語(yǔ)句因結(jié)果返回錯(cuò)誤而進(jìn)行回滾的，由這條 SQL 語(yǔ)句所設(shè)置的鎖定可能會(huì)被保持。這是因?yàn)?InnoDB 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 將依照你的查詢檢查數(shù)據(jù)庫(kù)給你的事務(wù)一個(gè)時(shí)間點(diǎn)(timepoint)。因而，如果事務(wù) B 在給你指定的時(shí)間點(diǎn)后刪除了一行并提交，那么你并不能知道這一行已被刪除。插入(insert)與更新(update)也是一致的。

你可以通過(guò)提交你的事務(wù)并重新發(fā)出一個(gè) SELECT 來(lái)將你的時(shí)間點(diǎn)提前。

這就叫做 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ù)庫(kù)“最新的(freshest)”狀態(tài)，你必須使用 READ COMMITTED 事務(wù)隔離級(jí)，或者你可以使用讀鎖：

SELECT * FROM t LOCK 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 COMMITTED。
• 以一個(gè)固定秩序(a fixed order)訪問(wèn)你的表和記錄。這樣事務(wù)將形成一個(gè)較精細(xì)的隊(duì)列，而避免死鎖。
• 為你的表添加合適的索引。那么你的查詢只需要掃描較少的索引，因而設(shè)置較少的鎖定。使用 EXPLAIN SELECT 來(lái)確定 MySQL 為你的查詢挑選的適當(dāng)?shù)乃饕?
• 盡量少用鎖定：如果可以通過(guò)一個(gè) SELECT 在一個(gè)較老的數(shù)據(jù)快照中獲得所需數(shù)據(jù)，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在這時(shí)使用 READ COMMITTED 隔離級(jí)是較好的主意，因?yàn)樵谕粋€(gè)事務(wù)中的每個(gè) consistent read 只讀取它最先確定的數(shù)據(jù)快照。
• 如果仍然沒(méi)有什么補(bǔ)救效果，使用表級(jí)鎖定連載你的事務(wù)(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表級(jí)鎖定可以使你的事務(wù)形成精細(xì)的隊(duì)列。注意 LOCK TABLES 隱含地啟動(dòng)一個(gè)事務(wù)，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一樣隱含地結(jié)束一個(gè)事務(wù)。
• 連載事務(wù)(serialize transactions)的另一個(gè)解決辦法就是建立一個(gè)僅有一行記錄的輔助“信號(hào)量(semaphore)” 表。每一個(gè)事務(wù)在訪問(wèn)其它表之前均更新這個(gè)記錄。通過(guò)這種方式所有的事務(wù)將持續(xù)執(zhí)行。注意同時(shí) InnoDB 實(shí)時(shí)死鎖檢測(cè)算法也在工作著，因?yàn)檫@個(gè)持續(xù)鎖定(serializing lock)是一個(gè)行鎖定。在 MySQL 中對(duì)于表級(jí)鎖定我們必須采取超時(shí)方式。