MySQL Lock MySQL 锁的原理详解。

锁

概念

锁在现实中的意义为: 封闭的器物，以钥匙或暗码开启。

在计算机中的锁一般用来管理对共享资源的并发访问，比如我们java同学熟悉的 Lock, synchronized 等都是我们常见的锁。

当然在我们的数据库中也有锁用来控制资源的并发访问，这也是数据库和文件系统的区别之一。

开发者要懂锁吗？

通常来说对于一般的开发人员，在使用数据库的时候一般懂点 DQL(select),DML(insert,update,delete) 就够了。

比如有一点线上死锁了，如果你只会上面的，估计无从下手。

MySQL 体系

本次测试版本：MySQL V5.6.0

Mysql 体系架构

• 查看

查看所有的引擎

mysql> show engines \G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

当前数据库默认的引擎

上面列举了这么多，我们用的最多的。

也就是 InnoDB

mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+

MyIsam vs InnoDB

对比项	InnoDB	MyIsAM
事务	支持	不支持
锁	支持MVCC行锁	表锁
外键	支持	不支持
存储空间	存储空间由于需要高速缓存，较大	可压缩
适用场景	有一定量的update和Insert	大量的select

事务的隔离性

事务的隔离性

InnoDB 锁类型

首先看下有哪些类型的锁

S or X

在InnoDb中实现了两个标准的行级锁，可以简单的看为两个读写锁:

S-共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。

X-排他锁: 又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。

兼容性: 是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。

ps: 除了 S-S 不冲突之外，其他全部冲突。

意向锁

意向锁在 InnoDB 中是表级锁，和他的名字一样他是用来表达一个事务想要获取什么。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁分为:

IS-意向共享锁: 表达一个事务想要获取一张表中某几行的共享锁。

IX-意向排他锁: 表达一个事务想要获取一张表中某几行的排他锁。

这个锁有什么用呢？为什么需要这个锁呢？

首先说一下如果没有这个锁，如果要给这个表加上表锁，一般的做法是去遍历每一行看看他是否有行锁，这样的话效率太低，而我们有意向锁，只需要判断是否有意向锁即可，不需要再去一行行的去扫描。

• 特点

(1) 首先，意向锁，是一个表级别的锁(table-level locking)；

(2) 意向锁分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

• 兼容性

在 InnoDB 中由于支持的是行级的锁，因此 InnboDB 锁的兼容性可以扩展如下：

.	IX	IS	X	S
IX	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
S	冲突	兼容	冲突	兼容

意向锁协议(intention locking protocol)并不复杂：

1. 事务要获得某些行的S锁，必须先获得表的IS锁

2. 事务要获得某些行的X锁，必须先获得表的IX锁

• 例子

select ... lock in share mode 要设置IS锁；

select ... for update 要设置IX锁；

自增长锁

自增长锁(Auto-inc Locks)是一种特殊的表锁机制，提升并发插入性能。

对于这个锁有几个特点:

• 表级别锁

• 在 sql 执行完就释放锁，并不是事务执行完。

• 对于 insert...select 大数据量插入会影响插入性能，因为会阻塞另外一个事务执行。

• 自增算法可以配置。

在 MySQL5.1.2 版本之后，有了很多优化，可以根据不同的模式来进行调整自增加锁的方式。

锁的模式

最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

在 MySQL 中 innodb_autoinc_lock_mode 有3种配置模式：0、1、2，分别对应”传统模式”, “连续模式”, “交错模式”。

1. 传统模式: 也就是我们最上面的使用表锁。

2. 连续模式: 对于插入的时候可以确定行数的使用互斥量，对于不能确定行数的使用表锁的模式。

3. 交错模式: 所有的都使用互斥量，为什么叫交错模式呢，有可能在批量插入时自增值不是连续的，当然一般来说如果不看重自增值连续一般选择这个模式，性能是最好的。

例子

• 默认环境

MySQL，InnoDB，默认的隔离级别(RR)，假设有数据表：

t(id AUTO_INCREMENT, name);

数据表中有数据：

1, shenjian 2, zhangsan 3, lisi

• 测试场景

事务A先执行，还未提交：

insert into t(name) values(xxx);

事务B后执行：

insert into t(name) values(ooo);

问：事务B会不会被阻塞？

解析

如果不等待。

(1) 事务 A 先执行 insert，会得到一条 (4, xxx) 的记录，由于是自增列，故不用显示指定id为4，InnoDB会自动增长，注意此时事务并未提交；

(2) 事务 B 后执行 insert，假设不会被阻塞，那会得到一条 (5, ooo) 的记录；

此时，并未有什么不妥，但如果 A 继续添加

(3) 事务 A 继续 insert

insert into t(name) values(xxoo);

会得到一条(6, xxoo)的记录。

(4) 事务 A 再 select

select * from t where id > 3;

得到的结果是：

4, xxx
6, xxoo

画外音：不可能查询到 5 的记录，再RR的隔离级别下，不可能读取到还未提交事务生成的数据。

咦，这对于事务A来说，就很奇怪了，对于 AUTO_INCREMENT 的列，连续插入了两条记录，一条是 4，接下来一条变成了 6，就像莫名其妙的幻影。

其他场景

• 如果没有设置为自增主键，会怎么样？

如果没有设置自增长，参见 插入意向锁

InnoDB 锁算法

如何去使用这些锁，还是得靠锁算法。

记录锁(Record-Lock)

记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录。

• 如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住。

• 如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。

• 如果要锁的列没有索引，则会进行全表记录加锁。

实例

select * from t where id=1 for update;

它会在 id=1 的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

• snapshot read

select * from t where id=1;

是快照读，它并不加锁。

mvcc-当前读和快照读

间隙锁(GAP Lock)

间隙锁顾名思义锁间隙，不锁记录。

锁间隙的意思就是锁定某一个范围，间隙锁又叫 gap 锁，其不会阻塞其他的 gap 锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。

|   1   |   (1, 3] |   3   |    (3, 5]  |   5   |
    |       |                   |
    |       |                   |
  记录锁   GAP 锁             GAP 锁

目的

依然是上面的例子，InnoDB，RR：

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

这个SQL语句

select * from t 
    where id between 8 and 15 
    for update;

会封锁区间，以阻止其他事务 id=10 的记录插入。

• 为什么要阻止id=10的记录插入？

如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致不可重复读。

• RC 失效

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁(Next-key Lock)

这个锁本质是记录锁加上 gap 锁。

在RR隔离级别下( InnoDB 默认)，Innodb 对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。

• 为什么呢?

因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据会造成幻读。

这里也说明了为什么 Mysql 可以在 RR 级别下解决幻读。

• 范围

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

例子

还是上面例子的数据。

PK上潜在的临键锁为：

(-infinity, 1]
(1, 3]
(3, 5]
(5, 9]
(9, +infinity]

• 目的

临键锁的主要目的，也是为了避免幻读(Phantom Read)。

• 失效场景

如果把事务的隔离级别降级为RC，临键锁则也会失效。

插入意向锁

官方解释

插入意图锁定是在行插入之前通过插入操作设置的间隙锁定类型。

这个锁表示要以这样一种方式插入: 如果多个事务插入到相同的索引间隙中，如果它们不在间隙中的相同位置插入，则无需等待其他事务。

假设有值为4和7的索引记录。尝试分别插入值为5和6的独立事务，在获取插入行上的独占锁之前，每个事务都用insert intention locks锁定4和7之间的间隙，但不会因为行不冲突而互相阻塞。

• 理解

可以看出插入意向锁是在插入的时候产生的,在多个事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

这里要说明的是如果有间隙锁了，插入意向锁会被阻塞。

作用

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？

插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

• 高并发插入

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

例子

• 场景

在MySQL，InnoDB，RR下：

t(id unique PK, name);

数据表中有数据：

10, shenjian 20, zhangsan 30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

• 问题

(1) 会使用什么锁？

(2) 事务 B 会不会被阻塞呢？

• 答案

回答：

虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：

1. 使用的是插入意向锁

2. 并不会阻塞事务 B

MVCC

mvcc

测试

准备工作

• mysql 版本号

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.30, for osx10.11 (x86_64) using  EditLine wrapper

• 建表

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `comment` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT = 0;

• 初始化数据

INSERT INTO `user`(id, name, comment) VALUES (1, '1', '1');
INSERT INTO `user`(id, name, comment) VALUES (5, '5', '5');
INSERT INTO `user`(id, name, comment) VALUES (9, '9', '9');

• 默认隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

• 设置自动提交为 false

SET AUTOCOMMIT=0; -- 禁止自动提交

如果想恢复：

SET AUTOCOMMIT=1; -- 开启自动提交

实验一

测试过程

• 事务 A

begin;

select * from user where name = '5' for update;

• 事务 B

执行事务 A 后执行事务 B，

BEGIN;

INSERT INTO `user`(id, name, comment) VALUES (6, '6', '6');

COMMIT;

会发现事务 B 如下：

[2018-09-03 15:50:54] [41000][1205] Lock wait timeout exceeded; try restarting transaction

疑问

此处是对 name=’5’ 的元素进行加锁，为何插入新元素报错？

一探究竟

• 锁的信息

select * from information_schema.INNODB_LOCKS

查询结果

mysql> select * from information_schema.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 2898702:2752:4:4 | 2898702     | X,GAP     | RECORD    | `test`.`user` | index_name |       2752 |         4 |        4 | '9', 9    |
| 2898701:2752:4:4 | 2898701     | X,GAP     | RECORD    | `test`.`user` | index_name |       2752 |         4 |        4 | '9', 9    |
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

• 原因

事务 A 给数据【5】加上了 next-key 锁。

事务 B 插入的时候会首先进行插入意向锁的插入，由于间隙锁和插入意向锁的冲突，导致了阻塞。

实验二

上面的 name 使用的是普通的非唯一索引，如果使用主键会怎么样呢？

测试过程

• 事务 A

begin;

select * from user where id = '5' for update;

• 事务 B

执行事务 A 后执行事务 B，

BEGIN;

INSERT INTO `user`(id, name, comment) VALUES (6, '6', '6');

COMMIT;

疑问

居然发现事务 B 并没有发生阻塞，why？

原因

执行事务 B 的时候，暂时不 commit。

可以看到对应的加锁情况如下：

mysql> select * from information_schema.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 2898667:2752:3:5 | 2898667     | S         | RECORD    | `test`.`user` | PRIMARY    |       2752 |         3 |        5 | 6         |
| 2898663:2752:3:5 | 2898663     | X         | RECORD    | `test`.`user` | PRIMARY    |       2752 |         3 |        5 | 6         |
+------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

原来是因为唯一索引会降级记录锁，这么做的理由是:

非唯一索引加 next-key 锁由于不能确定明确的行数有可能其他事务在你查询的过程中，再次添加这个索引的数据，导致隔离性遭到破坏，也就是幻读。

唯一索引由于明确了唯一的数据行，所以不需要添加间隙锁解决幻读。

实验三

那如果是没有索引的普通字段呢？

测试过程

• 事务 A

begin;

select * from user where id = '5' for update;

• 事务 B

执行事务 A 后执行事务 B，

BEGIN;

INSERT INTO `user`(id, name, comment) VALUES (6, '6', '6');

COMMIT;

or

BEGIN;

INSERT INTO `user`(id, name, comment) VALUES (12, '12', '12');

COMMIT;

疑问

发现上面 2 个数据都无法插入。

咋不管是用实验1非间隙锁范围的数据，还是用间隙锁里面的数据都不行，难道是加了表锁吗？

的确，如果用没有索引的数据，其会对所有聚簇索引上都加上 next-key 锁。

所以大家平常开发的时候如果对查询条件没有索引的，一定进行一致性读，也就是加锁读，会导致全表加上索引，会导致其他事务全部阻塞，数据库基本会处于不可用状态。

死锁

概念

死锁: 是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。说明有等待才会有死锁，解决死锁可以通过去掉等待，比如回滚事务。

解决死锁的两个办法

等待超时:当某一个事务等待超时之后回滚该事务，另外一个事务就可以执行了，但是这样做效率较低，会出现等待时间，还有个问题是如果这个事务所占的权重较大，已经更新了很多数据了，但是被回滚了，就会导致资源浪费。

等待图(wait-for-graph): 等待图用来描述事务之间的等待关系，当这个图如果出现回路如下:

t1->t2->t3->t4->t1

就出现回滚，通常来说 InnoDB 会选择回滚权重较小的事务，也就是 undo 较小的事务。

问题复现

基本需要的基本功都有了，于是在自己的本地表中开始复现这个问题:

场景

时间点	事务A	事务B
1	begin;	begin;
2	delete from user where name = ‘777’;	delete from user where name = ‘666’;
3	insert user select 27,’777’,’777’;	insert user select 26,’666’,’666’;
4	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	Query OK, 1 row affected (14.32 sec) Records: 1 Duplicates: 0 Warnings: 0

解释

可以看见事务A出现被回滚了，而事务B成功执行。

具体每个时间点发生了什么呢?

• 时间点2

事务A删除name = ‘777’的数据，需要对777这个索引加上next-Key锁，但是其不存在，所以只对555-999之间加间隙锁，同理事务B也对555-999之间加间隙锁。间隙锁之间是兼容的。

• 时间点3

事务A，执行Insert操作，首先插入意向锁，但是555-999之间有间隙锁，由于插入意向锁和间隙锁冲突，事务A阻塞，等待事务B释放间隙锁。事务B同理，等待事务A释放间隙锁。于是出现了A->B,B->A回路等待。

• 时间点4

事务管理器选择回滚事务A，事务B插入操作执行成功。

修复BUG

这个问题总算是被找到了，就是因为间隙锁，现在需要解决这个问题，这个问题的原因是出现了间隙锁，那就来去掉他吧:

方案一: 隔离级别降级为RC，在RC级别下不会加入间隙锁，所以就不会出现毛病了，但是在RC级别下会出现幻读，可提交读都破坏隔离性的毛病，所以这个方案不行。

方案二: 隔离级别升级为可序列化，小明经过测试后发现不会出现这个问题，但是在可序列化级别下，性能会较低，会出现较多的锁等待，同样的也不考虑。

方案三: 修改代码逻辑，不要直接删，改成每个数据由业务逻辑去判断哪些是更新，哪些是删除，那些是添加，这个工作量稍大，小明写这个直接删除的逻辑就是为了不做这些复杂的事的，所以这个方案先不考虑。

方案四: 较少的修改代码逻辑，在删除之前，可以通过快照查询(不加锁)，如果查询没有结果，则直接插入，如果有通过主键进行删除，在之前第三节实验2中，通过唯一索引会降级为记录锁，所以不存在间隙锁。

经过考明选择了第四种，马上进行了修复，然后上线观察验证，发现现在已经不会出现这个Bug了，这下总算能睡个安稳觉了。

拓展阅读

mysql-lock-in-action

2pl

mysql 索引数据结构

sql 隔离级别

参考资料

• juejin

https://juejin.im/post/5b6c5be86fb9a04fb30a2bc7

• 58

InnoDB，select为啥会阻塞insert？

InnoDB并发插入，居然使用意向锁？

挖坑，InnoDB的七种锁

插入InnoDB自增列，居然是表锁？

InnoDB并发如此高，原因竟然在这？

InnoDB行锁，如何锁住一条不存在的记录？

• 大家

http://hedengcheng.com/?p=771#_Toc374698317

《Mysql技术内幕-InnoDB引擎》

• 锁
  • 概念
  • 开发者要懂锁吗？
• MySQL 体系
  • Mysql 体系架构
  • 当前数据库默认的引擎
  • MyIsam vs InnoDB
• 事务的隔离性
• InnoDB 锁类型
  • S or X
  • 意向锁
  • 自增长锁
    • 锁的模式
    • 例子
    • 解析
    • 其他场景
• InnoDB 锁算法
  • 记录锁(Record-Lock)
    • 实例
  • 间隙锁(GAP Lock)
    • 目的
  • 临键锁(Next-key Lock)
    • 例子
  • 插入意向锁
    • 官方解释
    • 作用
    • 例子
• MVCC
• 测试
  • 准备工作
  • 实验一
    • 测试过程
    • 疑问
    • 一探究竟
  • 实验二
    • 测试过程
    • 疑问
    • 原因
  • 实验三
    • 测试过程
    • 疑问
• 死锁
  • 概念
  • 解决死锁的两个办法
• 问题复现
  • 场景
  • 解释
  • 修复BUG
• 拓展阅读
• 参考资料