全文主要內(nèi)容

1. MYSQL InnoDB引擎的鎖類型以及特點
2. 不同SQL語句的加鎖情況
3. 鎖之間的兼容性關(guān)系
4. 死鎖發(fā)現(xiàn)
5. 死鎖分析
6. 減少死鎖發(fā)生以及死鎖處理的解決方案

鎖集合

Record Lock

A record lock is a lock on an index record.

這個就比較容易理解，就是記錄鎖。鎖加在索引上，如果表沒有設(shè)置索引。那么會加在Innodb的隱藏的聚集索引上。

在InnoDB的monitor中經(jīng)常以這樣的字眼出現(xiàn)，

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 lock_mode X locks rec but not gap Record lock

Rec but not gap 代表的就是 記錄鎖了

Gap Lock

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。

間隙鎖就是一種鎖在記錄與記錄之間間隙的一種范圍的鎖，它是鎖一定的范圍。經(jīng)常用于RR隔離級別下的防止幻讀的解決方案。

如果索引是唯一索引，并且完全命中的話。也不會發(fā)生間隙鎖，因為記錄可以唯一確定。如果是多字段組合的唯一索引，然后where條件中只有部分字段命中索引，那么還是會發(fā)生GAP lock的。

總結(jié)一下，我認為發(fā)生間隙鎖的核心是

是否不加間隙鎖會導(dǎo)致出現(xiàn)多次讀寫的結(jié)果記錄不一致

后面遇到具體的不同SQL語句加鎖情況的時候，我也會再次提及這個核心條件。

關(guān)閉間隙鎖的方式：

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

大概的意思就是說使用了RC的隔離級別就不會發(fā)生間隙鎖了，因為RC允許幻讀的存在，同時這個時候間隙鎖只會發(fā)生在外鍵檢查和主鍵重復(fù)檢查的沖突中。

還有一個比較容易忽略的點，關(guān)于間隙鎖的在掃描遇到不滿足條件的記錄的時候是否會釋放。這個問題 不同的隔離級別所表現(xiàn)的形式也是不一樣的。在MYSQL官網(wǎng)的手冊中有說提到這個問題，實際上這個問題我之前也沒有仔細去看。直到我看到了一個微信公眾號的文章，也是關(guān)于鎖的分組實驗。我才發(fā)現(xiàn)了這個現(xiàn)象的存在。

原文如下:

There are also other effects of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

在RC的隔離級別下，鎖會在不匹配記錄的情況下被釋放。如果是當你要掃描的記錄已經(jīng)被鎖住的情況下，那么你就會被阻塞。無論這個記錄是否滿足你的過濾條件，你可以理解成 MYSQL要判斷你的記錄符不符合要求的前提是拿到這條記錄，但是這個時候記錄已經(jīng)被上鎖無法獲取，所以你只能阻塞住。

而在UPDATE的場景下，MYSQL做了一些優(yōu)化，一種叫做"semi-consistent"的讀操作，可以繞過阻塞。它會返回這條記錄的最新版本給到MYSQL，讓它去判斷是否是滿足條件的記錄，如果是命中過濾條件的記錄則會再次發(fā)起讀操作。這個時候就要不是獲取鎖，要不就是被阻塞。

Next Key Lock

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.

就是記錄鎖和間隙鎖的組合。

一般是RR隔離級別中用來搜索和掃描索引的一種加鎖方式。目的就是防止幻讀

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

看到這里是不是對在面試過程中，經(jīng)常被問到為什么MYSQL InnoDB的RR隔離級別就能完全保證事務(wù)的隔離性要求。而不像其他的數(shù)據(jù)庫系統(tǒng)，可能需要 “串行化”的隔離級別才能達到？

原因就是Next Key Lock算法

Intention Lock

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. 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.

MYSQL的InnoDB引擎為了實現(xiàn)多粒度鎖使用的一種解決方案，就是“意向鎖”。通過意向鎖，事務(wù)會表達它準備對數(shù)據(jù)記錄執(zhí)行的操作動作。

一共有兩種類型的意向鎖，一種是IX intention exclusive lock，一種是IS intention share lock。

意向鎖是表級鎖的類型，只與表級的操作可能發(fā)生沖突。不會與任何行級操作發(fā)生沖突，同時意向鎖之間也是兼容不沖突的

表級鎖類型之間的兼容性關(guān)系表，行代表已經(jīng)持有的鎖類型，列代表即將要請求的鎖類型。conflict代表沖突，compatible代表兼容。

Insert Intention Lock

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.

也是意向鎖，只不過這個是叫做“插入意向鎖”。但是它和剛剛的意向鎖有一些不同，它是一種輕量級的Gap鎖。主要目的是事務(wù)表達“插入”意向。

插入意向鎖會和Gap鎖互斥沖突，來解決幻讀的問題。

比如事務(wù)1，在非唯一索引上執(zhí)行了select * from table where column > 100 for update。此時column上加了Gap鎖，而如果這個時候事務(wù)2，想要插入一條記錄到Gap間隙中去，就會發(fā)生insert intention waiting的日志在InnoDB monitor 的output里面。

Shared And Exclusive Lock

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

這是InnoDB實現(xiàn)了兩種類型標準的行級鎖，一種是共享鎖，一種是排它鎖。這兩個類型應(yīng)該就是鎖的兩大基礎(chǔ)類型。無論你是record lock 還是 gap lock 都有S或者X之分。

• 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.

共享鎖允許事務(wù)共享，讀取一行數(shù)據(jù)。排它鎖允許事務(wù)取更新或者刪除一行數(shù)據(jù)。

S和X鎖的兼容性關(guān)系表格

行代表的是當前事務(wù)持有的鎖類型，列代表的是事務(wù)想要請求的鎖類型

Auto_Inc Lock

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

自增鎖是一種特殊的表級鎖，它是為了保證不同并發(fā)事務(wù)之間能夠正確的獲取連續(xù)的自增ID值。所以加了表級的鎖。

為了提供并發(fā)插入的性能，它實際上有三種鎖模式，在不同的insert語句的操作下這些不同的鎖模式會帶來不一樣的并發(fā)效果。

畢竟如果是表鎖的話，我們平時項目如果高并發(fā)插入，肯定性能就會降低很多?？墒瞧綍r也沒什么感覺，這其中的奧秘就在于自增鎖的幾種模式。了解和熟悉之后，應(yīng)該會明白很多。

MYSQL手冊中先介紹了幾種不同的插入statement的概念

INSERT-like statement

和插入相關(guān)的語句集合，特指下面全部的這些插入語句類型。

Simple inserts

這種的意思就是簡單插入，插入的記錄數(shù)可以在執(zhí)行的時候提前知道。常見的比如 insert replace 等 直接插入給定數(shù)據(jù)的語句。

不包括 INSERT ... ON DUPLICATE KEY UPDATE的語句

Bulk inserts

批量插入，針對的是那些不能提前知道數(shù)據(jù)量的插入語句。比如INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements。

不包括語句中已經(jīng)為每一行的主鍵列都指定了值的批量插入語句

比如這樣的插入語句，t1表的主鍵列是id，然后t2表顯示的給主鍵列進行了賦值。

insert into t1
(id,name)
select id,name from t2;

Mixed-mode inserts

混合插入，針對的是 使用了“simple insert”的插入，但是插入的過程中有的行顯示的賦值了主鍵列，有的沒有賦值。

比如：

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

還有一種是之前"simple insert"不包括的INSERT ... ON DUPLICATE KEY UPDATE。這種類型最壞的情況就是，插入后面跟一個更新，導(dǎo)致你分配的自增長值可能會被使用，也可能沒有用處。

看到這樣就真的很難受了，搞那么多花樣。

然后介紹了三種不同的插入模式，通過變量innodb_autoinc_lock_mode進行控制

• innodb_autoinc_lock_mode = 0` (“traditional” lock mode)

傳統(tǒng)的插入模式，這種自增的插入模式就是會在statement執(zhí)行的時候加上一個auto_inc的表級鎖，能夠很好的保證并發(fā)插入的主鍵自增的連續(xù)性。它是每一個statement生成一次自增值，然后在語句運行結(jié)束后就會釋放鎖，而不是等到事務(wù)的結(jié)束。并且在主從復(fù)制（statement based replication）的過程中，也能很好的保證從數(shù)據(jù)和主數(shù)據(jù)的主鍵自增的一致性。不過因此也會在并發(fā)事務(wù)插入的場景下，帶來并發(fā)和伸縮能力的限制。

• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

這個是默認的加鎖模式，它可以很好的平衡主鍵值的連續(xù)性與并發(fā)能力的限制。在"simple insert"的場景下，consecutive模式會使用輕量級的mutex互斥信號量來分配連續(xù)的自增主鍵值，它是在語句的開始階段一次性將整個statement的自增值分配好,而不使用AUTO-INC的表級鎖，達到了提高并發(fā)性能的目的。而在"bulk insert"的場景下還是保持和傳統(tǒng)的鎖模式一致，使用AUTO-INC表鎖。如果是別的事務(wù)已經(jīng)獲取了AUTO-INC鎖，那么當前事務(wù)無論是否"simple insert"都需要等待鎖的釋放。

優(yōu)點：能夠達到和traditional模式一樣的自增連續(xù)性，同時保證了性能。但是有一種情況例外：mixed-mode inserts

• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

這種模式的鎖是性能最好的，它是完全基于互斥信號量的方式來自增主鍵。并發(fā)的插入可能都不會連續(xù)。在基于語句的主從復(fù)制場景下，以及數(shù)據(jù)恢復(fù)下都不是安全的。因為自增的主鍵值可能完全不一樣了。

不同的自增鎖模式帶來的可能結(jié)果，可以參考MYSQL手冊。InnoDB AUTO_INCREMENT Lock Mode Usage Implications

不同SQL語句的加鎖情況

加鎖這個部分推薦一個大佬的PDF，有很好的圖文講解。是我從0到1理解加鎖最清晰的就是這個資料了。讀完之后 再去看手冊的加鎖會更加熟悉

MySQL 加鎖處理分析.pdf

select ... from

is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE。select 基本查詢會使用快照讀，不會涉及到任何的加鎖，除非使用了"串行化"的隔離級別，但是基本我們也不會使用這個隔離級別。所以可以簡單的認為普通的讀，無鎖的干擾。

select.. for update 和 select ... lock in share mode

這兩種讀都會設(shè)置next key lock 到所有掃描的記錄上，除非是使用了唯一索引，并且精確匹配時才使用record lock。for update 使用的是exclusive Lock 然后 share Mode 使用的是 shared lock。

update .... Where... 和 delete from ... where...

這兩種也是使用了exclusive的next key lock 除非是唯一索引 并且精確匹配則使用record lock。

insert 和insert ... duplicate key on update

insert的話需要先獲取一個insert intention lock 之后再獲取一個record lock 即可。插入之間不同的記錄不會阻塞。如果發(fā)生了duplicate key error 的主鍵沖突錯誤，會在主鍵索引上加 shared lock。這里也可能會導(dǎo)致死鎖的發(fā)生。而duplicate key on update 和普通的插入基本一樣，只不過在發(fā)生主鍵沖突時，它會使用一個exclusive lock。如果是主鍵沖突 就是 exclusive record lock 如果是 唯一索引沖突，那就是exclusive的next key lock了。

Replace

和insert是一樣的加鎖情況

鎖之間的兼容性關(guān)系表

shared和exclusive：行代表持有的鎖，列代表要請求的鎖

intention shared Lock 和 intention exclusive Lock：行代表持有的鎖，列代表要請求的鎖

死鎖發(fā)現(xiàn)

死鎖檢測的開啟

死鎖的檢測是通過 innodb_deadlock_detect variable 來控制的。如果不開啟，則MYSQL使用 innodb_lock_wait_timeout 去回滾事務(wù)，防止死鎖的發(fā)生。

查看最后一次死鎖的情況，可以使用 SHOW ENGINE INNODB STATUS語句。如果死鎖發(fā)生太頻繁，開啟 innodb_print_all_deadlocks 看到全部的死鎖日志。

最后一次死鎖的日志（SHOW ENGINE INNODB STATUS）類似如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-07-28 23:00:10 0x7f4725da9700
*** (1) TRANSACTION:
TRANSACTION 4810711, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 44, OS thread handle 139943553947392, query id 255173 172.18.0.1 root update
insert into bank_account_tab_00000000
    (account_number, `name`, gender,create_time)
    values
      
      ('5683761150', 'harris_NEW_DDDD', 'F', '2021-07-28 23:00:10.556'), ('5683799390', 'sad', 'M', '2021-07-28 23:00:10.556')
     
    on duplicate key update `name` = values(`name`),gender=values(gender)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2103 page no 11 n bits 344 index PRIMARY of table `harris`.`bank_account_tab_00000000` trx id 4810711 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 4810710, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 42, OS thread handle 139943554488064, query id 255171 172.18.0.1 root update
insert into bank_account_tab_00000000
    (account_number, `name`, gender,create_time)
    values
      
      ('5685979070', 'KKKK', 'M', '2021-07-28 23:00:10.542'), ('5686017310', 'KKKK', 'M', '2021-07-28 23:00:10.542')
     
    on duplicate key update `name` = values(`name`),gender=values(gender)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2103 page no 11 n bits 344 index PRIMARY of table `harris`.`bank_account_tab_00000000` trx id 4810710 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2103 page no 11 n bits 344 index PRIMARY of table `harris`.`bank_account_tab_00000000` trx id 4810710 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

死鎖日志分析

這里我拿了我自己遇到過的一次死鎖的情況作為例子來分析，為什么會發(fā)生死鎖。這個死鎖的日志是我開啟了innodb_print_all_deadlocks，然后存儲到MYSQL的error日志中的一次死鎖記錄。通過show engine InnoDB status 也可以。

日志的開頭，標記著一次死鎖的發(fā)現(xiàn)。deadlock detected 代表 MYSQL 發(fā)現(xiàn)了一次死鎖，并把detail information 記錄在下面。

首先是transaction 1，黃色的部分transaction 1 正在等待的鎖。鎖的space page 以及是什么索引都標記了，是space id 為2111 頁數(shù)為11的一個主鍵索引的記錄鎖。而事務(wù)id 5536910 lock mode 為 X的一個插入意向鎖正在等待。我們大概可以知道這個事務(wù)打算要插入數(shù)據(jù)了，所以會先請求插入意向鎖。鎖的位置在 supremum，這個是MYSQL的一個特殊標記。標記著最大的一個記錄Supremum Records

接著我們看transaction2，同樣黃色的部分，有兩塊。一個是transaction 持有的鎖 一個是它正在請求的鎖。持有的鎖是一個排他記錄鎖，請求也是請求這個記錄的插入意向鎖。

綜上 我們可以得出結(jié)論，transaction1 在等待transaction2的supremum記錄鎖，transaction持有這個鎖，同時也在請求這個supremum的鎖。造成了死鎖，循環(huán)等待的條件。

最后的紅色部分 MYSQL 選擇了回滾transaction1。

死鎖日志

造成這次死鎖的原因是：我的猜想是，兩個語句都是在批量插入，事務(wù)2 先插入了一條記錄，此時它會獲取supremum的鎖，這個時候事務(wù)1 也過來插入則要等待鎖的釋放。事務(wù)2的第二條語句開始插入也進入等待階段。造成了事務(wù)2自己無法釋放鎖。

所以建議是盡量不要使用insert on duplicate key 因為它是性能比較差的一種語句。插入發(fā)生了主鍵錯誤時，會有next key lock的鎖。

減少死鎖發(fā)生的MYSQL手冊推薦實踐

1. 發(fā)生了死鎖，可以通過 SHOW ENGINE INNODB STATUS 去發(fā)現(xiàn)最近的一次死鎖情況。不要慌
2. 保持事務(wù)執(zhí)行時間短，同時執(zhí)行的語句小。這樣可以減少事務(wù)的發(fā)生碰撞
3. 盡快提交事務(wù)，也是為了減少事務(wù)碰撞
4. 如果使用了當前讀(locking read) 可以將隔離級別設(shè)置成RC

資源推薦

1. 推薦何登成的GitHub中的database部分，何老師是數(shù)據(jù)庫的專家。這個database部分，包含了死鎖、索引以及各種內(nèi)部原理的分析。并且鎖這一塊我自己看過，圖文并茂，對鎖的初步理解非常有幫助。GitHub鏈接
2. 《高性能MYSQL》第三版 動物書 比較出名的講解MYSQL的系統(tǒng)的書。
3. MYSQL 的官方手冊，對大部分的細節(jié)都有詳細的描述。這篇文章的內(nèi)容，都在這個章節(jié)InnoDB Locking and Transaction Model