MySQL innodb的锁机制解读

网上有许多关于innodb的锁机制的文章，有许多文章讲述的不明白或者有问题，最近研究了好久，结合网上资料和实践操作，记录一下，供大家参考。如果有不对的地方，请随时留言。

一 Innodb具备的锁种类

1. 表锁（MySQL提供的，跟存储引擎无关）

2. 行锁（Innodb存储引擎实现）

二 Innodb内部实现的锁种类

1. 记录锁

对应Innodb的行锁，记录锁锁的是索引记录，不是具体的数据记录。

2. 间隙锁

锁定索引记录间隙的锁，确保索引记录的间隙不变，间隙锁是针对事务隔离等级是可重复读或以上级别而言的！

例如： create table t1(id int, v1 int,v2 int, primary key(id), key `idx_v1` (`v1`)) engine=innodb;

数据行有以下几行：

idv1v21102313425537741095

 

 

 

 

 

 

间隙锁一般是针对非唯一索引而言的

上面的数据表中v1的索引区间有

(-∞,1)

(1,3)

(3,4)

(4,5)

(5,7)

(7,9)

(9,+∞)

假如更新v1=5的数据行，那么此时会在索引记录idx_v1加上间隙锁，会把5之前的区间锁定，锁定的区间是(4,5)和(5,7)，也就是区间(4,7)，同时找到v1=5的数据行的主键，在该记录上加上记录锁，锁定该行记录。

 

3. 后码锁

记录锁和间隙锁的结合，对于innodb中，更新非唯一索引记录时，会加上后码锁。如果更新记录为空，就不能加记录锁，此时只剩下间隙锁。多条更新语句可能导致不同事务中锁定的索引区间重复，导致插入失败。

例子（事务隔离等级为可重复读，主要看一下后码锁是记录锁和间隙锁的结合）

transaction 1transaction 2BEGIN;BEGIN;update t1 set v2=1 where v1=6;(这句SQL会加上后码锁，但是v1=6的记录不存在，后码锁是记录锁和间隙锁组成的，此时只能加上间隙锁，会锁住idx_v1的索引区间是(5,7))update t1 set v2=2 where v1=7;(这句SQL也是加上后码锁，v1=7的记录存在，后码锁是由记录锁和区间锁组成，首先会使用记录锁，锁定v1=7的主键，即id=7的记录行，同时会使用间隙锁，会锁住idx_v1的索引区间是(5,9))INSERT INTO t1 set id=8, v1=6,v2=2;(v1=6在自己的idx_v1锁定的索引区间(5,7));INSERT INTO t1 set id=9, v1=8,v2=2;(v1=8在自己的idx_v1锁定的索引区间(5,9));锁等待插入成功插入成功rollback;rollback; 

 

 

 

 

 

 

 

 

由此可以看出，一个索引区间是可以被多个间隙锁锁定的，更新不当的时候，会造成死锁。

其中在transaction 2中，如果"INSERT INTO t1 set id=9, v1=8,v2=2;"这条语句换成"INSERT INTO t1 set id=9, v1=6,v2=2;"就会造成死锁，因为两个间隙锁都锁定了(5,7)这个区间。

同时在transaction 2中，执行update语句的时候，已经在id=7的主键索引上加了记录锁，任何在其他事务(例如transaction 1)中尝试更新id=7的行，都会被挂起，直到transaction 2提交或回滚。

三  锁选择

       执行更新类语句，像SELECT ... FOR UPDATE, UPDATE,DELETE语句

　　 1.  如果更新条件没有索引，例如执行"SELECT * FROM t1 where v2=2 for update"，那么此时更新操作会使用表锁。多条更新SQL语句在不同的事务中同时执行，先取得表锁的事务会将其他事务挂起，直到当前事务提交或回滚。

       使用表锁的原因：

       由于更新的数据没有索引，MySQL只能做扫表操作，扫表的时候，要阻止其他任何的更新操作，所以会上升为表锁。

       2. 如果更新条件为索引字段，但并非唯一索引（包括主键），例如执行"SELECT * FROM t1 where v1=1 for update"，那么此时更新会使用后码锁。

　　 使用后码锁的原因：

　　 a)首先要保证在符合条件的记录上加上排他的记录锁，会锁定当前非唯一索引和这些满足条件的记录对应的主键索引；b)还要保证更新的索引记录区间不能插入新数据。

       3. 如果更新条件字段为唯一索引，使用记录锁。

           Innodb会根据唯一索引，找到记录的主键索引，将符合条件的主键索引和唯一索引加上记录锁。

      说明：Innodb的索引结构

      Innodb支持聚簇索引，但是聚簇索引只能是主键索引，并且每张表只能有一个聚簇索引，所谓聚簇索引，就是索引在物理存储上是顺序存放的。主键索引就是聚簇索引，主键索引的叶子节点存放的是记录的物理地址，根据主键索引可以直接访问记录内容。非主键索引在B-tree索引的叶子节点上存放的并不是记录的物理地址，而是主键索引的物理地址。

      当给非唯一索引加上后码锁的时候（例如更新非唯一主键索引对应的记录内容），Innodb会采用后码锁。首先将满足条件的非唯一索引对应的主键索引和满足条件的非唯一索引加上记录锁。然后会给非唯一索引加上间隙锁，将当前非唯一索引对应的索引区间加上间隙锁，禁止在该区间的任何INSERT操作。

四 间隙锁演示

    说明：加后码锁的时候，并未锁住间隙两端的记录，那么两端的记录是可以更新的，但是如果更新记录时会影响到间隙锁，那需要被挂起，等待间隙锁被释放。

 

transaction 3transaction 4BEGIN;BEGIN;SELECT * FROM t1 WHERE v1=5 FOR UPDATE;(加上间隙锁，锁定了idx_v1的索引区间是(4,7)，同时会把满足条件的记录的主键索引上加上行锁)

UPDATE 类操作

UPDATE t1 set v2=2 WHERE v1=4;(OK,不会被挂起，间隙锁锁只锁间隙，而这条更新SQL并未影响idx_v1在区间(4,7)的间隙锁控制范围);

UPDATE t1 set v2=2 WHERE v1=7;(OK,不会被挂起)

UPDATE t1 set v2=2 WHERE v1=6;(OK,不会被挂起)

UPDATE t1 set v1=1 WHERE v1=4;(OK,不会被挂起，更新索引)

UPDATE t1 set v1=5 WHERE v1=4;(由于4,7区间被封锁，这个操作会被挂起)

UPDATE t1 set v1=8 WHERE v1=7;(OK，不会被挂起，更新索引)

UPDATE t1 set v1=1 WHERE v1=9;(OK,不会被挂起，因为条件和目的索引的值都不在封锁的区间)

UPDATE t1 set v1=5 WHERE v1=7;(由于4,7区间被封锁，这个操作会被挂起)

UPDATE t1 set v1=2 WHERE v1=7(这个操作会被挂起)

由上面这两组SQL可以看出来，间隙锁锁住的区间为4,7，当更新这两端的记录的时候，如果不改变区间的值，压根就跟区间索引没关系，那么更新操作就不会被间隙锁挂起。如果更新间隙锁区间的两端的索引值，且更新索引后的区间包含当前锁住的区间，那么可以更新成功。如果更新索引后，不能包含已经锁定的区间，那么更新操作会被挂起。

INSERT 类操作

INSERT INTO t1 set id=11, v1=5, v2=5;(挂起，transaction 3封闭的idx_v1间隙是(4,7)，插入v1=5肯定会被挂起)

INSERT INTO t1 set id=11, v1=8,v2=8(OK,v1不在封锁区间);

主要看一下在封锁区间两端的插入情况

INSERT INTO t1 set id=4,v1=4,v2=2;(v1=4是封锁区间的左侧值，此操作会被挂起)

INSERT INTO t1 set id=0,v1=4,v2=2;(此操作OK，可以执行)

INSERT INTO t1 set id=6, v1=7,v2=2;(v1=7是封锁区间的右侧值，此操作会被挂起)

INSERT INTO t1 set id=8, v1=7,v2=2;(此操作OK，可以执行)

当往封边区间两端插入值的时候，需要看要插入的值的主键是否在封锁区间对应的主键的范围。

具体解释：

idv1v2342774

 

 

 

当插入左边界值时，即插入v1=4的时候，要求主键id的值需要在小于id=3的范围，当数据库中v1=4左侧值有多条记录的时候，插入的id小于其中最大的id即可。

当插入右界值时，即插入v1=7的时候，要求主键id值大于id=7的范围，当数据库中v1=7右侧值有多条记录的时候，插入的id大于其中最小的id即可。

ROLLBACK;

ROLLBACK;

 

Locking read（ SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE），UPDATE以及DELETE语句通常会在他扫描的索引所有范围上加锁，忽略没有用到索引的那部分where语句。
举个例子：

CREATE TABLE `test` (  `id` int(11) NOT NULL DEFAULT '0',  `name` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8select * from test where id > 3 and name <'A' for update;

这条SQL语句的会将所有id>3的记录进行加锁，而不是id>3 and name <'A' 进行加锁，因为name上面没有索引。

如果一个SQL通过二级索引进行扫描，并且在二级索引上设置了一个锁，那么innodb将会在对应的聚簇索引记录上也加上一把锁。

如果一个SQL语句无法通过索引进行Locking read，UPDATE，DELETE，那么MySQL将扫描整个表，表中的每一行都将被锁定（在RC级别，通过semi-consistent read，能够提前释放不符合条件的记录，在RR级别，需要设置innodb_locks_unsafe_for_binlog为1，才能打开semi-consistent read）。在某些场景下，锁也不会立即被释放。例如一个union查询，生成 了一张临时表，导致临时表的行记录和原始表的行记录丢失了联系，只能等待查询执行结束才能释放。

SQL分析

---

1.SELECT ... FROM 是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下，如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

2. SELECT ... FROM ... LOCK IN SHARE MODE 语句在所有索引扫描范围的索引记录上加上共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

3. SELECT ... FROM ... FOR UPDATE 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。这将堵塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。

4. UPDATE ... WHERE ...语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。

5. DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

6. INSERT 语句将在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap 锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

在插入记录之前，将会加上一种叫做 insert intention gap 的 gap 锁。这个 insert intention gap表示他有意向在这个index gap插入记录，如果其他会话在这个index gap中插入的位置不相同，那么将不需要等待。假设存在索引记录4和7，会话A要插入记录5，会话B要插入记录6，每个会话在插入记录之前都需要锁定4和7之间gap，但是他们彼此不会互相堵塞，因为插入的位置不相同。

如果出现了重复键错误，将在重复键上加一个共享锁。如果会话1插入一条记录，没有提交，他会在该记录上加上排他锁，会话2和会话3都尝试插入该重复记录，那么他们都会被堵塞，会话2和会话3将尝试在该记录上加一个共享锁。如果此时会话1回滚，将发生死锁。

例子如下：
表结构：
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Session 1:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

为什么会发生死锁呢？当会话1进行回滚的时候，记录上的排他锁释放了，会话2和会话3都获得了共享锁。然后会话2和会话3都想要获得排他锁，进而发生了死锁。

还有一个类似的例子

Session 1:

START TRANSACTION;DELETE FROM t1 WHERE i = 1;

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

COMMIT;

会话1在该记录上拥有一把排他锁，会话2和会话3都碰到了重复记录，因此都在申请共享锁。当会话1提交之后，会话1释放了排他锁，之后的会话2会话3先后获得了共享锁，此时他们发生了死锁，因为会话2和会话3都无法或者排他锁，因为彼此都占用了该记录的共享锁。

7. INSERT ... ON DUPLICATE KEY UPDATE 和普通的INSERT并不相同。如果碰到重复键值，INSERT ... ON DUPLICATE KEY UPDATE 将在记录上加排他的 next-key锁。

8. REPLACE 在没有碰到重复键值的时候和普通的INSERT是一样的，如果碰到重复键，将在记录上加一个排他的next-key锁。

9. INSERT INTO T SELECT ... FROM S WHERE ... 语句在插入T表的每条记录上加上 index record lock 。如果隔离级别是 READ COMMITTED, 或者启用了 innodb_locks_unsafe_for_binlog 且事务隔离级别不是SERIALIZABLE，那么innodb将通过快照读取表S(no locks)。否则，innodb将在S的记录上加共享的next-key锁。

CREATE TABLE ... SELECT ... 和 INSERT INTO T SELECT ... FROM S WHERE ... 一样，在S上加共享的next-key锁或者进行快照读取（(no locks）

10. REPLACE INTO t SELECT ... FROM s WHERE ... 和 UPDATE t ... WHERE col IN (SELECT ... FROM s ...)中的select 部分将在表s上加共享的next-key锁。

11. 当碰到有自增列的表的时候，innodb在自增列的索引最后面加上一个排他锁，叫AUTO-INC table lock。AUTO-INC table lock会在语句执行完成后进行释放，而不是事务结束。如果AUTO-INC table lock被一个会话占有，那么其他会话将无法在该表中插入数据。innodb可以预先获取sql需要多少自增的大小，而不需要去申请锁，更多设置请参考参数innodb_autoinc_lock_mode.

12.如果一张表的外键约束被启用了，任何在该表上的插入、更新、删除都将需要加共享的 record-level locks来检查是否满足约束。如果约束检查失败，innodb也会加上共享的 record-level locks。

13. lock tables 是用来加表级锁，但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。

锁测试注意点

---

1. 当使用begin开启一个事务的时候，之后的查询并不是获取的begin 命令的时间点快照，而且begin命令之后第一个查询的时间点快照。

CREATE TABLE T2 (id INT,name varchar(10) ,PRIMARY KEY (id)) ENGINE = InnoDB;INSERT INTO T2 VALUES(1,'zhangsan'),(2,'lisi');SESS1:                      SESS2：BEGIN;                            BEGIN;                            INSERT INTO T2 values(3,'wangwu');                            COMMIT;SELECT * FROM T2;+----+----------+| id | name     |+----+----------+|  1 | zhangsan ||  2 | lisi     ||  3 | wangwu   |+----+----------+                          

在可重复读的情况下，为什么会出现这样的情况呢？

原因就是SESS1 没有BEGIN之后开启一个查询，导致SESS1的select * from t2 查询的快照是执行该SQL的快照，而不是BEGIN那个时间点的快照，而此时，SESS2已经提交。

2. 我们来看一个例子：

CREATE TABLE T3 (id INT,name varchar(10) ,PRIMARY KEY (id)) ENGINE = InnoDB;INSERT INTO T3 VALUES(1,'a'),(2,'b'),(3,'c');SESS1:                      SESS2：BEGIN;SELECT * FROM T3;+----+------+| id | name |+----+------+|  1 | a    ||  2 | b    ||  3 | c    |+----+------+                            BEGIN;                            INSERT INTO T3 values(4,'a');                            COMMIT;SELECT * FROM T3;+----+------+| id | name |+----+------+|  1 | a    ||  2 | b    ||  3 | c    |+----+------+UPDATE T3 SET NAME='aa' where name ='a';Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0SELECT * FROM T3;                       +----+------+| id | name |+----+------+|  1 | aa   ||  2 | b    ||  3 | c    ||  4 | aa   |+----+------+                            SELECT * FROM T3;                                                   +----+------+                            | id | name |                            +----+------+                            |  1 | a    |                            |  2 | b    |                            |  3 | c    |                            |  4 | a    |                            +----+------+

在可重复读的情况下，为什么SESS1先开启事务的情况下，还能更新会话2后来提交的数据呢?然后之后的select还能看到更新后的数据？我们查看下官方解释。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

默认情况下，innodb使用MVCC进行快照读。查询只能查看到之前提交的事务更改的数据，之后提交的数据或者没有提交的事务数据是没办法看到的。但是这里有个例外就是同一个事务的查询能看到同一个事务里面的更改。因此当SESS1 在进行UPDATE的时候，会进行当前读，也就是读取所有已经提交的数据，相当于读取的是select * from t3 where where name ='a' for update;的结果集，然后进行UPDATE。之后的select * from t3 where where name ='a'看到的就是当前UPDATE之后的结果了。