innodb鎖機制

在mysql當中,關于innodb的鎖類型總共可以分為四種,包含了行鎖和表鎖,分別是基本鎖(Shared Locks：S鎖和排它鎖(Exclusive Locks：X鎖))、意向鎖(intention lock,分為意向共享鎖(IS鎖)和意向排他鎖(IX鎖))、行鎖(record Locks、gap locks、next-key locks、Insert Intention Locks)、自增鎖(auto-inc locks)

共享鎖、意向鎖、排他鎖的兼容矩陣如下:

行鎖的兼容矩陣如下:

本文主要想介紹下間隙鎖的一些相關知識，以及出現問題的一些解決方案

間隙鎖官方解釋

間隙鎖是在索引記錄之間的間隙上的鎖定，或在最后一個索引記錄之前或之后的間隙上的鎖定。

間隙可能跨越單個索引值，多個索引值，甚至為空。

間隙鎖是性能和并發性之間權衡的一部分，并且在一些事務隔離級別中使用。

使用唯一索引鎖定行來搜索唯一行的語句不需要間隙鎖定。(這不包括搜索條件包含多列唯一索引的某些列的情況;在這種情況下，會發生間隙鎖定)。例如，如果id列具有唯一索引，則以下語句會使用id值為100?的行的索引記錄鎖定，其他會話是否在前面的間隙中插入行無關緊要：

SELECT*FROMchildWHEREid=100;

如果id沒有索引或具有非唯一索引，則該語句會鎖定上述間隙。

事務A可以在間隙上持有一個共享間隙鎖(gap S-lock),與此同時，事務B在同一間隙上持有一個排他間隙鎖(gap X-lock)。允許兩個間隙鎖的原因是，如果從索引中清除記錄，則必須合并不同事務對記錄持有的間隙鎖。

間隙鎖在InnoDB中只是一種“?純粹的抑制?",它們只是阻止其他事務插入到當前間隙當中,但是它們不會阻止不同的事務在同一間隙上獲得間隙鎖。因此，共享間隙鎖具有與排他間隙鎖相同的效果。

間隙鎖可以被明確禁用。就是將事務隔離級別更改為READ COMMITTED或啟用?innodb_locks_unsafe_for_binlog?系統變量(現在已被棄用)。在這種情況下，針對搜索和索引掃描禁用間隙鎖，僅用于外鍵約束檢查和重復鍵檢查。

還有使用READ COMMITTED隔離級別或啟用WHERE條件進行評估后，將釋放非匹配行的記錄鎖。對于?UPDATE語句，InnoDB?做一個“?半一致?”的讀取，將最新的提交版本返回給MySQL，以便MySQL可以確定該行是否符合UPDATE的where條件。

可能會出現的問題

當高并發訪問時執行delete操作和insert操作時，如

delete from user u where u.id = 2;?insert into user u values(2);

delete from user u where u.id = 3;?insert into user u values(3);

執行過程中可能會出現亂序，所以當執行delete from user u where u.id = 2和delete from user u where u.id = 3時就會拋出死鎖。這時數據庫會掃描索引，數據庫會向左掃描掃到第一個比給定參數小的值， 向右掃描掃描到第一個比給定參數大的值， 然后以此為界，構建一個區間，最終會鎖住整個區間內的數據，這就是間隙鎖的死鎖，這種情況就得修改代碼邏輯， 存在才刪除，盡量不去刪除不存在的記錄。

解決方案

mysql默認采用的是可重復讀(即repeatable read)隔離級別,并且會以Next-Key Lock的方式(即record lock和gap lock的結合)對數據行進行加鎖，這樣可以有效防止幻讀的發生,但是在平時開發過程中,一些不正確的行為可能會導致間隙鎖鎖定范圍變得很大，線程間互相等待從而導致死鎖,下面將提供一些解決思路供大家參考:

1.將事務隔離級別改為讀提交(即read committed)，binlog格式改為row或mixed,但可能會產生臟讀

2.避免刪除不存在的數據，這樣會導致間隙鎖鎖定范圍變得很大，可能會導致死鎖,一般采用的方法是先讀取數據，如果存在，再進行刪除

3.對于select from where for update操作，一定要保證where條件字段的值一定存在，不然可能會導致間隙鎖鎖定的范圍變得很大，阻塞別的事務，也有可能會導致死鎖

4.將一個較長的事務分解成幾個較小的事務

5.采用序列化(即serializable)隔離級別

6.避免在同一個事務中對同一張表的數據同時進行快照讀和當前讀

總結

在日常的開發過程中,雖然很少會遇到像mysql表死鎖的一些問題，但是了解常用的存儲引擎的一些特性和組成結構還是非常有必要的,便于在遇到問題的時候能夠更快的查出問題并制定出解決方案。另外，在開發的過程中如果遇到一些長事務或者是多個數據插入、更新或刪除操作的時候多考慮一下，采取正確的做法也許就可以避免一些不必要線上問題的發生。

補充點(供排查問題的時候使用)

1. 查看事務隔離級別

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

2. 設置隔離級別

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

例如：set session transaction isolation level read uncommitted;

3. 查看表狀態

show table status like '表名';

4. 查看SQL性能

set profiling=1;

show profiles

或

show profile for query 1;(show profiles查詢出來的id)

5. 查看當前最新事務ID

每開啟一個新事務，記錄當前最新事務的id，可用于后續死鎖分析。

show engine innodb status;

6. 查看事務鎖等待狀態情況

select?*?from information_schema.innodb_locks;

select? *from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

7. 查看innodb狀態(包含最近的死鎖日志)

show engine innodb status;

本文來自網易實踐者社區，經作者姚成授權發布。

總結

以上是生活随笔為你收集整理的mysql 索引间隙锁_关于mysql innodb间隙锁的一些思考的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。