关于 MySQL InnoDB锁机制

共计 15663 个字符，预计需要花费 40 分钟才能阅读完成。

一背景
MySQL 锁机制是一个极其复杂的实现，为数据库并发访问和数据一致提供保障。这里仅仅针对 MySQL 访问数据的三种锁做介绍，加深自己对锁方面的掌握。
二常见的锁机制
我们知道对于 InnoDB 存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。常见的有如下三种锁类型，本文未声明情况下都是在 RR 事务隔离级别下的描述。
2.1 Record Locks
记录锁实际上是索引上的锁，锁定具体的一行或者多行记录。当表上没有创建索引时，InnoDB 会创建一个隐含的聚族索引，并且使用该索引锁定数据。通常我们可以使用 show innodb status 看到行锁相关的信息。
2.2 Gap Locks
间隙锁是锁定具体的范围，但是不包含行锁本身。比如

select * from tab where id>10 and id<20；

RR 事务隔离级别下会锁定 10-20 之间的记录，不允许类似 15 这样的值插入到表里，以便消除“幻读”带来的影响。间隙锁的跨度可以是 1 条记录 (Record low 就可以认为是一个特殊的间隙锁，多行，或者为空。当访问的字段是唯一键 / 主键时，间隙锁会降级为 Record lock。RR 事务隔离级别下访问一个空行，也会有间隙锁，后续会举例子说明。
我们可以通过将事务隔离级别调整为 RC 模式或者设置 innodb_locks_unsafe_for_binlog=1 (该参数已经废弃)来禁用 Gap 锁。

2.3 Next-Key Locks
是 Record Lock+Gap Locks, 锁定一个范围并且包含索引本身。例如索引值包含 2，4，9，14 四个值，其 gap 锁的区间如下：
（-∞,2]，(2,4]，(4,9]，(9,14]，(14,+∞）
本文着重从主键, 唯一键、非唯一索引, 不存在值访问四个方面来阐述 RR 模式下锁的表现。
三测试案例
3.1 主键 / 唯一键

CREATE TABLE `lck_primarkey` (
`id` int(11) NOT NULL,
val int(11) not null default 0,
primary key (`id`),
key idx_val(val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)

会话 1

[session1] >select * from lck_primarkey;
+––––+–––––+
| id | val |
+––––+–––––+
| 2 | 3 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
+––––+–––––+
4 rows in set (0.00 sec)
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where id=9 for update;
+––––+–––––+
| id | val |
+––––+–––––+
| 9 | 8 |
+––––+–––––+
1 row in set (0.00 sec)

会话 2

[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_primarkey values(7,6);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(5,5);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(13,13);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(10,9);
Query OK, 1 row affected (0.00 sec)

分析
从例子看，当访问表的 where 字段是主键或者唯一键的时候，session2 中的插入操作并未被 session1 中的 id=8 影响。官方表述

“Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple–column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index–record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
select * from tab where id=100 for update”
就是说当语句通过主键或者唯一键访问数据的时候，Innodb 会使用 Record lock 锁住记录本身，而不是使用间隙锁锁定范围。

需要注意以下两种情况:
1 通过主键或则唯一索引访问不存在的值，也会产生 GAP 锁。

[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where id=7 for update;
Empty set (0.00 sec)
[session2] >insert into lck_primarkey values(8,13);
^CCtrl–C –– sending “KILL QUERY 303042481” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(5,13);
^CCtrl–C –– sending “KILL QUERY 303042481” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(3,13);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(10,13);
Query OK, 1 row affected (0.00 sec)

2 通过唯一索引中的一部分字段来访问数据，比如 unique key(a,b,c)，select * from tab where a=x and b=y; 读者朋友可以自己做这个例子。

3.2 非唯一键

CREATE TABLE `lck_secondkey` (
`id` int(11) NOT NULL,
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lck_secondkey values(2),(4),(9),(14)

会话 1

[session1] >begin ;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_secondkey;
+––––+
| id |
+––––+
| 2 |
| 3 |
| 4 |
| 9 |
| 14 |
+––––+
5 rows in set (0.00 sec)
[session1] >select * from lck_secondkey where id=9 for update;
+––––+
| id |
+––––+
| 9 |
+––––+
1 row in set (0.00 sec)

会话 2

[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_secondkey values(3);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_secondkey values(4);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(5);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(6);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(7);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(8);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(9);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(10);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(11);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(12);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(13);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(14);
Query OK, 1 row affected (0.00 sec)

分析
事务 1 对 id= 9 进行 for update 访问,session2 插入[4,13] 的值都是失败的。根据 MySQL 的锁原理，Innodb 范围索引或者表是通过 Next-key locks 算法，RR 事务隔离级别下，通过非唯一索引访问数据行并不是锁定唯一的行，而是一个范围。从例子上可以看出来 MySQL 对 [4,9] 和 (9,14] 之间的记录加上了锁，防止其他事务对 4 -14 范围中的值进行修改。可能有读者对其中 id=4 不能修改，但是 id=14 的值去可以插入有疑问？可以看接下来的例子

[session1] >select * from lck_primarkey;
+––––+–––––+
| id | val |
+––––+–––––+
| 2 | 3 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
+––––+–––––+
4 rows in set (0.00 sec)
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where val=8 for update;
+––––+–––––+
| id | val |
+––––+–––––+
| 9 | 8 |
+––––+–––––+
1 row in set (0.00 sec)

会话 2

[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_primarkey values(3,5);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(15,13);
Query OK, 1 row affected (0.00 sec)
[session2] >select * from lck_primarkey;
+––––+–––––+
| id | val |
+––––+–––––+
| 2 | 3 |
| 3 | 5 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
| 15 | 13 |
+––––+–––––+
6 rows in set (0.00 sec)
[session2] >insert into lck_primarkey values(16,12);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(16,6);
^CCtrl–C –– sending “KILL QUERY 303040567” to server ...
Ctrl–C –– query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(16,5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[session2] >
[session2] >insert into lck_primarkey values(1,5);
Query OK, 1 row affected (0.00 sec)

分析
因为 session1 对非唯一键 val=8 加上了 gap 锁 [4,5] -[14,13], 非此区间的记录都可以插入表中。记录(1,5),(15,13) 不在此 gap 锁区间，记录 (16,12),(16,6),(16,5) 中的 val 值在被锁的范围内，故不能插入。
四总结
写本文的目的主要是在于温故而知新，侧重于温故。本文着重介绍了三种锁，其实还有两种锁 Insert Intention Locks 和 AUTO-INC Locks 留作后面继续分析。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-01/139389p2.htm

一前言
前一页的文章《InnoDB 锁机制之一》介绍了 InnoDB 锁中的三种锁:record lock, gap lock,next-key lock，本文继续介绍另外两种锁 Insert Intention Locks 和 AUTO-INC Locks
二常见的锁类型
2.1 根据锁持有的时间粒度，分为
1. 内存级别：类似 mutex，很快释放
2. 语句级别：statement 结束，释放
3. 事务级别：transaction 提交或者回滚才释放
4. 会话级别：session 级别，连接断开才释放

2.2 AUTO-INC lock
AUTO-INC lock 是一个特殊的表级锁，当一个事务向含有自增字段的表插入数据时，该事务会获取一个 AUTO-INC lock，其他事务必须等待直到已经获取锁的 insert 语句结束。因此, 多个并发事务不能同时获取同一个表上面的 AUTO-INC lock, 如果持有 AUTO-INC 锁太长时间可能会影响到数据库性能 (比如 INSERT INTO t1… SELECT … FROM t2 这类语句) 或者死锁.
鉴于 AUTO-INC 锁的特性,MySQL 5.1.22 通过新增参数 innodb_autoinc_lock_mode 来控制自增序列的算法。该参数可以设置为 0,1,2.
在学习 innodb_autoinc_lock_mode 之前，我们先了解 insert 语句的类型
1 Simple inserts
能够事先确定具体行数的 insert 语句，比如 insert into tab values()…(); replace 等等。INSERT … ON DUPLICATE KEY UPDATE 和还有子查询的 insert 语句除外。
2 Bulk inserts
和 Simple inserts 对立，事先不能确定插入行数的 insert/replace 语句，insert … select ;replace …select; load data into table .. 这种情况下 Innodb 在执行具体的行的时候会为每一行单独分配一个 auto_increment 值。
3 Mixed-mode inserts
该情形是 Simple inserts 模式中，有些 insert 指定了自增字段的具体值，有些没有指定。比如:
INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);
INSERT … ON DUPLICATE KEY UPDATE

接下来我们再看 MySQL 对 auto_increment 的优化模式。
innodb_autoinc_lock_mode=0，是传统的方式。InnoDB 会在分配前给表加上 AUTO_INC 锁，并在 SQL 结束时释放掉。该模式保证了在 STATEMENT 复制模式下，备库执行类似 INSERT … SELECT 这样的语句时的一致性，因为这样的语句在执行时无法确定到底有多少条记录，只有在执行过程中不允许别的会话分配自增值，才能确保主备一致。
很显然这种锁模式非常影响并发插入的性能，但却保证了一条 SQL 内自增值分配的连续性。
innodb_autoinc_lock_mode=1 ，这个是 InnoDB 的默认值。该模式下对于 Simple inserts，InnoDB 会先加一个 autoinc_mutex 锁，然后去判断表上是否有别的线程加了 LOCK_AUTO_INC 锁，如果有的话，释放 autoinc_mutex，并使用传统的加锁模式。否则，在预留本次插入需要的自增值之后，就快速的将 autoinc_mutex 释放掉。很显然，对于普通的并发 INSERT 操作，都是无需加 LOCK_AUTO_INC 锁的。因此该模式提高了系统并发性；
innodb_autoinc_lock_mode=2，这种模式下只在分配时加个 mutex 即可，很快就释放，不会像值为 1 那样在某些场景下会退化到传统模式。因此设为 2 不能保证批量插入的复制安全性。

2.3 Insert Intention Locks
插入意向锁是 gap 锁的一种，只是针对 insert。当并发事务多条 insert 插入同一个 GAP, 如果他们不是插入同一行记录，会话之间并不会相互等待。例如索引记录删有 12，17 两个记录，两个会话同时插入记录 13，15，他们会分别为 (12,17) 加上 GAP 锁，但相互之间并不冲突（因为插入的记录不冲突）。
当向某个数据页中插入一条记录时，总是会调用函数 lock_rec_insert_check_and_lock 进行锁检查（构建索引时的数据插入除外），会去检查当前插入位置的下一条记录上是否存在锁对象，这里的下一条记录不是指的物理连续，而是按照逻辑顺序的下一条记录。
如果下一条记录上不存在锁对象：若记录是二级索引上的，先更新二级索引页上的最大事务 ID 为当前事务的 ID；直接返回成功。
如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了 GAP。如果 GAP 被锁住了，并判定和插入意向 GAP 锁冲突，当前操作就需要等待，加的锁类型为 LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，并进入等待状态。但是插入意向锁之间并不互斥。这意味着在同一个 GAP 里可能有多个申请插入意向锁的会话。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139389.htm

select * from tab where id>10 and id<20；

CREATE TABLE `lck_primarkey` (
`id` int(11) NOT NULL,
val int(11) not null default 0,
primary key (`id`),
key idx_val(val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)

会话 1

[session1] >select * from lck_primarkey;
+––––+–––––+
| id | val |
+––––+–––––+
| 2 | 3 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
+––––+–––––+
4 rows in set (0.00 sec)
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where id=9 for update;
+––––+–––––+
| id | val |
+––––+–––––+
| 9 | 8 |
+––––+–––––+
1 row in set (0.00 sec)

会话 2

[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_primarkey values(7,6);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(5,5);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(13,13);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(10,9);
Query OK, 1 row affected (0.00 sec)

分析
从例子看，当访问表的 where 字段是主键或者唯一键的时候，session2 中的插入操作并未被 session1 中的 id=8 影响。官方表述

“Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple–column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index–record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
select * from tab where id=100 for update”
就是说当语句通过主键或者唯一键访问数据的时候，Innodb 会使用 Record lock 锁住记录本身，而不是使用间隙锁锁定范围。