今日的深入理解MySQL鎖定類型與加鎖原理

相關免費學習推薦：mysql教學

前言

• MySQL索引底層資料結構與演算法
• MySQL效能最佳化原理-前篇
• MySQL效能最佳化-實作篇1
• MySQL效能最佳化-實作篇2
##MySQL鎖定與事物隔離等級

依照鎖定的粒度劃分：行鎖定、表鎖定、頁鎖定
1. 依照鎖定的使用方式分割：共享鎖、排它鎖（悲觀鎖的一種實現）
2. 還有兩種思想上的鎖：悲觀鎖、樂觀鎖。
3. InnoDB中有幾種行級鎖定類型：Record Lock、Gap Lock、Next-key Lock
4. Record Lock：在索引記錄上加鎖
5. Gap Lock：間隙鎖
6. Next-key Lock：Record Lock Gap Lock

資源開銷比行鎖少，不會出現死鎖的情況，但是發生鎖衝突的機率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支援表級鎖，但是InnoDB預設的是行級鎖。

共享鎖定用法：

LOCK TABLE table_name [ AS alias_name ] READ复制代码

排它鎖定用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码

解鎖用法：

unlock tables;复制代码

行級鎖定能大幅減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。有可能會出現死鎖的情況。 行級鎖定依使用方式分為共享鎖和排他鎖。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';复制代码

update user set age = 10 where id > 49;复制代码

共享鎖定/排他鎖定

共享鎖定(Share Lock)

共享鎖定又稱為讀鎖，是讀取操作所建立的鎖定。其他使用者可以並發讀取數據，但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。

如果交易T對資料A加上共享鎖定後，則其他交易只能對A再加共享鎖，不能加排他鎖。獲準共享鎖的事務只能讀數據，不能修改數據。

用法

SELECT ... LOCK IN SHARE MODE;

在查詢語句後面增加LOCK IN SHARE MODE#， Mysql會對查詢結果中的每行都加共享鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他執行緒也可以讀取使用了共享鎖的表，而這些執行緒讀取的是同一個版本的資料。

排他鎖（eXclusive Lock）

排他鎖又稱寫鎖，如果事務T對資料A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲準排他鎖的事務既能讀取數據，又能修改數據。

用法

SELECT ... FOR UPDATE;

#在查詢語句後面增加FOR UPDATE，Mysql就會對查詢結果中的每行都加排他鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請排他鎖，否則會被阻塞。

樂觀鎖定和悲觀鎖定

在資料庫的鎖定機制中介紹過，資料庫管理系統（DBMS）中的並發控制的任務是確保在多個事務同時存取資料庫中同一資料時不會破壞事務的隔離性和統一性以及資料庫的統一性。

樂觀並發控制(樂觀鎖)和悲觀並發控制（悲觀鎖）是並發控制主要採用的技術手段。

無論是悲觀鎖或樂觀鎖，都是人們定義出來的概念，可以認為是一種想法。其實不只是關係型資料庫系統中有樂觀鎖和悲觀鎖的概念，像memcache、hibernate、tair等都有類似的概念。

針對於不同的業務場景，應該選用不同的並發控制方式。所以，不要把樂觀並發控制和悲觀並發控制狹義的理解為DBMS中的概念，更不要把他們和資料中提供的鎖機制（行鎖、表鎖、排他鎖、共享鎖）混為一談。其實，在DBMS中，悲觀鎖定正是利用資料庫本身所提供的鎖定機制來實現的。

悲觀鎖定

在關係資料庫管理系統裡，悲觀並發控制（又稱“悲觀鎖”，Pessimistic Concurrency Control，縮寫“PCC”）是一種並發控制的方法。它可以阻止一個事務以影響其他使用者的方式來修改資料。如果一個事務執行的操作對某行資料套用了鎖，那就只有當這個事務把鎖釋放，其他事務才能夠執行與該鎖衝突的操作。悲觀並發控制主要用於資料爭用激烈的環境，以及發生並發衝突時使用鎖定保護資料的成本要低於回溯事務的成本的環境。

悲觀鎖，如其名，它指的是對資料被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度(悲觀)，因此，在整個資料處理過程中，將資料處於鎖定狀態。悲觀鎖的實現，往往依賴資料庫提供的鎖機制（也只有資料庫層提供的鎖機制才能真正保證資料存取的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改資料）

悲觀鎖定的具體流程

• 在對任意記錄進行修改前，先嘗試為該記錄加上排他鎖定（exclusive locking）；
• #如果加鎖失敗，表示該記錄正在修改，那麼當前查詢可能要等待或拋出異常。具體回應方式由開發者根據實際需求決定；
• 如果成功加鎖，那麼就可以對記錄做修改，事務完成後就會解鎖了。
• 其間如果有其他對該記錄做修改或加排他鎖的操作，都會等待我們解鎖或直接拋出例外。

悲觀鎖的優點和不足

悲觀鎖定實際上是採取了「先取鎖在存取」的策略，為資料的處理安全提供了保證，但是在效率方面，由於額外的加鎖機制產生了額外的開銷，並且增加了死鎖的機會。並且降低了並發性；當一個事物所以一行資料的時候，其他事物必須等待該交易提交之後，才能操作這行資料。

樂觀鎖

在關聯式資料庫管理系統裡，樂觀並發控制（又稱為“樂觀鎖”，Optimistic Concurrency Control，縮寫“OCC”）是一種並發控制的方法。它假設多用戶並發的事務在處理時不會彼此互相影響，各事務能夠在不產生鎖的情況下處理各自影響的那部分資料。在提交資料更新之前，每個事務會先檢查在該事務讀取資料後，有沒有其他事務又修改了該資料。如果其他事務有更新的話，正在提交的交易會進行回滾。

樂觀鎖（ Optimistic Locking ） 相對悲觀鎖而言，樂觀鎖假設認為資料一般情況下不會造成衝突，所以在資料提交更新的時候，才會正式對資料的衝突與否進行檢測，如果發現衝突了，則讓傳回使用者錯誤的訊息，讓使用者決定如何做。

相對於悲觀鎖，在對資料庫進行處理的時候，樂觀鎖並不會使用資料庫提供的鎖定機制。一般的實現樂觀鎖的方式就是記錄資料版本。

資料版本,為資料增加的一個版本標識。當讀取資料時，將版本標識的值一同讀出，資料每更新一次，同時對版本標識進行更新。當我們提交更新的時候，判斷資料庫表對應記錄的當前版本資訊與第一次取出的版本標識進行比對，如果資料庫表目前版本號與第一次取出的版本標識值相等，則予以更新，否則認為是過期數據。

樂觀鎖的優點和不足

樂觀並發控制相信事務之間的資料競爭(data race)的機率是比較小的，因此盡可能直接做下去，直到提交的時候才去鎖定，所以不會產生任何鎖和死鎖。但如果直接簡單這麼做，還是有可能會遇到不可預期的結果，例如兩個事務都讀取了資料庫的某一行，經過修改以後寫回資料庫，這時就遇到了問題。

意向共享鎖定/意向排他鎖定

由於表鎖定和行鎖定雖然鎖定範圍不同，但是會互相衝突。所以當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效率的方式，MySQL 引入了意向鎖，來偵測表鎖和行鎖的衝突。

意向鎖也是表格級鎖，也可分為讀取意向鎖（IS 鎖）和寫入意向鎖（IX 鎖）。當交易要在記錄上加上讀鎖或寫鎖時，要先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了，只要看下表上是否有意向鎖就行了。

意向鎖之間是不會產生衝突的，也不和AUTO_INC 表鎖衝突，它只會阻塞表級讀鎖或表級寫鎖，另外，意向鎖也不會和行鎖衝突，行鎖只會和行鎖衝突。

意向鎖定是InnoDB自動加總的，不需要使用者乾預。

對於insert、update、delete，InnoDB會自動將涉及的資料加上排他鎖（X）；

對於一般的Select語句，InnoDB不會加任何鎖，事務可以透過以下語句給予顯示加上共享鎖或排他鎖。

意向共享鎖（Intention Shared Lock）

意向共享鎖（IS）：表示交易準備好給資料行加入共享鎖，也就是說一個資料行加共享鎖前必須先取得表格的IS鎖

意向排他鎖（Exclusive Lock）

意向排他鎖（IX）：類似上面，表示交易準備給資料行加入排他鎖，說明交易在一個資料行加排他鎖前必須先取得該表的IX鎖。

記錄鎖定（Record Lock）

記錄鎖定是最簡單的行鎖，並沒有什麼好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或 name = 'Tom' 這筆記錄。

當 SQL 語句無法使用​​索引時，會進行全表掃描，這個時候 MySQL 會為整張表的所有資料行加上記錄鎖，再由 MySQL Server 層進行篩選。但是，在 MySQL Server 層進行過濾的時候，如果發現不符合 WHERE 條件，會釋放對應記錄的鎖定。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每筆記錄的加鎖操作還是不能省略的。

所以更新操作必須要根據索引進行操作，沒有索引時，不僅會消耗大量的鎖定資源，增加資料庫的開銷，還會極大的降低了資料庫的並發效能。

間隙鎖定（Gap Lock）

當我們使用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但不存在的記錄，InnoDB 也會對這個「間隙」加鎖，這種鎖定機制就是所謂的間隙鎖。

间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。

要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog

 show variables like 'innodb_locks_unsafe_for_binlog';复制代码

innodb_locks_unsafe_for_binlog：默认

值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。

# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1复制代码

案例1：唯一索引的间隙锁

测试环境：

MySQL5.7，InnoDB，默认的隔离级别（RR）

示例表：

CREATE TABLE `my_gap` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码

在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

只使用记录锁（行锁），不会产生间隙锁

/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。

产生间隙锁

恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：

/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。

恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？

/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
2. 对于查找某一范围内的查询语句，会产生间隙锁。

案例2：普通索引的间隙锁

示例表：id 是主键，在 number 上，建立了一个普通索引。

# 注意：number 不是唯一值CREATE TABLE `my_gap1` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码

在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

测试1

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码

我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：

这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

测试2

我们再进行以下测试，这里将数据还原成初始化那样

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码

查看表中的数据；

这里有一个奇怪的现象：

• 事务3 添加 id = 6，number = 8 的数据，阻塞了；
• 事务4 添加 id = 8，number = 8 的数据，正常执行了；
• 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。

这是为什么？我们来看看下面的图：

从图中库看出，当 number 相同时，会根据主键 id 来排序

1. 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
2. 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
3. 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。

临键锁（Next-key Locks）

临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

插入意向锁的作用：

1. 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
2. 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
3. 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。

自增锁（Auto-inc Locks）

AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

自增操作

使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：

• auto_increment_offset 表示起始数字
• auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();复制代码

自增锁

首先insert大致上可以分成三类：

1. simple insert 如insert into t(name) values('test')
2. bulk insert 如load data | insert into ... select .... from ....
3. mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：

• 0 ：traditonal (每次都会产生表锁)
• 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
• 2 ：interleaved （不会锁表，来一个处理一个，并发最高）

  MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。

 show variables like 'innodb_autoinc_lock_mode';复制代码

traditonal

innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。

• 它提供来一个向后兼容的能力
• 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
• 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
• 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。

consecutive

innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。

• 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
• 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。

interleaved

innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。

• 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

總結

InnoDB鎖定的特性

1. 在不透過索引條件查詢的時候，InnoDB使用的確實是表鎖定！
2. 由於 MySQL 的行鎖是針對索引加的鎖,不是針對記錄加的鎖,所以雖然是訪問不同行 的記錄,但是如果是使用相同的索引鍵,是會出現鎖衝突的。
3. 當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行,另外,不論是使用主鍵索引、唯一索引或普通索引,InnoDB 都會使用行鎖來對數據加鎖。
4. 即便在條件中使用了索引欄位,但是否使用索引來檢索資料是由MySQL 透過判斷不同執行計劃的代價來決定的,如果MySQL 認為全表掃效率更高,比如對一些很小的表,它就不會使用索引,這種情況下InnoDB 將使用表鎖,而不是行鎖。因此,在分析鎖定衝突時, 別忘了檢查 SQL 的執行計劃（explain查看）,以確認是否真正使用了索引。

鎖定模式

鎖的模式有：讀取意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)。

不同模式鎖定的相容矩陣

• #AI
• 相容

#相容

其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對插入意向鎖做個總結，如下：

• 插入意向鎖不影響其他交易加上其他任何鎖。也就是說，一個事務已經取得了插入意向鎖，對其他事務是沒有任何影響的；
• 插入意向鎖與間隙鎖和 Next-key 鎖定衝突。也就是說，一個事務想要取得插入意向鎖，如果有其他事務已經加了間隙鎖或 Next-key 鎖，則會阻塞。

其他類型的鎖的規則較為簡單：

• 間隙鎖定不和其他鎖定（不包括插入意圖鎖定）衝突；

• #記錄鎖定和記錄鎖定衝突，Next-key 鎖定和Next-key 鎖定衝突，記錄鎖定和Next-key 鎖定衝突；

以上是今日的深入理解MySQL鎖定類型與加鎖原理的詳細內容。更多資訊請關注PHP中文網其他相關文章！