mysql innodb locks_MySql InnoDB中的锁研究

MySql InnoDB中的锁研究

1.InnoDB中有哪些锁

1. 共享和排他(独占)锁(Shared and Exclusive Locks)

InnoDB实现标准的行级锁定，其中有两种类型的锁， shared(S)锁和exclusive(X)锁。

共享(S)锁允许持有锁的事务读取行

独占(X)锁允许持有锁的事务更新或删除行。

共享锁与独占锁的授予逻辑如下

1. 如果事务T1在行上持有一个shared(S)锁r，那么来自某个不同事务T2 的对行锁的请求r将按如下方式处理：

事务T2请求 S锁可以立即被授予。其结果是，T1与T2 均持有S的锁r。

T2请求一个 X锁不能立即授予。

2. 如果事务T1在行上持有exclusive(X)锁r，则不能立即授予来自某个不同事务T2的锁定任何类型的锁的请求r。事务T2必须等待事务T1释放其对行的锁定r。

2. 意图锁定(Intention Locks)

InnoDB支持多粒度锁定，允许行锁和表锁共存。例如，类似与LOCK TABLES ... WRITE的语句在指定表上进行独占锁定(X锁定)，

为了实现多粒度级别的锁定，InnoDB使用意图锁定。意图锁是表级锁，它指示事务稍后对表中的行进行操作时所需的锁(共享或独占)。

意图锁有两种类型:

意图共享锁(IS)指示一个事务打算为表中的某个行设置一个共享锁。

意图独占锁(IX)指示一个事务打算为表中的某个行设置一个独占锁

例如，SELECT ... FOR SHARE设置IS锁定并 SELECT ... FOR UPDATE设置IX锁定。

意图锁定协议如下:

在事务可以获取表中某行的共享锁之前，它必须首先获取表上的IS锁或更强的锁(IX)。

在事务可以获取表中某行的独占锁之前，它必须首先获取表上的IX锁。

表级锁定类型兼容性总结在以下矩阵中。

已获取的锁类型

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

如果事务请求的锁与现有锁兼容，则授予锁，但如果有冲突则不会。事务将会等待，直到释放冲突的现有锁。

如果锁定请求与现有锁因为会产生死锁而冲突，则会抛出死锁错误。

意图锁定不会阻止除了对完整表请求之外的任何内容(例如，LOCK TABLES ... WRITE)。

意图锁定的主要目的是显示某人正在锁定行，或者要锁定表中的行。

意图锁定的事务数据SHOW ENGINE INNODB STATUS与 InnoDB监视器 输出中的以下内容类似：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

3. 记录锁定(Record Locks)

记录锁定是对索引记录的锁定。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;为t表中c1=10的索引记录行上设置独占锁

可以防止其他事务对t.c1=10的行进行插入，更新或删除行

即使定义了没有索引的表，记录锁也始终锁定索引记录。对于此类情况，InnoDB创建隐藏的聚簇索引并使用此索引进行记录锁定

记录锁的事务数据SHOW ENGINE INNODB STATUS与 InnoDB监视器 输出中的以下内容类似：

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, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

4.间隙锁定(Gap Locks)

间隙锁定是锁定索引记录之间的间隙，或锁定在第一个或最后一个索引记录之前的间隙。

例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;将锁定t表中c1列的索引10到20之间的记录及其中的间隙

阻止其他事务将值15插入列t.c1，无论列中是否已存在任何此类值，因为该范围中所有现有值之间的间隙都已锁定。

间隙可能跨越单个索引值，多个索引值，甚至可能为空。

差距锁是性能和并发之间权衡的一部分，只支持部分隔离级别(RR)

使用唯一索引搜索唯一行进行锁定的语句不需要间隙锁定。(不包括搜索条件仅为组合唯一索引中的部分列的情况)

例如，如果id列具有唯一索引，则以下语句仅对id值为100的行的索引记录进行锁定(记录锁定)，其他会话是否在前一个间隙中插入行无关紧要：

SELECT * FROM child WHERE id = 100;

如果id列没有索引或具有非唯一索引，则该语句还会锁定记录前的一个间隙。

此处值得注意的是，间隙锁是可冲突的。例如，事务A可以在间隙上保持共享间隙锁定(间隙S锁定)，而事务B在同一间隙上保持独占间隙锁定(间隙X锁定)。

允许冲突间隙锁定的原因是，如果从索引中清除记录，不同事务保留在记录上的间隙锁定是必须合并的。

间隙锁定在InnoDB中是“purely inhibitive”，这意味着它们的唯一目的是防止其他事务插入间隙。差距锁可以共存。

一个事务占用的间隙锁定不会阻止另一个事务在同一个间隙上进行间隙锁定。共享和独占间隙锁之间没有区别。它们彼此不冲突，它们执行相同的功能。

可以明确禁用间隙锁定。如果将事务隔离级别更改为，则会发生这种情况 READ COMMITTED。在这些情况下，对于搜索和索引扫描禁用间隙锁定，并且仅用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别还有其他影响 。在MySQL评估WHERE条件后，将释放非匹配行的记录锁。对于 UPDATE语句，InnoDB 执行“ 半一致 ”读取，以便将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否符合WHERE 条件UPDATE。

间隙锁的事务数据在SHOW ENGINE INNODB STATUS与 InnoDB监视器 输出中的以下内容类似：

RECORD LOCKS space id 273 page no 5 n bits 72 index idx_client_id of table `test`.`t` trx id 567156 lock_mode X locks gap before rec

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 2; hex 6132; asc a2;;

1: len 8; hex 8000000000000002; asc ;;

5. 下一键锁(Next-Key Locks)

下一键锁定是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。

InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排它锁(扫描过的都会加)。因此，行级锁实际上是索引记录锁。

索引记录上的下一键锁定也会影响该索引记录之前的间隙。也就是说，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。

如果一个会话R在索引中具有共享或独占锁定记录 ，则另一个会话不能R在索引顺序之前的间隙中插入新索引记录 。

假设索引包含值10,11,13和20.此索引的上的下一个键锁定可能包括以下间隔，其中圆括号表示排除间隔端点，方括号表示包含端点：

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

对于最后一个间隔，InnoDB将采用上确界(supremum)的方式来描述该下一键锁，锁定数据库中最后一个索引记录到上确界之间的间隙，即此下一键锁定仅锁定最大索引值之后的间隙。

默认情况下，InnoDB以 REPEATABLE READ事务隔离级别运行。在这种情况下，** InnoDB使用下一键锁进行搜索和索引扫描 **

下一键锁的事务数据在SHOW ENGINE INNODB STATUS与 InnoDB监视器 输出中的以下内容类似：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10080 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

6. 插入意向锁(Insert Intention Locks)

插入意图锁定是一种由INSERT插入之前的设置的 间隙锁定 。当多个事物尝试在同一个间隙中插入不同位置的数据时，使用插入意向锁来表示它们想要插入数据的意图，不需要等待彼此。

假设存在值为4和7的索引记录。分别尝试插入值5和6的单独事务，

每个事务都会使用插入意向锁来锁定4-7之间的间隙，然后再去获取对应行上的索引记录X锁，但是不会互相阻塞，两个事务插入的行是不冲突的

以下示例演示了在获取插入记录的独占锁之前采用插入意图锁定的事务。该示例涉及两个客户端，A和B.

客户端A创建一个包含两个索引记录(90和102)的表，然后启动一个事务，该事务对ID大于100的索引记录放置独占锁，也包括记录102之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

+-----+

| id |

+-----+

| 102 |

+-----+

客户端B开始事务以将记录插入间隙。该事务在等待获取独占锁时采用插入意图锁。

mysql> START TRANSACTION;

mysql> INSERT INTO child (id) VALUES (101);

插入意图锁的事务数据SHOW ENGINE INNODB STATUS与 InnoDB监视器 输出中的以下内容类似 ：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`

trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000066; asc f;;

1: len 6; hex 000000002215; asc " ;;

2: len 7; hex 9000000172011c; asc r ;;...

7. AUTO-INC锁定

AUTO-INC锁是一个特殊的表级锁，当使用AUTO_INCREMENT自增列进行插入的情况下需要获取该锁。

如果一个事务正在获取AUTO_INCREMENT自增列的值时，任何其他事务插入数据时必须等待，以便第一个事务插入的行拿到的是连续的键值。

2.DML语句会采用哪些锁

SELECT

SELECT ... FROM ...使用快照读，在这种情况下是无锁的。当使用SERIALIZABLE级别时，该语句将会使用下一键S锁来锁定扫描过的行，

如果使用唯一索引来搜索的话，则只需要锁定索引记录的那一行

当SELECT ... FOR UPDATE和 SELECT ... FOR SHARE使用唯一索引来搜索时，InnoDB先锁定搜索过的行，当搜索完毕后，再释放不满足条件的行上的锁，

如果查询执行过程中，获取的的结果集和其原始数据源之间的关系会丢失的情况下，可能不会立即解锁不满足条件的行，

比如，使用UNION时，扫描过的行会在评估是否满足条件之前插入临时表，临时表与源数据之间的关系会丢失，此时会在查询结束之后才会释放锁

对于锁定读取(SELECT ... FOR UPDATE和 SELECT ... FOR SHARE) 使用唯一索引进行搜索时，仅锁定找到的索引记录，不锁定间隙,使用非唯一索引或其他搜索条件时，锁定扫描过的索引范围，使用间隙锁或下一键锁来阻止其他事物对锁定索引范围内的间隙上的插入操作

UPDATE

UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个独占的下一键锁定。对于使用唯一索引搜索唯一行的语句，只需要索引记录锁定

当UPDATE修改一个聚集索引记录，对受影响的二级索引将会采用隐含的锁,在插入辅助索引前进行重复性检查时，以及插入新的辅助索引记录时，还会在受影响的辅助索引上加上共享锁定

INSERT

INSERT在插入的行上设置独占锁。此锁是索引记录锁，而不是下一键锁(即没有间隙锁)，并且不会阻止其他会话在插入行之前插入间隙

但是在插入行之前，会使用插入意向锁来锁定需要插入的间隙

如果发生了重复键错误，则会在对应的索引记录上加上共享锁，这非常容易导致死锁出现

例如CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

事务1：INSERT INTO t1 VALUES(1);获取到独占锁

事务2：INSERT INTO t1 VALUES(1); 发生重复键错误，获取到共享锁

事务3：INSERT INTO t1 VALUES(1);发生重复键错误，获取到共享锁

事务1：ROLLBACK;

此时就会发生死锁，事务2和事务3都持有了共享锁，均在等待独占锁，而都因为对方持有了共享锁无法获取独占锁，死锁发生

INSERT ... ON DUPLICATE KEY UPDATE不同与INSERT之处在于，当发生重复键错误时，在要更新的行上放置独占锁而不是共享锁。对重复的主键值采用独占索引记录锁定。对于重复的唯一索引键值，采用独占的下一键锁定

DELETE

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个独占的下一键锁定。对于使用唯一索引搜索唯一行的语句，只需要索引记录锁定

REPLACE

REPLACE INTO 在InnoDB中使用如下方式来执行

尝试直接INSERT数据

当发生唯一索引冲突时

执行DELETE语句

执行INSERT语句

由于REPLACE INTO 特殊的逻辑,所以其在数据行上加的锁也是不一样的

已经存在记录

replace into 与insert+delete+insert类似，不同的地方在于设置的锁均是独占锁

即该情况下会获取本记录的索引记录X锁，本记录前的间隙X锁，本记录的下一键X锁，插入位置的下一个索引的下一键X锁

记录不存在

replace into在这种情况下仅会锁定本记录的间隙锁，插入位置的下一个索引记录前的间隙X锁(当插入的记录位于索引尾部时，获取上确界supremum的下一键X锁)

一些共性

对于锁定读取(SELECT ... FOR UPDATE和 SELECT ... FOR SHARE),UPDATE和DELETE语句，取决是否使用了唯一索引

使用唯一索引进行搜索时，仅锁定找到的索引记录，不锁定间隙

使用非唯一索引或其他搜索条件时，锁定扫描过的索引范围，使用间隙锁或下一键锁来阻止其他事物对锁定索引范围内的间隙上的插入操作

锁定读，UPDATE,DELETE语句会为sql扫描过的每一个索引记录上加锁，MySql不记录WHERE条件，只知道自己扫描过哪些索引记录,该锁通常是下一键锁，这会阻止对索引记录之前间隙的INSERT操作

如果MySql在扫描过程中使用到了二级索引，并且要设置的锁还是独占锁的话，MySql还会锁定其聚集索引

如果没有合适的索引的话，MySql将会锁表，阻止其他用户的插入操作

在某些事务隔离级别下，SELECT ... FOR UPDATE将会阻塞SELECT ... FOR SHARE读取(猜测是在SERIALIZABLE),RR下使用一致性读取，读取时忽略记录上的任何锁定

其他语句

INSERT INTO T SELECT ... FROM S WHERE ... 将在T表的索引记录上设置独占的索引记录锁(不锁间隙)，在S表的索引记录上设置共享的下一键锁定，因为当使用binlog回滚时，每个sql语句都必须按照它之前的方式执行，当隔离级别为RC时，使用一致性读取S表(不加锁)

CREATE TABLE ... SELECT ...与INSERT...SELECT...表现一致

REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)时，在S表上的锁与上述一致

自增列的锁

当初始化建表时设置的AUTO_INCREMENT列时，InnoDB在自增列的末尾设置一个独占锁，在访问自增计数器时，InnoDB使用特殊的AUTO-INC表级锁模式来锁定，该锁定仅持续到语句结束而不是事务结束，当AUTO-INC锁定时，其他事务无法插入数据,获取已经初始化好的AUTO_INCREMENT列的值不会设置任何锁

外键检测

当定义了外键时，任何一种需要检查外键约束的 insert, update,或delete语句都会在查看过的记录上加上共享索引记录锁，无视约束是否失败

表级锁

LOCK TABLES设置表级锁，但是是设置在MySql层而不是InnoDB层的，当 innodb_table_locks = 1 (the default) 并且 autocommit = 0时，InnoDB能感知到表级锁，并且MySql能感知到行级锁，死锁检测无法检测到这种情况下的死锁

3.死锁案例分析

基本死锁案例

CREATE TABLE t (i INT) ENGINE = InnoDB;

INSERT INTO t (i) VALUES(1);

事务1：SELECT * FROM t WHERE i = 1 FOR SHARE;获取索引上的共享锁

事务2：DELETE FROM t WHERE i = 1;锁等待，想要获取索引上的独占锁

事务1：DELETE FROM t WHERE i = 1;死锁

事务2先请求了X锁，并在等待事务1释放其S锁，事务1持有的S锁也不能升级成X锁因为事务2先请求了，导致死锁

replace into导致的死锁案例

X锁与X锁的死锁 a1 a2均已存在

CREATE TABLE `client_heart_mapping` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`client_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`last_heart_time` datetime(0) NULL DEFAULT NULL,

`last_expire_time` datetime(0) NULL DEFAULT NULL COMMENT '最后到期时间',

PRIMARY KEY (`id`) USING BTREE,

UNIQUE INDEX `idx_client_id`(`client_id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 102421 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

事务2：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a2',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

获取supremum(插入位置)的下一键X锁，a2的下一键X锁，a2的索引记录锁，a2之前的间隙锁

事务1：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

获取到a1的下一键X锁，a1的记录X锁，a1之前的间隙X锁，需要获取a2之前的间隙(插入位置)X锁(等待事务2释放持有的a2上的下一键X锁)

事务2：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a2',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

死锁发生，事务1等待事务2上释放a2上的下一键X锁,事务2等待事务1释放a1上的下一键X锁

插入意向锁与插入意向锁的死锁 a1,a2均不存在

事务2：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a2',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

持有supremum(插入位置)的下一键X锁,a2记录前的间隙X锁，a2的记录锁

事务1： replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

持有a2前的间隙X锁,在a2前的间隙上设置了插入意向锁(等待)

事务2：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

死锁发生，事务1与事务2均持有a2间隙前的X锁，并都在等待对方释放，以便插入数据

插入意向锁与X锁的死锁 a1,a2均存在

事务1：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

持有a1的下一键X锁，a2的下一键X锁，a1的索引记录X锁，a1前的间隙X锁

事务2：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a2',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

等待a2的下一键X锁

事务1：replace into client_heart_mapping (client_id,last_heart_time,last_expire_time) values('a1',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

死锁发生，事务2想获取a2的下一键X锁，事务1在a2前的间隙上设置插入意向锁，并都在等待对方释放