InnoDB鎖

官方文檔: https://dev.MySQL.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

鎖概念

InnoDB 實現(xiàn)了以下兩種類型的行級鎖：

• 共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。其他 session 仍然可以查詢記錄，并也可以對該記錄加 share mode 的共享鎖。但是如果當(dāng)前事務(wù)需要對該記錄進行更新操作，則很有可能造成死鎖。
• 排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。其他 session 可以查詢該記錄，但是不能對該記錄加共享鎖或排他鎖，而是等待獲得鎖

為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB 還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖：

• 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)再給一個數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖。
• 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖。

鎖只有在執(zhí)行commit或者rollback的時候才會釋放，并且所有的鎖都是在同一時刻被釋放。

在 select，update 和 delete 的時候，where 條件如果不存在索引字段，那么這個事務(wù)會導(dǎo)致表鎖（當(dāng)“值重復(fù)率”低時，甚至接近主鍵或者唯一索引的效果，“普通索引”依然是行鎖；當(dāng)“值重復(fù)率”高時，MySQL 不會把這個“普通索引”當(dāng)做索引，即造成了一個沒有索引的 SQL，此時引發(fā)表鎖。）

　　索引不是越多越好,索引每個表應(yīng)控制在5個以內(nèi)，索引存在一個和這個表相關(guān)的文件里，占用硬盤空間，寧缺勿濫，每個表都有主鍵（id），操作能使用主鍵盡量使用主鍵。

八大鎖

行鎖

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

間隙鎖

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

這句話表明間隙鎖一定是開區(qū)間，比如（3，5）或者。在MySQL官網(wǎng)上還有一段非常關(guān)鍵的描述：

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

這段話表明間隙鎖在本質(zhì)上是不區(qū)分共享間隙鎖或互斥間隙鎖的，而且間隙鎖是不互斥的，即兩個事務(wù)可以同時持有包含共同間隙的間隙鎖。這里的共同間隙包括兩種場景：其一是兩個間隙鎖的間隙區(qū)間完全一樣；其二是一個間隙鎖包含的間隙區(qū)間是另一個間隙鎖包含間隙區(qū)間的子集。間隙鎖本質(zhì)上是用于阻止其他事務(wù)在該間隙內(nèi)插入新記錄，而自身事務(wù)是允許在該間隙內(nèi)插入數(shù)據(jù)的。也就是說間隙鎖的應(yīng)用場景包括并發(fā)讀取、并發(fā)更新、并發(fā)刪除和并發(fā)插入。

在MySQL官網(wǎng)上關(guān)于間隙鎖還有一段重要描述：

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

這段話表明，在RU和RC兩種隔離級別下，即使你使用select ... in share mode或select ... for update，也無法防止幻讀（讀后寫的場景）。因為這兩種隔離級別下只會有行鎖，而不會有間隙鎖。這也是為什么示例中要規(guī)定隔離級別為RR的原因。

臨鍵鎖

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

這句話表明臨鍵鎖是行鎖+間隙鎖，即臨鍵鎖是是一個左開右閉的區(qū)間，比如（3，5]。

在MySQL的官方文檔中還有以下重要描述：

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

個人覺得這段話描述得不夠好，很容易引起誤解。這里更正如下：InnoDB的默認(rèn)事務(wù)隔離級別是RR，在這種級別下，如果你使用select ... in share mode或者select ... for update語句，那么InnoDB會使用臨鍵鎖，因而可以防止幻讀；但即使你的隔離級別是RR，如果你這是使用普通的select語句，那么InnoDB將是快照讀，不會使用任何鎖，因而還是無法防止幻讀。

共享鎖/排他鎖

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks。

• A shared (S) lock permits the transaction that holds the lock to read a row.
• An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

這段話明確說名了共享鎖/排他鎖都只是行鎖，與間隙鎖無關(guān)，這一點很重要，后面還會強調(diào)這一點。其中共享鎖是一個事務(wù)并發(fā)讀取某一行記錄所需要持有的鎖，比如select ... in share mode；排他鎖是一個事務(wù)并發(fā)更新或刪除某一行記錄所需要持有的鎖，比如select ... for update。

不過這里需要重點說明的是，盡管共享鎖/排他鎖是行鎖，與間隙鎖無關(guān)，但一個事務(wù)在請求共享鎖/排他鎖時，獲取到的結(jié)果卻可能是行鎖，也可能是間隙鎖，也可能是臨鍵鎖，這取決于數(shù)據(jù)庫的隔離級別以及查詢的數(shù)據(jù)是否存在。關(guān)于這一點，后面分析場景一和場景二的時候還會提到。

意向共享鎖/意向排他鎖

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

The intention locking protocol is as follows:

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

這段話說明意向共享鎖/意向排他鎖屬于表鎖，且取得意向共享鎖/意向排他鎖是取得共享鎖/排他鎖的前置條件。

共享鎖/排他鎖與意向共享鎖/意向排他鎖的兼容性關(guān)系：

XIXSISX互斥互斥互斥互斥IX互斥兼容互斥兼容S互斥互斥兼容兼容IS互斥兼容兼容兼容

這里需要重點關(guān)注的是IX鎖和IX鎖是相互兼容的，這是導(dǎo)致上面場景一發(fā)生死鎖的前置條件，后面會對死鎖原因進行詳細(xì)分析。

插入意向鎖

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

這段話表明盡管插入意向鎖是一種特殊的間隙鎖，但不同于間隙鎖的是，該鎖只用于并發(fā)插入操作。如果說間隙鎖鎖住的是一個區(qū)間，那么插入意向鎖鎖住的就是一個點。因而從這個角度來說，插入意向鎖確實是一種特殊的間隙鎖。與間隙鎖的另一個非常重要的差別是：盡管插入意向鎖也屬于間隙鎖，但兩個事務(wù)卻不能在同一時間內(nèi)一個擁有間隙鎖，另一個擁有該間隙區(qū)間內(nèi)的插入意向鎖（當(dāng)然，插入意向鎖如果不在間隙鎖區(qū)間內(nèi)則是可以的）。這里我們再回顧一下共享鎖和排他鎖：共享鎖用于讀取操作，而排他鎖是用于更新或刪除操作。也就是說插入意向鎖、共享鎖和排他鎖涵蓋了常用的增刪改查四個動作。

自增鎖

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

這段話表明自增鎖是一種特殊的表級鎖，主要用于事務(wù)中插入自增字段，也就是我們最常用的自增主鍵id。通過innodb_autoinc_lock_mode參數(shù)可以設(shè)置自增主鍵的生成策略。為了便于介紹innodb_autoinc_lock_mode參數(shù)，我們先將需要用到自增鎖的Insert語句進行分類：

1）Insert語句分類

1. “INSERT-like” statements(類INSERT語句) （這種語句實際上包含了下面的2、3、4）

所有可以向表中增加行的語句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.

2. “Simple inserts”

可以預(yù)先確定要插入的行數(shù)（當(dāng)語句被初始處理時）的語句。 這包括沒有嵌套子查詢的單行和多行INSERT和REPLACE語句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。

3. “Bulk inserts”

事先不知道要插入的行數(shù)（和所需自動遞增值的數(shù)量）的語句。 這包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句，但不包括純INSERT。 InnoDB在處理每行時一次為AUTO_INCREMENT列分配一個新值。

4. “Mixed-mode inserts”

這些是“Simple inserts”語句但是指定一些（但不是全部）新行的自動遞增值。 示例如下，其中c1是表t1的AUTO_INCREMENT列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一種類型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最壞的情況下實際上是INSERT語句隨后又跟了一個UPDATE，其中AUTO_INCREMENT列的分配值不一定會在 UPDATE 階段使用。

2）InnoDB AUTO_INCREMENT鎖定模式分類

1. innodb_autoinc_lock_mode = 0 (“traditional” lock mode)這種鎖定模式提供了在MySQL 5.1中引入innodb_autoinc_lock_mode配置參數(shù)之前存在的相同行為。傳統(tǒng)的鎖定模式選項用于向后兼容性，性能測試以及解決“Mixed-mode inserts”的問題，因為語義上可能存在差異。在此鎖定模式下，所有“INSERT-like”語句獲得一個特殊的表級AUTO-INC鎖，用于插入具有AUTO_INCREMENT列的表。此鎖定通常保持到語句結(jié)束（不是事務(wù)結(jié)束），以確保為給定的INSERT語句序列以可預(yù)測和可重復(fù)的順序分配自動遞增值，并確保自動遞增由任何給定語句分配的值是連續(xù)的。在**基于語句復(fù)制(statement-based replication)**的情況下，這意味著當(dāng)在從服務(wù)器上復(fù)制SQL語句時，自動增量列使用與主服務(wù)器上相同的值。多個INSERT語句的執(zhí)行結(jié)果是確定性的，SLAVE再現(xiàn)與MASTER相同的數(shù)據(jù)（反之，如果由多個INSERT語句生成的自動遞增值交錯，則兩個并發(fā)INSERT語句的結(jié)果將是不確定的，并且不能使用基于語句的復(fù)制可靠地傳播到從屬服務(wù)器）。
2. innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)這是默認(rèn)的鎖定模式。在這個模式下,“bulk inserts”仍然使用AUTO-INC表級鎖,并保持到語句結(jié)束.這適用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句。同一時刻只有一個語句可以持有AUTO-INC鎖。而“Simple inserts”（要插入的行數(shù)事先已知）通過在mutex（輕量鎖）的控制下獲得所需數(shù)量的自動遞增值來避免表級AUTO-INC鎖， 它只在分配過程的持續(xù)時間內(nèi)保持，而不是直到語句完成。 不使用表級AUTO-INC鎖，除非AUTO-INC鎖由另一個事務(wù)保持。 如果另一個事務(wù)保持AUTO-INC鎖，則“簡單插入”等待AUTO-INC鎖，如同它是一個“批量插入”。此鎖定模式確保,當(dāng)行數(shù)不預(yù)先知道的INSERT存在時(并且自動遞增值在語句過程執(zhí)行中分配)由任何“類INSERT”語句分配的所有自動遞增值是連續(xù)的，并且對于基于語句的復(fù)制(statement-based replication)操作是安全的。這種鎖定模式顯著地提高了可擴展性,并且保證了對于基于語句的復(fù)制(statement-based replication)的安全性。此外，與“傳統(tǒng)”鎖定模式一樣，由任何給定語句分配的自動遞增數(shù)字是連續(xù)的。 與使用自動遞增的任何語句的“傳統(tǒng)”模式相比，語義沒有變化，但有個特殊場景需要注意：The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.也就說對于混合模式的插入，可能會有部分多余自增值丟失。在連續(xù)鎖定模式下，InnoDB可以避免為“Simple inserts”語句使用表級AUTO-INC鎖，其中行數(shù)是預(yù)先已知的，并且仍然保留基于語句的復(fù)制的確定性執(zhí)行和安全性。
3. innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)在這種鎖定模式下,所有類INSERT(“INSERT-like” )語句都不會使用表級AUTO-INC lock,并且可以同時執(zhí)行多個語句。這是最快和最可擴展的鎖定模式，但是當(dāng)使用基于語句的復(fù)制或恢復(fù)方案時，從二進制日志重播SQL語句時，這是不安全的。在此鎖定模式下，自動遞增值保證在所有并發(fā)執(zhí)行的“類INSERT”語句中是唯一且單調(diào)遞增的。但是，由于多個語句可以同時生成數(shù)字（即，跨語句交叉編號），為任何給定語句插入的行生成的值可能不是連續(xù)的。如果執(zhí)行的語句是“simple inserts”，其中要插入的行數(shù)已提前知道，則除了“混合模式插入”之外，為單個語句生成的數(shù)字不會有間隙。然而，當(dāng)執(zhí)行“批量插入”時，在由任何給定語句分配的自動遞增值中可能存在間隙。如果不使用二進制日志作為恢復(fù)或復(fù)制的一部分來重放SQL語句，則可以使用interleaved lock模式來消除所有使用表級AUTO-INC鎖，以實現(xiàn)更大的并發(fā)性和性能,其代價是由于并發(fā)的語句交錯執(zhí)行,同一語句生成的AUTO-INCREMENT值可能會產(chǎn)生GAP。
4. innodb_autoinc_lock_mode參數(shù)的修改編輯/etc/my.cnf，加入如下行:innodb_autoinc_lock_mode=2直接通過命令修改會報錯:mysql(mdba@localhost:(none) 09:32:19)>set global innodb_autoinc_lock_mode=2; ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable

3）InnoDB AUTO_INCREMENT鎖定模式含義

1. 在復(fù)制環(huán)節(jié)中使用自增列

如果你在使用基于語句的復(fù)制(statement-based replication)請將innodb_autoinc_lock_mode設(shè)置為0或1，并在主從上使用相同的值。 如果使用innodb_autoinc_lock_mode = 2（“interleaved”）或主從不使用相同的鎖定模式的配置，自動遞增值不能保證在從機上與主機上相同。

如果使用基于行的或混合模式的復(fù)制，則所有自動增量鎖定模式都是安全的，因為基于行的復(fù)制對SQL語句的執(zhí)行順序不敏感（混合模式會在遇到不安全的語句是使用基于行的復(fù)制模式）。

2. “Lost” auto-increment values and sequence gaps

在所有鎖定模式（0,1和2）中，如果生成自動遞增值的事務(wù)回滾，那些自動遞增值將“丟失”。 一旦為自動增量列生成了值，無論是否完成“類似INSERT”語句以及包含事務(wù)是否回滾，都不能回滾。 這種丟失的值不被重用。 因此，存儲在表的AUTO_INCREMENT列中的值可能存在間隙。

3. Specifying NULL or 0 for the AUTO_INCREMENT column

在所有鎖定模式（0,1和2）中，如果用戶在INSERT中為AUTO_INCREMENT列指定NULL或0，InnoDB會將該行視為未指定值，并為其生成新值。

4. 為AUTO_INCREMENT列分配一個負(fù)值

在所有鎖定模式（0,1和2）中，如果您為AUTO_INCREMENT列分配了一個負(fù)值，則InnoDB會將該行為視為未指定值，并為其生成新值。

5. 如果AUTO_INCREMENT值大于指定整數(shù)類型的最大整數(shù)

在所有鎖定模式（0,1和2）中，如果值大于可以存儲在指定整數(shù)類型中的最大整數(shù)，則InnoDB會將該值設(shè)置為指定類型所允許的最大值。

6. Gaps in auto-increment values for “bulk inserts”

當(dāng)innodb_autoinc_lock_mode設(shè)置為0（“traditional”）或1（“consecutive”）時,任何給定語句生成的自動遞增值是連續(xù)的，沒有間隙，因為表級AUTO-INC鎖會持續(xù)到 語句結(jié)束,并且一次只能執(zhí)行一個這樣的語句。

當(dāng)innodb_autoinc_lock_mode設(shè)置為2（“interleaved”）時，在“bulk inserts”生成的自動遞增值中可能存在間隙，但只有在并發(fā)執(zhí)行“INSERT-Like”語句時才會產(chǎn)生這種情況。

對于鎖定模式1或2，在連續(xù)語句之間可能出現(xiàn)間隙，因為對于批量插入，每個語句所需的自動遞增值的確切數(shù)目可能不為人所知，并且可能進行過度估計。

7. 由“mixed-mode inserts”分配的自動遞增值

考慮一下場景,在“mixed-mode insert”中,其中一個“simple insert”語句指定了一些（但不是全部）行的AUTO-INCREMENT值。 這樣的語句在鎖模式0,1和2中表現(xiàn)不同。innodb_autoinc_lock_mode=0時,auto-increment值一次只分配一個,而不是在開始時全部分配。當(dāng)innodb_autoinc_lock_mode=1時，不同于innodb_autoinc_lock_mode=0時的情況，因為auto-increment值在語句一開始就分配了，但實際可能使用不完。當(dāng)innodb_autoinc_lock_mode=2時，取決于并發(fā)語句的執(zhí)行順序。

8. 在INSERT語句序列的中間修改AUTO_INCREMENT列值

在所有鎖定模式（0,1和2）中，在INSERT語句序列中間修改AUTO_INCREMENT列值可能會導(dǎo)致duplicate key錯誤。

4）InnoDB AUTO_INCREMENT計數(shù)器初始化

如果你為一個Innodb表創(chuàng)建了一個AUTO_INCREMENT列，則InnoDB數(shù)據(jù)字典中的表句柄包含一個稱為自動遞增計數(shù)器的特殊計數(shù)器，用于為列分配新值。 此計數(shù)器僅存在于內(nèi)存中，而不存儲在磁盤上。

要在服務(wù)器重新啟動后初始化自動遞增計數(shù)器，InnoDB將在首次插入行到包含AUTO_INCREMENT列的表時執(zhí)行以下語句的等效語句。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加語句檢索的值，并將其分配給表和表的自動遞增計數(shù)器。 默認(rèn)情況下，值增加1。此默認(rèn)值可以由auto_increment_increment配置設(shè)置覆蓋。

如果表為空，InnoDB使用值1。此默認(rèn)值可以由auto_increment_offset配置設(shè)置覆蓋。

如果在自動遞增計數(shù)器初始化前使用SHOW TABLE STATUS語句查看表, InnoDB將初始化計數(shù)器值,但不會遞增該值。這個值會儲存起來以備之后的插入語句使用。這個初始化過程使用了一個普通的排它鎖來讀取表中自增列的最大值。InnoDB遵循相同的過程來初始化新創(chuàng)建的表的自動遞增計數(shù)器。

在自動遞增計數(shù)器初始化之后，如果您未明確指定AUTO_INCREMENT列的值，InnoDB會遞增計數(shù)器并將新值分配給該列。如果插入顯式指定列值的行，并且該值大于當(dāng)前計數(shù)器值，則將計數(shù)器設(shè)置為指定的列值。

只要服務(wù)器運行，InnoDB就使用內(nèi)存中自動遞增計數(shù)器。當(dāng)服務(wù)器停止并重新啟動時，InnoDB會重新初始化每個表的計數(shù)器，以便對表進行第一次INSERT，如前所述。

服務(wù)器重新啟動還會取消CREATE TABLE和ALTER TABLE語句中的AUTO_INCREMENT = N表選項的效果（可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值，也可用alter table table_name AUTO_INCREMENT=n命令來重設(shè)自增的起始值）。

鎖的發(fā)生時機

官方文檔： https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

• 意向鎖是由 InnoDB 自動添加的， 不需用戶干預(yù)；
• SELECT ... 語句正常情況下為快照讀，不加鎖；
• SELECT ... LOCK IN SHARE MODE 語句為當(dāng)前讀，加 S 鎖（事務(wù)隔離級別為Serializable時，默認(rèn)采用方式）；
• SELECT ... FOR UPDATE 語句為當(dāng)前讀，加 X 鎖；
• 常見的 DML 語句（如 INSERT、DELETE、UPDATE）為當(dāng)前讀，加 X 鎖；
• 常見的 DDL 語句（如 ALTER、CREATE 等）加表級鎖，且這些語句為隱式提交，不能回滾。

其中，當(dāng)前讀的 SQL 語句的 where 從句的不同也會影響加鎖，包括是否使用索引，索引是否是唯一索引等等。

快照讀：簡單的select操作，屬于快照讀，不加鎖。(當(dāng)然，也有例外)

當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，需要加鎖。　　

當(dāng)前讀，讀取記錄的最新版本。并且，讀取之后，還需要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄，對讀取記錄加鎖

為什么將 插入/更新/刪除 操作，都?xì)w為當(dāng)前讀？可以看看下面這個 更新 操作，在數(shù)據(jù)庫中的執(zhí)行流程：

 

從圖中，可以看到，一個Update操作的具體流程。當(dāng)Update SQL被發(fā)給MySQL后，MySQL Server會根據(jù)where條件，讀取第一條滿足條件的記錄，然后InnoDB引擎會將第一條記錄返回，并加鎖 (current read)。待MySQL Server收到這條加鎖的記錄之后，會再發(fā)起一個Update請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止。因此，Update操作內(nèi)部，就包含了一個當(dāng)前讀。同理，Delete操作也一樣。Insert操作會稍微有些不同，簡單來說，就是Insert操作可能會觸發(fā)Unique Key的沖突檢查，也會進行一個當(dāng)前讀。

注：根據(jù)上圖的交互，針對一條當(dāng)前讀的SQL語句，InnoDB與MySQL Server的交互，是一條一條進行的，因此，加鎖也是一條一條進行的。先對一條滿足條件的記錄加鎖，返回給MySQL Server，做一些DML操作；然后在讀取下一條加鎖，直至讀取完畢。

2PL：Two-Phase Locking

　　傳統(tǒng)RDBMS加鎖的一個原則，就是2PL (二階段鎖)：Two-Phase Locking。相對而言，2PL比較容易理解，說的是鎖操作分為兩個階段：加鎖階段與解鎖階段，并且保證加鎖階段與解鎖階段不相交。

 

　從上圖可以看出，2PL就是將加鎖/解鎖分為兩個完全不相交的階段。加鎖階段：只加鎖，不放鎖。解鎖階段：只放鎖，不加鎖。

事務(wù)隔離級別Isolation Level

　　隔離級別：Isolation Level，也是RDBMS的一個關(guān)鍵特性。相信對數(shù)據(jù)庫有所了解的朋友，對于4種隔離級別：Read Uncommited，Read Committed，Repeatable Read，Serializable，都有了深入的認(rèn)識。本文不打算討論數(shù)據(jù)庫理論中，是如何定義這4種隔離級別的含義的，而是跟大家介紹一下MySQL/InnoDB是如何定義這4種隔離級別的。

　　MySQL/InnoDB定義的4種隔離級別：

• Read Uncommited可以讀取未提交記錄。此隔離級別，不會使用，忽略。
• Read Committed (RC)快照讀忽略，本文不考慮。針對當(dāng)前讀，RC隔離級別保證對讀取到的記錄加鎖 (記錄鎖)，存在幻讀現(xiàn)象。
• Repeatable Read (RR)快照讀忽略，本文不考慮。針對當(dāng)前讀，RR隔離級別保證對讀取到的記錄加鎖 (記錄鎖)，同時保證對讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)，不存在幻讀現(xiàn)象。
• Serializable從MVCC并發(fā)控制退化為基于鎖的并發(fā)控制。不區(qū)別快照讀與當(dāng)前讀，所有的讀操作均為當(dāng)前讀，讀加讀鎖 (S鎖)，寫加寫鎖 (X鎖)。Serializable隔離級別下，讀寫沖突，因此并發(fā)度急劇下降，在MySQL/InnoDB下不建議使用。

加鎖過程分析

INSERT 語句加鎖分析

Insert 語句在兩種情況下會加鎖：

• 為了防止幻讀，如果記錄之間加有間隙鎖，此時不能 Insert；
• 如果 Insert 的記錄和已有記錄造成唯一鍵沖突，此時不能 Insert；

除了上述情況，Insert 語句的鎖都是隱式鎖。隱式鎖是 InnoDB 實現(xiàn)的一種延遲加鎖的機制來減少加鎖的數(shù)量。

隱式鎖的特點是只有在可能發(fā)生沖突時才加鎖，減少了鎖的數(shù)量。另外，隱式鎖是針對被修改的 B+Tree 記錄，因此都是記錄類型的鎖，不可能是間隙鎖或 Next-Key 類型。

具體 Insert 語句的加鎖流程如下：

• 首先對插入的間隙加插入意向鎖（Insert Intension Locks）如果該間隙已被加上了間隙鎖或 Next-Key 鎖，則加鎖失敗進入等待；如果沒有，則加鎖成功，表示可以插入；
• 然后判斷插入記錄是否有唯一鍵，如果有，則進行唯一性約束檢查如果不存在相同鍵值，則完成插入如果存在相同鍵值，則判斷該鍵值是否加鎖如果沒有鎖， 判斷該記錄是否被標(biāo)記為刪除如果標(biāo)記為刪除，說明事務(wù)已經(jīng)提交，還沒來得及 purge，這時加 S 鎖等待；如果沒有標(biāo)記刪除，則報 duplicate key 錯誤；如果有鎖，說明該記錄正在處理（新增、刪除或更新），且事務(wù)還未提交，加 S 鎖等待；
• 插入記錄并對記錄加 X 記錄鎖；

DELETE 語句加鎖分析

一般來說，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒有太大的差異。

因為，在 MySQL 數(shù)據(jù)庫中，執(zhí)行 DELETE 語句其實并沒有直接刪除記錄，而是在記錄上打上一個刪除標(biāo)記，然后通過后臺的一個叫做 purge 的線程來清理。從這一點來看，DELETE 和 UPDATE 確實是非常相像。事實上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的。

組合一：id主鍵+RC

這個組合，是最簡單，最容易分析的組合。id是主鍵，Read Committed隔離級別，給定SQL：delete from t1 where id = 10; 只需要將主鍵上，id = 10的記錄加上X鎖即可。如下圖所示：

 

　結(jié)論：id是主鍵時，此SQL只需要在id=10這條記錄上加X鎖即可。

組合二：id唯一索引+RC

這個組合，id不是主鍵，而是一個Unique的二級索引鍵值。那么在RC隔離級別下，delete from t1 where id = 10; 需要加什么鎖呢？見下圖：

 

　此組合中，id是unique索引，而主鍵是name列。此時，加鎖的情況由于組合一有所不同。由于id是unique索引，因此delete語句會選擇走id列的索引進行where條件的過濾，在找到id=10的記錄后，首先會將unique索引上的id=10索引記錄加上X鎖，同時，會根據(jù)讀取到的name列，回主鍵索引(聚簇索引)，然后將聚簇索引上的name = ‘d’ 對應(yīng)的主鍵索引項加X鎖。為什么聚簇索引上的記錄也要加鎖？試想一下，如果并發(fā)的一個SQL，是通過主鍵索引來更新：update t1 set id = 100 where name = ‘d’; 此時，如果delete語句沒有將主鍵索引上的記錄加鎖，那么并發(fā)的update就會感知不到delete語句的存在，違背了同一記錄上的更新/刪除需要串行執(zhí)行的約束。

結(jié)論：若id列是unique列，其上有unique索引。那么SQL需要加兩個X鎖，一個對應(yīng)于id unique索引上的id = 10的記錄，另一把鎖對應(yīng)于聚簇索引上的[name='d',id=10]的記錄。

組合三：id非唯一索引+RC

　相對于組合一、二，組合三又發(fā)生了變化，隔離級別仍舊是RC不變，但是id列上的約束又降低了，id列不再唯一，只有一個普通的索引。假設(shè)delete from t1 where id = 10; 語句，仍舊選擇id列上的索引進行過濾where條件，那么此時會持有哪些鎖？同樣見下圖：

 

根據(jù)此圖，可以看到，首先，id列索引上，滿足id = 10查詢條件的記錄，均已加鎖。同時，這些記錄對應(yīng)的主鍵索引上的記錄也都加上了鎖。與組合二唯一的區(qū)別在于，組合二最多只有一個滿足等值查詢的記錄，而組合三會將所有滿足查詢條件的記錄都加鎖。

結(jié)論：若id列上有非唯一索引，那么對應(yīng)的所有滿足SQL查詢條件的記錄，都會被加鎖。同時，這些記錄在主鍵索引上的記錄，也會被加鎖。

組合四：id無索引+RC

　　相對于前面三個組合，這是一個比較特殊的情況。id列上沒有索引，where id = 10;這個過濾條件，沒法通過索引進行過濾，那么只能走全表掃描做過濾。對應(yīng)于這個組合，SQL會加什么鎖？或者是換句話說，全表掃描時，會加什么鎖？這個答案也有很多：有人說會在表上加X鎖；有人說會將聚簇索引上，選擇出來的id = 10;的記錄加上X鎖。那么實際情況呢？請看下圖：

 

由于id列上沒有索引，因此只能走聚簇索引，進行全部掃描。從圖中可以看到，滿足刪除條件的記錄有兩條，但是，聚簇索引上所有的記錄，都被加上了X鎖。無論記錄是否滿足條件，全部被加上X鎖。既不是加表鎖，也不是在滿足條件的記錄上加行鎖。

　　有人可能會問？為什么不是只在滿足條件的記錄上加鎖呢？這是由于MySQL的實現(xiàn)決定的。如果一個條件無法通過索引快速過濾，那么存儲引擎層面就會將所有記錄加鎖后返回，然后由MySQL Server層進行過濾。因此也就把所有的記錄，都鎖上了。

注：在實際的實現(xiàn)中，MySQL有一些改進，在MySQL Server過濾條件，發(fā)現(xiàn)不滿足后，會調(diào)用unlock_row方法，把不滿足條件的記錄放鎖 (違背了2PL的約束)。這樣做，保證了最后只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。

結(jié)論：若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由于過濾是由MySQL Server層面進行的。因此每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優(yōu)化，對于不滿足條件的記錄，會在判斷后放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。同時，優(yōu)化也違背了2PL的約束。

組合五：id主鍵+RR

　　上面的四個組合，都是在Read Committed隔離級別下的加鎖行為，接下來的四個組合，是在Repeatable Read隔離級別下的加鎖行為。

　　組合五，id列是主鍵列，Repeatable Read隔離級別，針對delete from t1 where id = 10; 這條SQL，加鎖與組合一：[id主鍵，Read Committed]一致。

組合六：id唯一索引+RR

　　與組合五類似，組合六的加鎖，與組合二：[id唯一索引，Read Committed]一致。兩個X鎖，id唯一索引滿足條件的記錄上一個，對應(yīng)的聚簇索引上的記錄一個。

組合七：id非唯一索引+RR

　　還記得前面提到的MySQL的四種隔離級別的區(qū)別嗎？RC隔離級別允許幻讀，而RR隔離級別，不允許存在幻讀。但是在組合五、組合六中，加鎖行為又是與RC下的加鎖行為完全一致。那么RR隔離級別下，如何防止幻讀呢？問題的答案，就在組合七中揭曉。

　　組合七，Repeatable Read隔離級別，id上有一個非唯一索引，執(zhí)行delete from t1 where id = 10; 假設(shè)選擇id列上的索引進行條件過濾，最后的加鎖行為，是怎么樣的呢？同樣看下面這幅圖：

 

此圖，相對于組合三：[id列上非唯一鎖，Read Committed]看似相同，其實卻有很大的區(qū)別。最大的區(qū)別在于，這幅圖中多了一個GAP鎖，而且GAP鎖看起來也不是加在記錄上的，倒像是加載兩條記錄之間的位置，GAP鎖有何用？

　　其實這個多出來的GAP鎖，就是RR隔離級別，相對于RC隔離級別，不會出現(xiàn)幻讀的關(guān)鍵。確實，GAP鎖鎖住的位置，也不是記錄本身，而是兩條記錄之間的GAP。所謂幻讀，就是同一個事務(wù)，連續(xù)做兩次當(dāng)前讀 (例如：select * from t1 where id = 10 for update;)，那么這兩次當(dāng)前讀返回的是完全相同的記錄 (記錄數(shù)量一致，記錄本身也一致)，第二次的當(dāng)前讀，不會比第一次返回更多的記錄 (幻象)。

　　如何保證兩次當(dāng)前讀返回一致的記錄，那就需要在第一次當(dāng)前讀與第二次當(dāng)前讀之間，其他的事務(wù)不會插入新的滿足條件的記錄并提交。為了實現(xiàn)這個功能，GAP鎖應(yīng)運而生。

　　如圖中所示，有哪些位置可以插入新的滿足條件的項 (id = 10)，考慮到B+樹索引的有序性，滿足條件的項一定是連續(xù)存放的。記錄[6,c]之前，不會插入id=10的記錄；[6,c]與[10,b]間可以插入[10, aa]；[10,b]與[10,d]間，可以插入新的[10,bb],[10,c]等；[10,d]與[11,f]間可以插入滿足條件的[10,e],[10,z]等；而[11,f]之后也不會插入滿足條件的記錄。因此，為了保證[6,c]與[10,b]間，[10,b]與[10,d]間，[10,d]與[11,f]不會插入新的滿足條件的記錄，MySQL選擇了用GAP鎖，將這三個GAP給鎖起來。

　　Insert操作，如insert [10,aa]，首先會定位到[6,c]與[10,b]間，然后在插入前，會檢查這個GAP是否已經(jīng)被鎖上，如果被鎖上，則Insert不能插入記錄。因此，通過第一遍的當(dāng)前讀，不僅將滿足條件的記錄鎖上 (X鎖)，與組合三類似。同時還是增加3把GAP鎖，將可能插入滿足條件記錄的3個GAP給鎖上，保證后續(xù)的Insert不能插入新的id=10的記錄，也就杜絕了同一事務(wù)的第二次當(dāng)前讀，出現(xiàn)幻象的情況。

　　有心的朋友看到這兒，可以會問：既然防止幻讀，需要靠GAP鎖的保護，為什么組合五、組合六，也是RR隔離級別，卻不需要加GAP鎖呢？

　　首先，這是一個好問題。其次，回答這個問題，也很簡單。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況。而組合五，id是主鍵；組合六，id是unique鍵，都能夠保證唯一性。一個等值查詢，最多只能返回一條記錄，而且新的相同取值的記錄，一定不會在新插入進來，因此也就避免了GAP鎖的使用。其實，針對此問題，還有一個更深入的問題：如果組合五、組合六下，針對SQL：select * from t1 where id = 10 for update; 第一次查詢，沒有找到滿足查詢條件的記錄，那么GAP鎖是否還能夠省略？此問題留給大家思考。

結(jié)論：Repeatable Read隔離級別下，id列上有一個非唯一索引，對應(yīng)SQL：delete from t1 where id = 10; 首先，通過id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖，然后返回；然后讀取下一條，重復(fù)進行。直至進行到第一條不滿足條件的記錄[11,f]，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結(jié)束。

組合八：id無索引+RR

　　組合八，Repeatable Read隔離級別下的最后一種情況，id列上沒有索引。此時SQL：delete from t1 where id = 10; 沒有其他的路徑可以選擇，只能進行全表掃描。最終的加鎖情況，如下圖所示：

 

如圖，這是一個很恐怖的現(xiàn)象。首先，聚簇索引上的所有記錄，都被加上了X鎖。其次，聚簇索引每條記錄間的間隙(GAP)，也同時被加上了GAP鎖。這個示例表，只有6條記錄，一共需要6個記錄鎖，7個GAP鎖。試想，如果表上有1000萬條記錄呢？

　　在這種情況下，這個表上，除了不加鎖的快照度，其他任何加鎖的并發(fā)SQL，均不能執(zhí)行，不能更新，不能刪除，不能插入，全表被鎖死。

　　當(dāng)然，跟組合四：[id無索引, Read Committed]類似，這個情況下，MySQL也做了一些優(yōu)化，就是所謂的semi-consistent read。semi-consistent read開啟的情況下，對于不滿足查詢條件的記錄，MySQL會提前放鎖。針對上面的這個用例，就是除了記錄[d,10]，[g,10]之外，所有的記錄鎖都會被釋放，同時不加GAP鎖。semi-consistent read如何觸發(fā)：要么是read committed隔離級別；要么是Repeatable Read隔離級別，同時設(shè)置了innodb_locks_unsafe_for_binlog 參數(shù)。更詳細(xì)的關(guān)于semi-consistent read的介紹，可參考我之前的一篇博客：MySQL+InnoDB semi-consitent read原理及實現(xiàn)分析 。

結(jié)論：在Repeatable Read隔離級別下，如果進行全表掃描的當(dāng)前讀，那么會鎖上表中的所有記錄，同時會鎖上聚簇索引內(nèi)的所有GAP，杜絕所有的并發(fā) 更新/刪除/插入 操作。當(dāng)然，也可以通過觸發(fā)semi-consistent read，來緩解加鎖開銷與并發(fā)影響，但是semi-consistent read本身也會帶來其他問題，不建議使用。

組合九：Serializable

　　針對前面提到的簡單的SQL，最后一個情況：Serializable隔離級別。對于SQL2：delete from t1 where id = 10; 來說，Serializable隔離級別與Repeatable Read隔離級別完全一致，因此不做介紹。

　　Serializable隔離級別，影響的是SQL1：select * from t1 where id = 10; 這條SQL，在RC，RR隔離級別下，都是快照讀，不加鎖。但是在Serializable隔離級別，SQL1會加讀鎖，也就是說快照讀不復(fù)存在，MVCC并發(fā)控制降級為Lock-Based CC。

結(jié)論：在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是隔離級別相關(guān)的。Serializable隔離級別，讀不加鎖就不再成立，所有的讀操作，都是當(dāng)前讀。

一條復(fù)雜的sql語句

　寫到這里，其實MySQL的加鎖實現(xiàn)也已經(jīng)介紹的八八九九。只要將本文上面的分析思路，大部分的SQL，都能分析出其會加哪些鎖。而這里，再來看一個稍微復(fù)雜點的SQL，用于說明MySQL加鎖的另外一個邏輯。SQL用例如下：

 

如圖中的SQL，會加什么鎖？假定在Repeatable Read隔離級別下 (Read Committed隔離級別下的加鎖情況，留給讀者分析。)，同時，假設(shè)SQL走的是idx_t1_pu索引。

　　　　在詳細(xì)分析這條SQL的加鎖情況前，還需要有一個知識儲備，那就是一個SQL中的where條件如何拆分？

• Index key：pubtime > 1 and puptime < 20。此條件，用于確定SQL在idx_t1_pu索引上的查詢范圍。
• Index Filter：userid = ‘hdc’ 。此條件，可以在idx_t1_pu索引上進行過濾，但不屬于Index Key。
• Table Filter：comment is not NULL。此條件，在idx_t1_pu索引上無法過濾，只能在聚簇索引上過濾。在分析出SQL where條件的構(gòu)成之后，再來看看這條SQL的加鎖情況 (RR隔離級別)，如下圖所示：從圖中可以看出，在Repeatable Read隔離級別下，由Index Key所確定的范圍，被加上了GAP鎖；Index Filter鎖給定的條件 (userid = ‘hdc’)何時過濾，視MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server層過濾，在5.6后支持了Index Condition Pushdown，則在index上過濾。若不支持ICP，不滿足Index Filter的記錄，也需要加上記錄X鎖，若支持ICP，則不滿足Index Filter的記錄，無需加記錄X鎖 (圖中，用紅色箭頭標(biāo)出的X鎖，是否要加，視是否支持ICP而定)；而Table Filter對應(yīng)的過濾條件，則在聚簇索引中讀取后，在MySQL Server層面過濾，因此聚簇索引上也需要X鎖。最后，選取出了一條滿足條件的記錄[8,hdc,d,5,good]，但是加鎖的數(shù)量，要遠(yuǎn)遠(yuǎn)大于滿足條件的記錄數(shù)量。結(jié)論：在Repeatable Read隔離級別下，針對一個復(fù)雜的SQL，首先需要提取其where條件。Index Key確定的范圍，需要加上GAP鎖；Index Filter過濾條件，視MySQL版本是否支持ICP，若支持ICP，則不滿足Index Filter的記錄，不加X鎖，否則需要X鎖；Table Filter過濾條件，無論是否滿足，都需要加X鎖。

死鎖原理與分析

來看看兩個死鎖的例子 (一個是兩個Session的兩條SQL產(chǎn)生死鎖；另一個是兩個Session的一條SQL，產(chǎn)生死鎖)：

 

 

上面的兩個死鎖用例。第一個非常好理解，也是最常見的死鎖，每個事務(wù)執(zhí)行兩條SQL，分別持有了一把鎖，然后加另一把鎖，產(chǎn)生死鎖。

　　第二個用例，雖然每個Session都只有一條語句，仍舊會產(chǎn)生死鎖。要分析這個死鎖，首先必須用到本文前面提到的MySQL加鎖的規(guī)則。針對Session 1，從name索引出發(fā)，讀到的[hdc, 1]，[hdc, 6]均滿足條件，不僅會加name索引上的記錄X鎖，而且會加聚簇索引上的記錄X鎖，加鎖順序為先[1,hdc,100]，后[6,hdc,10]。而Session 2，從pubtime索引出發(fā)，[10,6],[100,1]均滿足過濾條件，同樣也會加聚簇索引上的記錄X鎖，加鎖順序為[6,hdc,10]，后[1,hdc,100]。發(fā)現(xiàn)沒有，跟Session 1的加鎖順序正好相反，如果兩個Session恰好都持有了第一把鎖，請求加第二把鎖，死鎖就發(fā)生了。　　

結(jié)論：死鎖的發(fā)生與否，并不在于事務(wù)中有多少條SQL語句，死鎖的關(guān)鍵在于：兩個(或以上)的Session加鎖的順序不一致。而使用本文上面提到的，分析MySQL每條SQL語句的加鎖規(guī)則，分析出每條語句的加鎖順序，然后檢查多個并發(fā)SQL間是否存在以相反的順序加鎖的情況，就可以分析出各種潛在的死鎖情況，也可以分析出線上死鎖發(fā)生的原因。

死鎖情況說明

死鎖的第一種情況

一個用戶A 訪問表A(鎖住了表A),然后又訪問表B；另一個用戶B 訪問表B(鎖住了表B)，然后企圖訪問表A；這時用戶A由于用戶B已經(jīng)鎖住表B，它必須等待用戶B釋放表B才能繼續(xù)，同樣用戶B要等用戶A釋放表A才能繼續(xù)，這就死鎖就產(chǎn)生了。

解決方法：

這種死鎖比較常見，是由于程序的BUG產(chǎn)生的，除了調(diào)整的程序的邏輯沒有其它的辦法。仔細(xì)分析程序的邏輯，對于數(shù)據(jù)庫的多表操作時，盡量按照相同的順序進 行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A后B的順序處理， 必須同時鎖定兩個資源時，要保證在任何時刻都應(yīng)該按照相同的順序來鎖定資源。

死鎖的第二種情況

用戶A查詢一條記錄，然后修改該條記錄；這時用戶B修改該條紀(jì)錄，這時用戶A的事務(wù)里鎖的性質(zhì)由查詢的共享鎖企圖上升到獨占鎖，而用戶B里的獨占鎖由于A 有共享鎖存在所以必須等A釋放掉共享鎖，而A由于B的獨占鎖而無法上升的獨占鎖也就不可能釋放共享鎖，于是出現(xiàn)了死鎖。這種死鎖比較隱蔽，但在稍大點的項 目中經(jīng)常發(fā)生。如在某項目中，頁面上的按鈕點擊后，沒有使按鈕立刻失效，使得用戶會多次快速點擊同一按鈕，這樣同一段代碼對數(shù)據(jù)庫同一條記錄進行多次操 作，很容易就出現(xiàn)這種死鎖的情況。

解決方法：

1、對于按鈕等控件，點擊后使其立刻失效，不讓用戶重復(fù)點擊，避免對同時對同一條記錄操作。 2、使用樂觀鎖進行控制。樂觀鎖大多是基于數(shù)據(jù)版本（Version）記錄機制實現(xiàn)。即為數(shù)據(jù)增加一個版本標(biāo)識，在基于數(shù)據(jù)庫表的版本解決方案中，一般是 通過為數(shù)據(jù)庫表增加一個“version”字段來實現(xiàn)。讀取出數(shù)據(jù)時，將此版本號一同讀出，之后更新時，對此版本號加一。此時，將提交數(shù)據(jù)的版本數(shù)據(jù)與數(shù) 據(jù)庫表對應(yīng)記錄的當(dāng)前版本信息進行比對，如果提交的數(shù)據(jù)版本號大于數(shù)據(jù)庫表當(dāng)前版本號，則予以更新，否則認(rèn)為是過期數(shù)據(jù)。樂觀鎖機制避免了長事務(wù)中的數(shù)據(jù) 庫加鎖開銷（用戶A和用戶B操作過程中，都沒有對數(shù)據(jù)庫數(shù)據(jù)加鎖），大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)。Hibernate 在其數(shù)據(jù)訪問引擎中內(nèi)置了樂觀鎖實現(xiàn)。需要注意的是，由于樂觀鎖機制是在我們的系統(tǒng)中實現(xiàn)，來自外部系統(tǒng)的用戶更新操作不受我們系統(tǒng)的控制，因此可能會造 成臟數(shù)據(jù)被更新到數(shù)據(jù)庫中。 3、使用悲觀鎖進行控制。悲觀鎖大多數(shù)情況下依靠數(shù)據(jù)庫的鎖機制實現(xiàn)，如Oracle的Select … for update語句，以保證操作最大程度的獨占性。但隨之而來的就是數(shù)據(jù)庫性能的大量開銷，特別是對長事務(wù)而言，這樣的開銷往往無法承受。如一個金融系統(tǒng)， 當(dāng)某個操作員讀取用戶的數(shù)據(jù)，并在讀出的用戶數(shù)據(jù)的基礎(chǔ)上進行修改時（如更改用戶賬戶余額），如果采用悲觀鎖機制，也就意味著整個操作過程中（從操作員讀 出數(shù)據(jù)、開始修改直至提交修改結(jié)果的全過程，甚至還包括操作員中途去煮咖啡的時間），數(shù)據(jù)庫記錄始終處于加鎖狀態(tài)，可以想見，如果面對成百上千個并發(fā)，這樣的情況將導(dǎo)致災(zāi)難性的后果。所以，采用悲觀鎖進行控制時一定要考慮清楚。

死鎖的第三種情況

如果在事務(wù)中執(zhí)行了一條不滿足條件的update語句，則執(zhí)行全表掃描，把行級鎖上升為表級鎖，多個這樣的事務(wù)執(zhí)行后，就很容易產(chǎn)生死鎖和阻塞。類似的情 況還有當(dāng)表中的數(shù)據(jù)量非常龐大而索引建的過少或不合適的時候，使得經(jīng)常發(fā)生全表掃描，最終應(yīng)用系統(tǒng)會越來越慢，最終發(fā)生阻塞或死鎖。

解決方法：

SQL語句中不要使用太復(fù)雜的關(guān)聯(lián)多表的查詢；使用“執(zhí)行計劃”對SQL語句進行分析，對于有全表掃描的SQL語句，建立相應(yīng)的索引進行優(yōu)化。

死鎖排查

開啟死鎖日志

死鎖的情況打印相關(guān)信息

 

set global innodb_print_all_deadlocks=on;

查看當(dāng)前的錯誤日志配置,缺省情況下位于數(shù)據(jù)目錄

 show variables like 'log_error';

死鎖日志分析

2020-07-30 20:15:12 12329 [ERROR] 
/usr/sbin/mysqld: Sort aborted: Deadlock found when trying to get lock; 
try restarting transaction
2020-08-02 11:33:46 7f8bf24ab700InnoDB: transactions deadlock detected, 
dumping detailed information.
2020-08-02 11:33:46 7f8bf24ab700
*** (1) TRANSACTION:
TRANSACTION 1788888424, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 489 lock struct(s), heap size 79400, 688 row lock(s), undo log entries 241
MySQL thread id 147747, OS thread handle 0x7f8c11066700, 
query id 4136771289 10.8.251.167 App_ecrm updating
update kd_customer_today_pay 
set  last_pay_time=(case when last_pay_time>'2020-08-01 19:08:09'  
                    then last_pay_time else '2020-08-01 19:08:09' end)  
where sys_customer_id=617885769 and shop_id=10016852
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` 
of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888424 
lock_mode X #表示該記錄鎖為排他鎖
locks rec but not gap waiting #表示要加的鎖為記錄鎖、并且處于鎖等待狀態(tài)
Record lock #表示正在等待的是記錄鎖,
heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000024d43049; asc     $ 0I;;
 1: len 8; hex 800000000098d854; asc        T;;
 2: len 8; hex 80000000000dc94e; asc        N;;

*** (2) TRANSACTION:
TRANSACTION 1788888433, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
538 lock struct(s), heap size 79400, 681 row lock(s), undo log entries 323
MySQL thread id 147743, OS thread handle 0x7f8bf24ab700, 
query id 4136771738 10.8.251.167 app_ecrm updating
update kd_customer_today_pay 
set  last_pay_time=(case when last_pay_time>'2020-08-02 06:48:53'  
                    then last_pay_time else '2020-08-02 06:48:53' end)  
where sys_customer_id=617885769 and shop_id=10016852
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` 
of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888433 
lock mode S
Record lock, heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000024d43049; asc     $ 0I;;
 1: len 8; hex 800000000098d854; asc        T;;
 2: len 8; hex 80000000000dc94e; asc        N;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` 
of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888433 
lock_mode X locks rec but not gap waiting
Record lock, heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000024d43049; asc     $ 0I;;
 1: len 8; hex 800000000098d854; asc        T;;
 2: len 8; hex 80000000000dc94e; asc        N;;

*** WE ROLL BACK TRANSACTION (1)

行鎖實現(xiàn)方式

• InnoDB 行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點 MySQL 與 Oracle 不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB 這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！
• 不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數(shù)據(jù)加鎖。
• 只有執(zhí)行計劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計劃的代價來決定的，如果 MySQL 認(rèn)為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。
• 由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然多個session是訪問不同行的記錄， 但是如果是使用相同的索引鍵， 是會出現(xiàn)鎖沖突的（后使用這些索引的session需要等待先使用索引的session釋放鎖后，才能獲取鎖）。

間隙鎖

用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會造成嚴(yán)重的鎖等待。因此，在實際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。

InnoDB使用間隙鎖的目的

1. 防止幻讀，以滿足相關(guān)隔離級別的要求；
2. 滿足恢復(fù)和復(fù)制的需要：

MySQL 通過 BINLOG 錄入執(zhí)行成功的 INSERT、UPDATE、DELETE 等更新數(shù)據(jù)的 SQL 語句，并由此實現(xiàn) MySQL 數(shù)據(jù)庫的恢復(fù)和主從復(fù)制。MySQL 的恢復(fù)機制（復(fù)制其實就是在 Slave Mysql 不斷做基于 BINLOG 的恢復(fù)）有以下特點：

一是 MySQL 的恢復(fù)是 SQL 語句級的，也就是重新執(zhí)行 BINLOG 中的 SQL 語句。

二是 MySQL 的 Binlog 是按照事務(wù)提交的先后順序記錄的， 恢復(fù)也是按這個順序進行的。

由此可見，MySQL 的恢復(fù)機制要求：在一個事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀。

不同隔離級別下的一致性讀及鎖的差異

 

 

鎖競爭情況

可以通過檢查 InnoDB_row_lock 狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：

show status like 'innodb_row_lock%'; 
| Innodb_row_lock_current_waits #當(dāng)前等待鎖的數(shù)量
| Innodb_row_lock_time #系統(tǒng)啟動到現(xiàn)在，鎖定的總時間長度
| Innodb_row_lock_time_avg #每次平均鎖定的時間
| Innodb_row_lock_time_max #最長一次鎖定時間
| Innodb_row_lock_waits #系統(tǒng)啟動到現(xiàn)在總共鎖定的次數(shù)

查看相關(guān)表：

mysql8.0的表位置有差異。可以通過select version();查看當(dāng)前的數(shù)據(jù)庫服務(wù)版本。

# 當(dāng)前運行的所有事務(wù)
select * from information_schema.innodb_trx;
# 重點查詢
select trx_id, trx_state, trx_requested_lock_id, trx_query, trx_operation_state,
trx_tables_locked, trx_rows_locked, trx_rows_modified, 
trx_isolation_level, trx_unique_checks from information_schema.innodb_trx;
| trx_id #事務(wù)ID
| trx_state  #事務(wù)狀態(tài)：
| trx_started #事務(wù)開始時間；
| trx_requested_lock_id  #innodb_locks.lock_id
| trx_wait_started  #事務(wù)開始等待的時間
| trx_mysql_thread_id  #事務(wù)線程ID
| trx_query #具體SQL語句
| trx_operation_state #事務(wù)當(dāng)前操作狀態(tài)
| trx_tables_in_use #事務(wù)中有多少個表被使用
| trx_tables_locked #當(dāng)前執(zhí)行 SQL 的行鎖數(shù)量
| trx_lock_structs #事務(wù)保留的鎖數(shù)量
| trx_lock_memory_bytes #事務(wù)鎖住的內(nèi)存大小（B）
| trx_rows_locked #事務(wù)鎖住的行數(shù)
| trx_rows_modified #事務(wù)更改的行數(shù)
| trx_concurrency_tickets #事務(wù)并發(fā)票數(shù)
| trx_isolation_level #事務(wù)隔離級別
| trx_unique_checks #是否唯一性檢查
| trx_foreign_key_checks #是否外鍵檢查
| trx_last_foreign_key_error #最后的外鍵錯誤

# 當(dāng)前出現(xiàn)的鎖
select * from information_schema.innodb_locks;
# mysql8 select * from performance_schema.data_locks;
| lock_id #鎖ID
| lock_trx_id #擁有鎖的事務(wù)ID
| lock_mode #鎖模式
| lock_type #鎖類型
| lock_table #被鎖的表
| lock_index #被鎖的索引
| lock_space #被鎖的表空間號
| lock_page #被鎖的頁號
| lock_rec #被鎖的記錄號
| lock_data #被鎖的數(shù)據(jù)

# 鎖等待的對應(yīng)關(guān)系
select * from information_schema.innodb_lock_waits;
# mysql8 select * from performance_schema.data_locks_wait;
| requesting_trx_id #請求鎖的事務(wù)ID
| requested_lock_id #請求鎖的鎖ID
| blocking_trx_id #當(dāng)前擁有鎖的事務(wù)ID
| blocking_lock_id #當(dāng)前擁有鎖的鎖ID

LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables，這都是在服務(wù)器層（MySQL Server層）實現(xiàn)的，和存儲引擎無關(guān)，它們有自己的用途，并不能替代事務(wù)處理。 （除了禁用了autocommint后可以使用，其他情況不建議使用）：

• LOCK TABLES 可以鎖定用于當(dāng)前線程的表。如果表被其他線程鎖定，則當(dāng)前線程會等待，直到可以獲取所有鎖定為止。
• UNLOCK TABLES 可以釋放當(dāng)前線程獲得的任何鎖定。當(dāng)前線程執(zhí)行另一個 LOCK TABLES 時， 或當(dāng)與服務(wù)器的連接被關(guān)閉時，所有由當(dāng)前線程鎖定的表被隱含地解鎖

LOCK TABLES語法

• 在用 LOCK TABLES 對 InnoDB 表加鎖時要注意，要將 AUTOCOMMIT 設(shè)為 0，否則MySQL 不會給表加鎖；
• 事務(wù)結(jié)束前，不要用 UNLOCK TABLES 釋放表鎖，因為 UNLOCK TABLES會隱含地提交事務(wù)；
• COMMIT 或 ROLLBACK 并不能釋放用 LOCK TABLES 加的表級鎖，必須用UNLOCK TABLES 釋放表鎖。

正確的方式見如下語句： 例如，如果需要寫表 t1 并從表 t 讀，可以按如下做：

SET AUTOCOMMIT=0; 
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES;

使用LOCK TABLES的場景

給表顯示加表級鎖（InnoDB表和MyISAM都可以），一般是為了在一定程度模擬事務(wù)操作，實現(xiàn)對某一時間點多個表的一致性讀取。（與MyISAM默認(rèn)的表鎖行為類似）

在用 LOCK TABLES 給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且 MySQL 不支持鎖升級。也就是說，在執(zhí)行 LOCK TABLES 后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執(zhí)行查詢操作，而不能執(zhí)行更新操作。

其實，在MyISAM自動加鎖（表鎖）的情況下也大致如此，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現(xiàn)死鎖（Deadlock Free）的原因。

例如，有一個訂單表 orders，其中記錄有各訂單的總金額 total，同時還有一個 訂單明細(xì)表 order_detail，其中記錄有各訂單每一產(chǎn)品的金額小計 subtotal，假設(shè)我們需要檢 查這兩個表的金額合計是否相符，可能就需要執(zhí)行如下兩條 SQL：

Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 

這時，如果不先給兩個表加鎖，就可能產(chǎn)生錯誤的結(jié)果，因為第一條語句執(zhí)行過程中， order_detail 表可能已經(jīng)發(fā)生了改變。因此，正確的方法應(yīng)該是：

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;

（在 LOCK TABLES 時加了“local”選項，其作用就是允許當(dāng)你持有表的讀鎖時，其他用戶可以在滿足 MyISAM 表并發(fā)插入條件的情況下，在表尾并發(fā)插入記錄（MyISAM 存儲引擎支持“并發(fā)插入”））

死鎖（Deadlock Free）

• 死鎖產(chǎn)生：
• 死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)。當(dāng)事務(wù)試圖以不同的順序鎖定資源時，就可能產(chǎn)生死鎖。多個事務(wù)同時鎖定同一個資源時也可能會產(chǎn)生死鎖。鎖的行為和順序和存儲引擎相關(guān)。以同樣的順序執(zhí)行語句，有些存儲引擎會產(chǎn)生死鎖有些不會——死鎖有雙重原因：真正的數(shù)據(jù)沖突；存儲引擎的實現(xiàn)方式。
• **檢測死鎖：**數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時的機制。InnoDB存儲引擎能檢測到死鎖的循環(huán)依賴并立即返回一個錯誤。
• **死鎖恢復(fù)：**死鎖發(fā)生以后，只有部分或完全回滾其中一個事務(wù)，才能打破死鎖，InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務(wù)進行回滾。所以事務(wù)型應(yīng)用程序在設(shè)計時必須考慮如何處理死鎖，多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。
• **外部鎖的死鎖檢測：**發(fā)生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務(wù)釋放鎖并回退，另一個事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖， 這需要通過設(shè)置鎖等待超時參數(shù) innodb_lock_wait_timeout 來解決
• **死鎖影響性能：**死鎖會影響性能而不是會產(chǎn)生嚴(yán)重錯誤，因為InnoDB會自動檢測死鎖狀況并回滾其中一個受影響的事務(wù)。在高并發(fā)系統(tǒng)上，當(dāng)許多線程等待同一個鎖時，死鎖檢測可能導(dǎo)致速度變慢。 有時當(dāng)發(fā)生死鎖時，禁用死鎖檢測（使用innodb_deadlock_detect配置選項）可能會更有效，這時可以依賴innodb_lock_wait_timeout設(shè)置進行事務(wù)回滾。

避免死鎖

• 為了在單個InnoDB表上執(zhí)行多個并發(fā)寫入操作時避免死鎖，可以在事務(wù)開始時通過為預(yù)期要修改的每個元祖（行）使用SELECT ... FOR UPDATE語句來獲取必要的鎖，即使這些行的更改語句是在之后才執(zhí)行的。
• 在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)先申請共享鎖、更新時再申請排他鎖，因為這時候當(dāng)用戶再申請排他鎖時，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖
• 如果事務(wù)需要修改或鎖定多個表，則應(yīng)在每個事務(wù)中以相同的順序使用加鎖語句。 在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機會
• 通過SELECT ... LOCK IN SHARE MODE獲取行的讀鎖后，如果當(dāng)前事務(wù)再需要對該記錄進行更新操作，則很有可能造成死鎖。
• 改變事務(wù)隔離級別

如果出現(xiàn)死鎖，可以用 SHOW INNODB STATUS 命令來確定最后一個死鎖產(chǎn)生的原因。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息，如引發(fā)死鎖的 SQL 語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進措施。

一些優(yōu)化鎖性能的建議

• 盡量使用較低的隔離級別；
• 精心設(shè)計索引， 并盡量使用索引訪問數(shù)據(jù)， 使加鎖更精確， 從而減少鎖沖突的機會
• 選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小
• 給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖
• 不同的程序訪問一組表時，應(yīng)盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會
• 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響
• 不要申請超過實際需要的鎖級別
• 除非必須，查詢時不要顯示加鎖。 MySQL的MVCC可以實現(xiàn)事務(wù)中的查詢不用加鎖，優(yōu)化事務(wù)性能；MVCC只在COMMITTED READ（讀提交）和REPEATABLE READ（可重復(fù)讀）兩種隔離級別下工作
• 對于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能

樂觀鎖、悲觀鎖

• 樂觀鎖(Optimistic Lock)：假設(shè)不會發(fā)生并發(fā)沖突，只在提交操作時檢查是否違反數(shù)據(jù)完整性。 樂觀鎖不能解決臟讀的問題。

樂觀鎖, 顧名思義，就是很樂觀，每次去拿數(shù)據(jù)的時候都認(rèn)為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數(shù)據(jù)，可以使用版本號等機制。樂觀鎖適用于多讀的應(yīng)用類型，這樣可以提高吞吐量，像數(shù)據(jù)庫如果提供類似于write_condition機制的其實都是提供的樂觀鎖。

• 悲觀鎖(Pessimistic Lock)：假定會發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作。

悲觀鎖，顧名思義，就是很悲觀，每次去拿數(shù)據(jù)的時候都認(rèn)為別人會修改，所以每次在拿數(shù)據(jù)的時候都會上鎖，這樣別人想拿這個數(shù)據(jù)就會block直到它拿到鎖。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫里邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

附：隔離（Isolation）級別設(shè)置

#查看當(dāng)前事物級別：
SELECT @@tx_isolation;

 

# 設(shè)置mysql的隔離級別：
# set session transaction isolation level
# 設(shè)置read uncommitted級別：
set session transaction isolation level read uncommitted;
# 設(shè)置read committed級別：
set session transaction isolation level read committed;
# 設(shè)置repeatable read級別：
set session transaction isolation level repeatable read;
# 設(shè)置serializable級別：
set session transaction isolation level serializable;