1. 前言
数据事务设计遵循 ACID 的原则.
MySQL 数据库提供了四种默认的隔离级别, 读未提交(read-uncommitted), 读已提交(或不可重复读)(read-committed), 可重复读(repeatable-read), 串行化(serializable).
MySQL 的默认隔离级别是 RR.
2. 锁基本概念
2.1 共享锁和排它锁
InnoDB 实现了两种标准行级锁, 一种是共享锁(shared locks,S 锁), 另一种是独占锁, 或者叫排它锁(exclusive locks,X 锁).
S 锁允许当前持有该锁的事务读取行.
X 锁允许当前持有该锁的事务更新或删除行.
S 锁
如果事务 T1 持有了行 r 上的 S 锁, 则其他事务可以同时持有行 r 的 S 锁, 但是不能对行 r 加 X 锁.
X 锁
如果事务 T1 持有了行 r 上的 X 锁, 则其他任何事务不能持有行 r 的 X 锁, 必须等待 T1 在行 r 上的 X 锁释放.
如果事务 T1 在行 r 上保持 S 锁, 则另一个事务 T2 对行 r 的锁的请求按如下方式处理:
T2 可以同时持有 S 锁
T2 如果想在行 r 上获取 X 锁, 必须等待其他事务对该行添加的 S 锁或 X 锁的释放.
2.2 意向锁 - Intention Locks
InnoDB 支持多种粒度的锁, 允许行级锁和表级锁的共存. 例如 LOCK TABLES ... WRITE 等语句可以在指定的表上加上独占锁.
InnoBD 使用意向锁来实现多个粒度级别的锁定. 意向锁是表级锁, 表示 table 中的 row 所需要的锁 (S 锁或 X 锁) 的类型.
意向锁分为意向共享锁 (IS 锁) 和意向排它锁(IX 锁).
IS 锁表示当前事务意图在表中的行上设置共享锁, 下面语句执行时会首先获取 IS 锁, 因为这个操作在获取 S 锁:
SELECT ... LOCK IN SHARE MODE
IX 锁表示当前事务意图在表中的行上设置排它锁. 下面语句执行时会首先获取 IX 锁, 因为这个操作在获取 X 锁:
SELECT ... FOR UPDATE
事务要获取某个表上的 S 锁和 X 锁之前, 必须先分别获取对应的 IS 锁和 IX 锁.
2.3 锁的兼容性
锁的兼容矩阵如下:
--- | 排它锁 (X) | 意向排它锁 (IX) | 共享锁 (S) | 意向共享锁(IS) |
---|---|---|---|---|
排它锁(X) | N | N | N | N |
意向排它锁(IX) | N | OK | N | OK |
共享锁(S) | N | N | OK | OK |
意向共享锁(IS) | N | OK | OK | OK |
按照上面的兼容性, 如果不同事务之间的锁兼容, 则当前加锁事务可以持有锁, 如果有冲突则会等待其他事务的锁释放.
如果一个事务请求锁时, 请求的锁与已经持有的锁冲突而无法获取时, 互相等待就可能会产生死锁.
意向锁不会阻止除了全表锁定请求之外的任何锁请求.
意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行.
3. InnoDB 中的锁
常见的锁有 Record 锁, gap 锁, next-key 锁, 插入意向锁, 自增锁等.
下面会对每一种锁给出一个查看锁的示例.
3.1 准备工作
3.1.1 测试用表结构
示例的基础是一个只有两列的数据库表.
- MySQL> CREATE TABLE test (
- id int(11) NOT NULL,
- code int(11) NOT NULL,
- PRIMARY KEY(id),
- KEY (code)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- MySQL> INSERT INTO test(id,code) values(1,1),(10,10);
数据表 test 只有两列, id 是主键索引, code 是普通的索引(注意, 一定不要是唯一索引), 并初始化了两条记录, 分别是(1,1),(10,10).
这样, 我们验证唯一键索引就可以使用 id 列, 验证普通索引 (非唯一键二级索引) 时就使用 code 列.
3.1.2 查看锁状态的方式
要看到锁的情况, 必须手动开启多个事务, 其中一些锁的状态的查看则必须使锁处于 waiting 状态, 这样才能在 MySQL 的引擎状态日志中看到.
命令:
MySQL> show engine innodb status;
这条命令能显示最近几个事务的状态, 查询和写入情况等信息. 当出现死锁时, 命令能给出最近的死锁明细.
3.2 记录锁 Record Locks
Record 锁
Record Lock 是对索引记录的锁定. 记录锁有两种模式, S 模式和 X 模式.
例如 SELECT id FROM test WHERE id = 10 FOR UPDATE; 表示防止任何其他事务插入, 更新或者删除 id =10 的行.
记录锁始终只锁定索引. 即使表没有建立索引, InnoDB 也会创建一个隐藏的聚簇索引(隐藏的递增主键索引), 并使用此索引进行记录锁定.
查看记录锁
开启第一个事务, 不提交, 测试完之后回滚.
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> update test set id=2 where id=1;
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
事务加锁情况
- MySQL> show engine innodb status\G;
- ...
- ------------
- TRANSACTIONS
- ------------
- ---TRANSACTION 366811, ACTIVE 690 sec
- 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
- MySQL thread id 785, OS thread handle 123145432457216, query id 729076 localhost 127.0.0.1 root
- ...
可以看到有一行被加了锁. 由之前对锁的描述可以推测出, update 语句给 id=1 这一行上加了一个 X 锁.
注意: X 锁广义上是一种抽象意义的排它锁, 即锁一般分为 X 模式和 S 模式, 狭义上指 row 或者 index 上的锁, 而 Record 锁是索引上的锁.
为了不修改数据, 可以用 select ... for update 语句, 加锁行为和 update,delete 是一样的, insert 加锁机制较为复杂, 后面的章节会提到.
第一个事务保持原状, 不要提交或者回滚, 现在开启第二个事务.
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> update test set id=3 where id=1;
执行 update 时, sql 语句的执行被阻塞了. 查看下事务状态:
- MySQL> show engine innodb status\G;
- ...
- ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 62 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 366820 lock_mode X locks rec but not gap waiting
- Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
- 0: len 8; hex 0000000000000001; asc ;;
- 1: len 6; hex 0000000598e3; asc ;;
- 2: len 7; hex 7e000001a80896; asc ~ ;;
- ------------------
- ...
喜闻乐见, 我们看到了这个锁的状态. 状态标题是'事务正在等待获取锁', 描述中的 lock_mode X locks rec but not gap 就是本章节中的 record 记录锁, 直译一下'X 锁模式锁住了记录'. 后面还有一句 but not gap 意思是只对 record 本身加锁, 并不对间隙加锁, 间隙锁的叙述见下一个章节.
3.3 间隙锁 Gap Locks
间隙锁
间隙锁作用在索引记录之间的间隔, 又或者作用在第一个索引之前, 最后一个索引之后的间隙. 不包括索引本身.
例如, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 这条语句阻止其他事务插入 10 和 20 之间的数字, 无论这个数字是否存在.
间隙可以跨越 0 个, 单个或多个索引值.
间隙锁是性能和并发权衡的产物, 只存在于部分事务隔离级别.
select * from table where id=1;
唯一索引可以锁定一行, 所以不需要间隙锁锁定.
如果列没有索引或者具有非唯一索引, 该语句会锁定当前索引前的间隙.
在同一个间隙上, 不同的事务可以持有上述兼容 / 冲突表中冲突的两个锁. 例如, 事务 T1 现在持有一个间隙 S 锁, T2 可以同时在同一个间隙上持有间隙 X 锁.
允许冲突的锁在间隙上锁定的原因是, 如果从索引中清除一条记录, 则由不同事务在这条索引记录上的加间隙锁的动作必须被合并.
InnoDB 中的间隙锁的唯一目的是防止其他事务插入间隙.
间隙锁是可以共存的, 一个事务占用的间隙锁不会阻止另一个事务获取同一个间隙上的间隙锁.
如果事务隔离级别改为 RC, 则间隙锁会被禁用.
查看间隙锁
按照官方文档, where 子句查询条件是唯一键且指定了值时, 只有 record 锁, 没有 gap 锁.
如果 where 语句指定了范围, gap 锁是存在的.
这里只测试验证一下当指定非唯一键索引的时候, gap 锁的位置, 按照文档的说法, 会锁定当前索引及索引之前的间隙.(指定了非唯一键索引, 例code=10, 间隙锁仍然存在)
开启第一个事务, 锁定一条非唯一的普通索引记录
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select * from test where code = 10 for update;
- +----+------+
- | id | code |
- +----+------+
- | 10 | 10 |
- +----+------+
- 1 row in set (0.00 sec)
由于预存了两条数据, row(1,1)和 row(10,10), 此时这个间隙应该是 1<gap<10. 我们先插入 row(2,2)来验证下 gap 锁的存在, 再插入 row(0,0)来验证 gap 的边界.
按照间隙锁的官方文档定义, select * from test where code = 10 for update; 会锁定 code=10 这个索引, 并且会锁定 code<10 的间隙.
开启第二个事务, 在 code=10 之前的间隙中插入一条数据, 看下这条数据是否能够插入.
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into test values(2,2);
插入的时候, 执行被阻塞, 查看引擎状态:
- MySQL> show engine innodb status\G;
- ...
- ---TRANSACTION 366864, ACTIVE 5 sec inserting
- MySQL tables in use 1, locked 1
- LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
- MySQL thread id 793, OS thread handle 123145434963968, query id 730065 localhost 127.0.0.1 root update
- insert into test values(2,2)
- ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 63 page no 4 n bits 72 index code of table `test`.`test` trx id 366864 lock_mode X locks gap before rec insert intention waiting
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 8; hex 800000000000000a; asc ;;
- 1: len 8; hex 000000000000000a; asc ;;
- ------------------
- ...
插入语句被阻塞了, lock_mode X locks gap before rec, 由于第一个事务锁住了 1 到 10 之间的 gap, 需要等待获取锁之后才能插入.
如果再开启一个事务, 插入(0,0)
- MySQL> start transaction;
- MySQL> insert into test values(0,0);
- Query OK, 1 row affected (0.00 sec)
可以看到: 指定的非唯一建索引的 gap 锁的边界是当前索引到上一个索引之间的 gap.
最后给出锁定区间的示例, 首先插入一条记录(5,5)
- MySQL> insert into test values(5,5);
- Query OK, 1 row affected (0.00 sec)
开启第一个事务:
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select * from test where code between 1 and 10 for update;
- +----+------+
- | id | code |
- +----+------+
- | 1 | 1 |
- | 5 | 5 |
- | 10 | 10 |
- +----+------+
- 3 rows in set (0.00 sec)
第二个事务, 试图去更新 code=5 的行:
- MySQL> begin;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> update test set code=4 where code=5;
执行到这里, 如果第一个事务不提交或者回滚的话, 第二个事务一直等待直至 MySQL 中设定的超时时间.
3.4 Next-key Locks
Next-key 锁
Next-key 锁实际上是 Record 锁和 gap 锁的组合. Next-key 锁是在下一个索引记录本身和索引之前的 gap 加上 S 锁或是 X 锁(如果是读就加上 S 锁, 如果是写就加 X 锁).
默认情况下, InnoDB 的事务隔离级别为 RR, 系统参数 innodb_locks_unsafe_for_binlog 的值为 false.InnoDB 使用 next-key 锁对索引进行扫描和搜索, 这样就读取不到幻象行, 避免了幻读的发生.
幻读是指在同一事务下, 连续执行两次同样的 SQL 语句, 第二次的 SQL 语句可能会返回之前不存在的行.
当查询的索引是唯一索引时, Next-key lock 会进行优化, 降级为 Record Lock, 此时 Next-key lock 仅仅作用在索引本身, 而不会作用于 gap 和下一个索引上.
查看 Next-key 锁
Next-key 锁的作用范围
如上述例子, 数据表 test 初始化了 row(1,1),row(10,10), 然后插入了 row(5,5). 数据表如下:
- MySQL> select * from test;
- +----+------+
- | id | code |
- +----+------+
- | 1 | 1 |
- | 5 | 5 |
- | 10 | 10 |
- +----+------+
- 3 rows in set (0.00 sec)
由于 id 是主键, 唯一索引, MySQL 会做优化, 因此使用 code 这个非唯一键的二级索引来举例说明.
对于 code, 可能的 next-key 锁的范围是:
(-∞,1]
- (1,5]
- (5,10]
- (10,+∞)
开启第一个事务, 在 code=5 的索引上请求更新:
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select * from test where code=5 for update;
- +----+------+
- | id | code |
- +----+------+
- | 5 | 5 |
- +----+------+
- 1 row in set (8.81 sec)
之前在 gap 锁的章节中介绍了, code=5 for update 会在 code=5 的索引上加一个 record 锁, 还会在 1<gap<5 的间隙上加 gap 锁. 现在不再验证, 直接插入一条(8,8):
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into test values(8);
insert 处于等待执行的状态, 这就是 next-key 锁生效而导致的结果. 第一个事务, 锁定了区间 (1,5], 由于 RR 的隔离级别下 next-key 锁处于开启生效状态, 又锁定了(5,10] 区间. 所以插入 SQL 语句的执行被阻塞.
解释: 在这种情况下, 被锁定的区域是 code=5 前一个索引到它的间隙, 以及 next-key 的区域. code=5 for update 对索引的锁定用区间表示, gap 锁锁定了 (1,5),record 锁锁定了{5} 索引记录, next-key 锁锁住了 (5,10], 也就是说整个(1,10] 的区间被锁定了. 由于是 for update, 所以这里的锁都是 X 锁, 因此阻止了其他事务中带有冲突锁定的操作执行.
如果我们在第一个事务中, 执行了 code>8 for update, 在扫描过程中, 找到了 code=10, 此时就会锁住 10 之前的间隙 (5 到 10 之间的 gap),10 本身(record), 和 10 之后的间隙(next-key). 此时另一个事务插入(6,6),(9,9) 和(11,11)都是不被允许的, 只有在前一个索引 5 及 5 之前的索引和间隙才能执行插入(更新和删除也会被阻塞).
3.5 插入意向锁 Insert Intention Locks
插入意向锁在行插入之前由 INSERT 设置一种间隙锁, 是意向排它锁的一种.
在多事务同时写入不同数据至同一索引间隙的时, 不会发生锁等待, 事务之间互相不影响其他事务的完成, 这和间隙锁的定义是一致的.
假设一个记录索引包含 4 和 7, 其他不同的事务分别插入 5 和 6, 此时只要行不冲突, 插入意向锁不会互相等待, 可以直接获取. 参照锁兼容 / 冲突矩阵.
插入意向锁的例子不再列举, 可以查看 gap 锁的第一个例子.
3.6 自增锁
自增锁 (AUTO-INC Locks) 是事务插入时自增列上特殊的表级别的锁. 最简单的一种情况: 如果一个事务正在向表中插入值, 则任何其他事务必须等待, 以便第一个事务插入的行接收连续的主键值.
我们一般把主键设置为 AUTO_INCREMENT 的列, 默认情况下这个字段的值为 0,InnoDB 会在 AUTO_INCREMENT 修饰下的数据列所关联的索引末尾设置独占锁. 在访问自增计数器时, InnoDB 使用自增锁, 但是锁定仅仅持续到当前 SQL 语句的末尾, 而不是整个事务的结束, 毕竟自增锁是表级别的锁, 如果长期锁定会大大降低数据库的性能. 由于是表锁, 在使用期间, 其他会话无法插入表中.
4 幻读
这一章节, 我们通过幻读, 逐步展开对 InnoDB 锁的探究.
4.1 幻读概念
解释了不同概念的锁的作用域, 我们来看一下幻读到底是什么. 幻读在 RR 条件下是不会出现的. 因为 RR 是 Repeatable Read, 它是一种事务的隔离级别, 直译过来也就是 "在同一个事务中, 同样的查询语句的读取是可重复", 也就是说他不会读到 "幻影行"(其他事务已经提交的变更), 它读到的只能是重复的(无论在第一次查询之后其他事务做了什么操作, 第二次查询结果与第一次相同).
上面的例子都是使用 for update, 这种读取操作叫做当前读, 对于普通的 select 语句均为快照读.
当前读, 又叫加锁读, 或者 阻塞读. 这种读取操作不再是读取快照, 而是读取最新版本并且加锁.
快照读不会添加任何锁.
官方文档对于幻读的定义是这样的:
- MySQL> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select * from test where id>8;
- +----+------+
- | id | code |
- +----+------+
- | 10 | 10 |
- +----+------+
- 1 row in set (0.00 sec)
- MySQL> update test set code=9 where id=10;
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MySQL> select * from test where id>8;
- +----+------+
- | id | code |
- +----+------+
- | 10 | 9 |
- +----+------+
- 1 row in set (0.00 sec)
- MySQL> select * from test;
- +----+------+
- | id | code |
- +----+------+
- | 1 | 1 |
- | 5 | 5 |
- | 10 | 10 |
- +----+------+
- 3 rows in set (0.01 sec)
来源: http://www.linuxidc.com/Linux/2019-03/157384.htm