『MySQL』搞懂 InnoDB 锁机制 以及 高并发下如何解决超买超卖问题

MySQL系列文章：

「MySQL」高性能索引优化策略

「MySQL」揭开索引神秘面纱

「MySQL」 MySQL执行流程

MySQL 锁是系列第四篇文章，之前感觉锁一直是比较神秘的知识，为了搞懂它，翻看了《MySQL技术内幕》第二版，并记录如下：

1. 锁知识

1.1 为什么会有锁的机制

• 最大程度的利用数据库的并发访问；
• 确保每个用户能以一致的方式读取和修改数据。

1.2 lock 与 latch

• latch 一般叫做闩锁，轻量级。 在InnoDB存储引擎中，latch分为 mutex （互斥锁）和 rwlock（读写锁），目的是用来保证并发线程操作临界资源的正确性，并且通常也没有死锁检测机制。很少用到。
• lock 是本文的主角，它的对象是事务，用来锁定数据库中的对象，如表、页、行。且lock的对象需要再事务commit 或者 rollback 后进行释放。有死锁检测机制。

1.3 锁的类型

1.3.1 行锁 和 表锁

• 定义

  • 行锁，顾名思义就是锁表中对应的行，只限制当前行的读写。
  • 表锁，锁整张表，限制的是整张表的数据读写。

• 对比

  • 行锁，计算机资源开销大，加锁校慢，同时会出现死锁，但锁定粒度小，锁冲突的概率最低，并发度最高，性能高。
  • 表锁，计算机资源开销小，对比行锁，加锁快，也不会出现死锁，但锁定粒度大，锁冲突的概率最高，并发度最低，性能低。

• 限制条件

  • 行锁的实现，SQL语句必须使用索引。如果没有使用索引，则变成了表锁。

行锁和表锁，在不同引擎还有所区别，MyISAM只有表锁，没有行锁，不支持事务。 InnoDB 有行锁和表锁，支持事务。

1.3.2 共享锁(S Lock) 和 排他锁(X Lock)

InnoDB 存储引擎实现了两种标准的行锁，就是共享锁，也称叫S锁，允许事务读一行数据。排他锁，也称叫X锁，允许事务删除或更新一行数据。

• 特性

  • 共享锁和共享锁之间是兼容的，但跟排他锁不兼容。这是什么意思了，假设A事务对某行r数据加了共享锁，那A是可以读取和修改r的内容。其他事务B是可以读取r的内容，获取行r的共享锁，但不能进行修改，也就是不能获取行r的排他锁。需要等待事务A释放行r上的共享锁。
  • 排他锁与排他锁以及共享锁均不兼容。假设A事务对行r加了排他锁，A是可以读取和修改行r的内容。但是其余事务B不能对行r进行修改，即不能获取排他锁，也不能对行r加共享锁读取。

• 加锁方式

  • select语句 在查询语句中，可以通过在 SQL 语句中主动加锁。

    • 共享锁:

      select * from table where 索引限制 lock in share mode
      

      记住，行锁查询是需要具备索引条件。比如执行: select * from user where id=1 lock in share mode. 其中 id 是主键。

    • 排他锁: select * from table where 索引限制 for update 比如执行： select name from user where id=1 for update.其中 id 是主键

  • insert or update or delete 语句。 InnoDB中对修改数据相关类SQL中，会自动给涉及到的数据加上排他锁。

• 如何释放锁

  • 非事务中，语句执行完毕，立即释放锁
  • 行锁在事务中，只有等当前事务进行了commit or rollback操作才能释放锁。

• 查看当前锁的状态可以通过SQL语句 ： show engine innodb status\G; 查看。

1.4 一致性非锁定读 VS 一致性锁定读

1.4.1 一致性非锁定读

一致性的非锁定读是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。 如果读取的行的时候有正在执行的 Delete 或者 Update 操作，这时读取操作不会等待行上锁的释放，而是InnoDB引擎会去读取行的一个快照数据。

图片来自于《MySQL技术内幕第2版》

可以得知一致性非锁定读机制大大提升了数据库的并发性，这也是InnoDB默认的读取方式，即读取不会占用和等待表上的锁。但不同事务隔离级别下，读取的方式不同，对快照的定义也不同，一个行记录可能有多个快照数据，一般称这种技术为行多版本技术，由此带来的并发控制，称之为多版本并发控制（MVCC）

事务隔离级别 READ-COMMITTED vs REPEATABLE-READ

REPEATABLE-READ 是InnoDB默认的事务隔离级别，REPEATABLE-READ 对于快照数据，非一致性读总是读取事务开始时的行数据版本。

READ-COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据.

我们来举例看看，开启2个终端，可以通过下面命令开始事务会话：

start transaction;` or `begin;` or `set autocommit=0`
复制代码

通过 select @@tx_isolation\G; 可以查看事务隔离级别，先来看看 REPEATABLE-READ 的情况， 在SessionA 和 SessionB中，总共执行了6步，先执行1和2，都能查到id=2的内容，然后再SessionA中执行update操作，将id=2改为3，如果不执行commit操作，无论是REPEATABLE-READ or READ-COMMITTED ，都是能查到id=2的内容，但如果commit之后，REPEATABLE-READ还是可以继续查看id=2的内容，演示数据如下 1-2-3-4-5-6 顺序。

Session A:

mysql> begin;       
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: REPEATABLE-READ

mysql> select * from user where id=2;     # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.01 sec)

mysql> update user set id=3 where id=2;   # 3
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;                          # 5
Query OK, 0 rows affected (0.00 sec)
复制代码

Session B:

mysql> select * from user where id=2;    # 2
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=2;    # 4
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=2;   # 6
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)
复制代码

再来事务隔离级别为READ-COMMITTED的情况：

可以通过命令 set session transaction isolation level read committed; 修改会话级的事务隔离级别。

如下面顺序 1-2-3-4-5-6，可以看出READ-COMMITTED下，SessionA commit之后，SessionB就更改了。

Session A

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=4;      # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.01 sec)

mysql> update user set id=3 where id=4;   # 3
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;                          # 5
Query OK, 0 rows affected (0.00 sec)
复制代码

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

mysql> select * from user where id=4;        # 2
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=4;       # 4
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=4;     # 6
Empty set (0.00 sec)
复制代码

1.4.2 一致性锁定读

默认情况下，InnoDB是一致性非锁定读，如果有些业务场景需要显式的对数据库读取操作进行加锁以保证数据逻辑的一致性。这就需要进行加锁了，加锁方式上面描述共享锁和排他锁的时候已经提到过，这里不再重复。

select ... for update 和 select ... lock in share mode

下面演示一下： 顺序是 1-2-3-4，加锁的前提是必须在一个事务中，所以开始一个事务，然后进行加共享锁，如果未进行commit， SessionB执行update操作则会等待，等待的时候默认是50s，可以查看相关 mysql 配置，如果再超时之前，SessionA执行了commit操作，则SessionB会马上执行成功。

Session A:

ysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=3 lock in share mode; # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  3 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> commit;                          # 3
Query OK, 0 rows affected (0.00 sec)

复制代码

Session B:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set id=4 where id=3;   # 2 
# 等待

ysql> update user set id=4 where id=3;    # 4
Query OK, 1 row affected (18.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0
复制代码

1.4 锁的算法

• RecordLock: 表示单个行记录上的锁，会去锁定索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，则InnoDB会去使用隐式的主键来锁定。
• Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock: GapLock + RecordLock 的结合，锁定一个范围，并记录范围本身。

举例，索引有10,11,13,20这四个值。

• InnoDB使用Record Lock将10，11，13，20四个索引锁住，
• InnoDB使用Gap Lock将(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五个范围区间锁住，
• InnoDB使用Next-Key Lock锁住的区间有为(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)。

InnoDB默认 REPEATABLE-READ 事务隔离下，是使用的是Next-Key Lock算法。但是如果出现查询的列式唯一索引的情况下，会发生降级。比如： select * from user where id=3

则只会锁定id=3这一行，即降级为Record Rock算法。 如果是辅助索引，则情况会有所不同，举例解释一下，这里有点绕。

CREATE TABLE z (a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;
复制代码

执行上面语句，会创建一个z表，同时数据库里有如下数据

mysql> select * from z;
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  3 |    1 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
5 rows in set (0.00 sec)
复制代码

现在开启一个会话A和B。

Session A:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where b=3 for update; # 1 给辅助索引b=3加上X锁
+---+------+
| a | b    |
+---+------+
| 5 |    3 |
+---+------+
复制代码

给辅助索引b=3加上X锁之后，由于使用的Next-Key Lock算法，并且有涉及到a=5的主键索引，会首先对 a=5 进行Record Lock锁定，然后对b=3进行Next-Key Lock锁定，即锁定(1, 3]。需要特别注意的是，InnoDB还会对辅助索引的下一个键（6）加上Gap Lock锁，即锁定(3, 6)。

所以如果再SessionB中执行下面语句会是等待吗？

Session B:

select * from z where a=5 lock in share mode; #2

insert into z select 4, 2; #3

insert into z select 6, 5; #4

insert into z select 8, 6; #5
复制代码

执行2，发现需要等待，原因是a=5索引已经被加上了X锁。 执行3，主键写入4没有问题，但辅助索引2是在锁定的范围（1，3）中。 执行4，主键写入6没有问题，但辅助索引5是在锁定的范围（3，6）中。 执行5，主键8和辅助索引6均没有问题，可以写入。

说了这么多，接下来说一下如何关闭Gap Lock。

• 将事务隔离级别改为READ-COMMITTED。
• 将参数innodb_locks_unsafe_for_binlog设置为1。

所以将隔离级别设置为READ-COMMITTED要谨慎。关闭GapLock之后，除了外键约束和唯一性检查还需要GapLoc，其余情况仅使用RecordLock进行锁定。这样设置会破坏事务的隔离性。下面来说一下这个问题。

• Phantom Problem

先来认识一个名词： Phantom Problem，幻像问题。Innodb存储引擎采用Next-Key Lock算法就是为了避免Phantom Problem。

Phantom Problem是指同一个事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行。

举例分别在SessionA 和 SessionB中按顺序1-2-3-4执行。

Session A:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

mysql> select * from z where a>2 for update;  # 1
+----+------+
| a  | b    |
+----+------+
|  3 |    1 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from z where a>2 for update; # 4 
+----+------+
| a  | b    |
+----+------+
|  3 |    1 |
|  4 |    0 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
5 rows in set (0.00 sec)

复制代码

Session B:

mysql> insert into z select 4, 0;   # 2
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec) # 3
复制代码

结果发现SessionA中，事务还没结束，执行1和4返回的结果不一样，这样就是违法了事务的隔离性。

如果使用事务隔离级别为： REPEATABLE-READ。会使用Next-Key Lock算法，则上面执行1则会锁定（2，+∞），从而第2步会等待，进而避免了Phantom Problem问题。

说了这么多，总结几点InnoDB默认下的几种情况：

• 在没有索引条件查询时，InnoDB 会锁定表中的所有记录。
• 使用了主键索引，InnoDB会锁住主键索引；使用辅助索引时，InnoDB会锁住辅助索引，也会锁定主键索引。且不仅会锁住辅助索引值所在的范围，还会将其下一个辅助索引加上Gap LOCK。
• 当查询只使用唯一索引时， InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住该行索引。
• InnoDB默认事务隔离级别是REPEATABLE-READ，只有在该隔离下使用Next-Key Lock算法机制， 目的是避免Phantom Problem（幻像问题）。

1.5 锁带来的问题

锁机制虽然可以实现事务的隔离性要求，使得事务可以并发的工作，不过也会带来几个潜在的问题。

1.5.1 脏读

脏读是指不同事务下， 当前事务可以读到另外事务未提交的数据。这个一般生产环境很少遇到，且只会发生在事务隔离级别为READ-UNCOMMITTED的情况下，这种事务隔离设置很少见。具体演示，感兴趣的可以试一下。

1.5.2 不可重复读

不可重复读是指一个事务内多次读取同一数据集合，得到数据结果不一样。与脏读的区别是，脏读读取到未提交的数据，而不可重复读读取到了已经提交的数据，但是违反了数据库事务一致性的要求，当前事务未结束，前后两次相同查询得到了不一样的结果。

这种情况上面已经有演示过，当事务隔离级别是READ-COMMITTED,则会发生这种情况。

1.5.3 丢失更新

丢失更新就是一个事务的更新操作会被另外一个事务的更新操作所覆盖，从而导致数据的不一致。 比如：

• 事务T1将行记录r更新为v1，但是事务T1并未提交。
• 与此同时，事务T1将行记录r更新为v2，事务T2未提交。
• 事务T1提交
• 事务T2提交

上面举例理论上在MySQL的事务隔离级别，都不会发生丢失更新，因为对行进行更新操作，都会对行继续加锁，所以第2步并不会执行成功，而是会阻塞，等待事务T1提交。

但丢失更新在生产环境是会发生的，出现在下面的情况：

1. 事务T1查询到r行数据，放入本地内容，并显示给用户User1。
2. 事务T2也查询到r行数据，并将取得的数据显示给用户User2。
3. User1修改这行记录，更新数据库提交。
4. User2修改这行记录，更新数据库提交。

导致这个问题，并不是因为数据库本身的问题，而是在多用户系统环境下，高并发读取信息都有可能会产生这个问题。比如容易发生在账单金额方面的场景。 要避免此类丢失更新发送，则需要事务在这种情况下的操作变成串行化，而不是并行操作。需要再1)中用户读取的记录加上一个排他锁（X锁），这样2）则读的时候需要等待1）3）事务结束才可以读到。从而避免了丢失更新的问题。

1.6 死锁

接下来，来看看死锁问题。 死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

一般比较简单的解决死锁的问题是超时，当两个事务互相等待时，当一个等待时间超过设置的阀值时，则该事务进行回滚，另一个等待的事务则继续进行。可以通过innodb_lock_wait_timeout来设置超时的时间。

除了超时机制，数据库普通采用等待图(wait-for graph)的方式来进行死锁检测，Innodb采用的是这种方式来进行死锁检测。

wait-for graph需要2个信息：

• 锁的信息链表
• 事务等待链表 通过上面链表可以构造一张图，如果图上存在回路，则代表存在死锁。

举例：

图中有t1,t2,t3,t4 4个事务，事务t1需要等待t2中row1的资源，则wait-for graph有节点t1指向t2。事务t2又需要等待t1,t4的资源，事务t3需要等待t1,t4,t2的资源，从而构成以下wait-for graph

可以看见t1和t2之间形成回路，从而存在死锁。

死锁实际举例:

A	B
select * from user where id=2 for update;	begin
	select * from user where id=8 for update;
select * from user where id=8 for update; (等待)
	select * from user where id=2 for update; \ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如上表，SessionA 先给id=2行加上X锁，SessionB则给id=8加上X锁，SessionA也准备想给id=8加上X锁，从而处于等待中，需要等待SessionB是否id=8的锁，SessionB在未是否id=8的锁之前，又想给id=2加上X锁，从而使SessionA和SessionB互相等待，出现死锁。

2. 秒杀系统中数据库层面如何防止超买超卖

上面讲了那么一大堆锁相关的知识，接下来来看看了解这些知识有什么用。 常见的一个场景，秒杀系统。双11或者电商抢购的时候，经常是多用户抢购一个商品，库存肯定是很有限的，如何控制库存不让出现超买超卖，以防止造成不必要的损失。

仔细想想，其实跟上面锁知识中描述的丢失更新类似，假设库存只剩下一个，如果查询的时候不加任何锁，也不开启事务。同时a、b、c三个用户读到了这一个库存，然后程序也均通过了，a、b、c用户付款后，依次更新数据库的库存，这时候发现库存出现负值，造成商家的损失。

如何避免了？

如果显式的给查询的时候加上S锁(共享锁)，有用吗？显然根据上面的锁知识得知，还是会出现，因为共享锁跟共享锁是兼容了，可以都读取，只是不能写入。这样a、b、c还是会都读到最后一个库存。

所以只能使用排他锁了(X锁)。

总结如下：

• 开始事务。
• 查询库存，并显式的设置排他锁，通过 SELECT * FROM table_name WHERE … FOR UPDATE。
• 生成订单。
• 去库存，会隐式的设置排他锁，因为update操作，Innodb会默认设置。通过 UPDATE products SET count=count-1 WHERE id=1。
• commit，释放锁。

如果不开启事务，读取结束后就会是否锁，所以一定要先开启事务。

当然这样加锁，高并发的情况，实际生产环境不会这么做，大量的数据库读写对性能和DB都有很大的压力。实际过程中，均会引入缓存、队列等来协助实现秒杀系统。这只是单纯从数据库层面进行分析。

这一篇文章就到这里，下一篇继续对MySQL事务继续分析了解。

更多精彩文章，请关注公众号『天澄技术杂谈』