超卖问题（图解 + 秒懂 + 史上最全）

前言

先来就库存超卖的问题作描述：一般电子商务网站都会遇到如团购、秒杀、特价之类的活动，而这样的活动有一个共同的特点就是访问量激增、上千甚至上万人抢购一个商品。然而，作为活动商品，库存肯定是很有限的，如何控制库存不让出现超买，以防止造成不必要的损失是众多电子商务网站程序员头疼的问题，这同时也是最基本的问题。

在秒杀系统设计中，超卖是一个经典、常见的问题，任何商品都会有数量上限，如何避免成功下订单买到商品的人数不超过商品数量的上限，这是每个抢购活动都要面临的难点。

一、问题描述

在多个用户同时发起对同一个商品的下单请求时，先查询商品库存，再修改商品库存，会出现资源竞争问题，导致库存的最终结果出现异常。问题：

当商品 A 一共有库存 15 件，用户甲先下单 10 件，用户乙下单 8 件，这时候库存只能满足一个人下单成功，如果两个人同时提交，就出现了超卖的问题。

https://img-blog.csdnimg.cn/20191129105250547.png

二、解决的三种方案

  • 悲观锁

通过悲观锁解决超卖

  • 乐观锁

通过乐观锁解决超卖

  • 分段执行的排队方案

通过分段执行的排队方案解决超卖

解决方案 1: 悲观锁

当查询某条记录时，即让数据库为该记录加锁，锁住记录后别人无法操作，使用类似如下语法：

1
2
3
4
5
6
7
8
9
10
11
beginTranse(开启事务)
try{
    query('select amount from s_store where goodID = 12345');
    if(库存 > 0){
        //quantity为请求减掉的库存数量
        query('update s_store set amount = amount - quantity where goodID = 12345');
    }
}catch( Exception e ){
    rollBack(回滚)
}
commit(提交事务)

问题：

注意，上面的代码容易出现死锁，采用不多。

有社群小伙伴，对死锁的的原因比较关心，这里简单分析一下。

上面的语句，可能出现死锁的简单的原因，在事务的隔离级别为 Serializable 时，假设事务 t1 通过 select 拿到了共享锁，而其他事务如果拿到了 排他锁，此时 t1 去拿排他锁的时候， 就有可能会出现死锁, 注意，这里是可能，并不是一定。实际的原因，与事务的隔离级别和语句的复杂度，都有关系。

总之，避免死锁的方式之一（稍后介绍）：为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用 SELECT … FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

解决方案：一般提前采用 select for update，提前加上写锁。

1
2
3
4
5
6
7
8
9
10
11
beginTranse(开启事务)
try{
    query('select amount from s_store where goodID = 12345   for update');
    if(库存 > 0){
        //quantity为请求减掉的库存数量
        query('update s_store set amount = amount - quantity where goodID = 12345');
    }
}catch( Exception e ){
    rollBack(回滚)
}
commit(提交事务)

1 行锁和表锁

行锁：分为 共享锁 和 排它锁。

  • 共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

上共享锁的写法：lock in share mode

例如： select math from zje where math>60 lock in share mode；

  • 排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

上排它锁的写法：for update

例如：select math from zje where math >60 for update；

死锁

  • 死锁：例如说两个事务，事务 A 锁住了 15 行，同时事务 B 锁住了 610 行，此时事务 A 请求锁住 610 行，就会阻塞直到事务 B 施放 610 行的锁，而随后事务 B 又请求锁住 15 行，事务 B 也阻塞直到事务 A 释放 15 行的锁。死锁发生时，会产生 Deadlock 错误。

表锁：不会出现死锁，发生锁冲突几率高，并发低。

表锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。但是表锁效率低。

  • 行锁：会出现死锁，发生锁冲突几率低，并发高。

使用行锁需要注意的点：

  1. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

  2. 两个事务不能锁同一个索引，例如：

1
2
3
4
5
6
7
事务A先执行：
select math from zje where math>60 for update;

事务B再执行：
select math from zje where math<60 for update；
这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，
但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。

3.insert ，delete ， update 在事务中都会自动默认加上排它锁。

实现：

会话 1：会话 2：
begin；select math from zje where math>60 for update；begin；update zje set math=99 where math=68；阻塞

2 MyISAM 与 InnoDB 的区别

介绍

**MyISAM：**MyISAM 是默认存储引擎（Mysql5.1 前），每个 MyISAM 在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。

  • .frm 文件存储表定义
  • ·MYD (MYData) 文件存储表的数据
  • .MYI (MYIndex) 文件存储表的索引

**InnoDB：**MySQL 的默认存储引擎，给 MySQL 提供了具有事务 (transaction)、回滚(rollback) 和崩溃修复能力 (crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control) 的事务安全 (transaction-safe (ACID compliant)) 型表。InnoDB 提供了行级锁(locking on row level)，提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。

MyISAM 与 InnoDB 的区别

  1. InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务；

  2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MyISAM 会失败；

  3. 聚集索引 VS 非聚集索引

    InnoDB 是聚集索引，使用 B+Tree 作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按 B+Tree 组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

InnoDB 的 B + 树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值。

https://img-blog.csdn.net/20180923094753230?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjQyMDM2/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70

但是， MyISAM 是非聚集索引，也是使用 B+Tree 作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

https://img-blog.csdn.net/20180923094753224?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjQyMDM2/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70

总结

也就是说：InnoDB 的 B + 树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而 MyISAM 的 B + 树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

  1. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何 WHERE 条件）；

那么为什么 InnoDB 没有了这个变量呢？
因为 InnoDB 的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此 count 统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB 会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB 还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于 InnoDB 维护的缓冲区（Buffer Pool）中，count 操作会比较费时。可以建立一个记录总行数的表并让你的程序在 INSERT/DELETE 时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试 SHOW TABLE STATUS。

  1. InnoDB 不支持全文索引，而 MyISAM 支持全文索引，在涉及全文索引领域的查询效率上 MyISAM 速度更快高；PS：5.7 以后的 InnoDB 支持全文索引了
  2. MyISAM 表格可以被压缩后进行查询操作
  3. InnoDB 支持表、行 (默认) 级锁，而 MyISAM 支持表级锁

InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

1
2
3
4
5
6
7
8
9
10
例如：
    t_user(uid, uname, age, sex) innodb;

    uid PK
    无其他索引
    update t_user set age=10 where uid=1;             命中索引，行锁。

    update t_user set age=10 where uid != 1;           未命中索引，表锁。

    update t_user set age=10 where name='chackca';    无索引，表锁。
  1. InnoDB 表必须有唯一索引（如主键）（用户没有指定的话会自己找 / 生产一个隐藏列 Row_id 来充当默认主键），而 MyISAM 可以没有
  2. Innodb 存储文件有 frm、ibd，而 MyISAM 是 frm、MYD、MYI

Innodb：frm 是表定义文件，ibd 是数据文件
MyISAM：frm 是表定义文件，myd 是数据文件，myi 是索引文件

如何选择这两种引擎：

  1. 是否要支持事务，如果要请选择 innodb，如果不需要可以考虑 MyISAM；

  2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读也有写，请使用 InnoDB。

  3. 系统奔溃后，MyISAM 恢复起来更困难，能否接受；

  4. MySQL5.5 版本开始 Innodb 已经成为 Mysql 的默认引擎 (之前是 MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用 InnoDB，至少不会差。

InnoDB 为什么推荐使用自增 ID 作为主键？

答：自增 ID 可以保证每次插入时 B + 索引是从右边扩展的，可以避免 B + 树和频繁合并和分裂（对比使用 UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

InnoDB 引擎的 4 大特性

插入缓冲（insert buffer), 二次写 (double write), 自适应哈希索引 (ahi), 预读 (read ahead)

3 事务与死锁

在 MySQL 的 InnoDB 中，预设的 Tansaction isolation level 为 REPEATABLE READ(可重读)

在 SELECT 的读取锁定主要分为两种方式：

  1. SELECT … LOCK IN SHARE MODE
  2. SELECT … FOR UPDATE

这两种方式在事务 (Transaction) 进行当中 SELECT 到同一个数据表时，都必须等待其它事务数据被提交(Commit) 后才会执行。

而主要的不同在于共享锁 (lock in share mode) 在有一方事务要 Update 同一个表单时很容易造成死锁。

简单的说，如果 SELECT 后面若要 UPDATE 同一个表单，最好使用 SELECT … UPDATE。

MySQL SELECT … FOR UPDATE 的 Row Lock 与 Table Lock

上面介绍过 SELECT … FOR UPDATE 的用法，不过锁定 (Lock) 的数据是判别就得要注意一下了。由于 InnoDB 预设是 Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行 Row lock (只锁住被选取的数据) ，否则 MySQL 将会执行 Table Lock (将整个数据表单给锁住)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
举个例子:
假设有个表单 products ，里面有 id 跟 name 二个栏位，id 是主键。

例 1: (明确指定主键，并且有此数据，row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;

例 2: (明确指定主键，若查无此数据，无 lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;

例 2: (无主键，table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例 3: (主键不明确，table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例 4: (主键不明确，table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

4 淘宝是如何使用悲观锁的

那么后端的数据库在高并发和超卖下会遇到什么问题呢？主要会有如下 3 个问题：（主要讨论写的问题，读的问题通过增加 cache 可以很容易的解决）

  1. 首先 MySQL 自身对于高并发的处理性能就会出现问题，一般来说，MySQL 的处理性能会随着并发 thread 上升而上升，但是到了一定的并发度之后会出现明显的拐点，之后一路下降，最终甚至会比单 thread 的性能还要差。
  2. 其次，超卖的根结在于减库存操作是一个事务操作，需要先 select，然后 insert，最后 update -1。最后这个 - 1 操作是不能出现负数的，但是当多用户在有库存的情况下并发操作，出现负数这是无法避免的。
  3. 最后，当减库存和高并发碰到一起的时候，由于操作的库存数目在同一行，就会出现争抢 InnoDB 行锁的问题，导致出现互相等待甚至死锁，从而大大降低 MySQL 的处理性能，最终导致前端页面出现超时异常。

针对上述问题，如何解决呢？ 我们先看眼淘宝的高大上解决方案：

关闭死锁检测以提高并发处理性能

在一个高并发的 MySQL 服务器上，事务会递归检测死锁，当超过一定的深度时，性能的下降会变得不可接受。Facebook 早就提出了禁止死锁检测。

我们做了一个实验，在禁止死锁检测后，TPS 得到了极大的提升，如下图所示：

禁止死锁检测后，即使死锁发生，也不会回滚事务，而是全部等待到超时。

MySQL 的 innobase_deadlock_check 是在 InnoDB 里新加的系统变量，用于控制是否打开死锁检测。

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。可以认为如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。

InnoDB 的并发写操作会触发死锁，InnoDB 也提供了死锁检测机制，可以通过设置 innodb_deadlock_detect 参数来打开或关闭死锁检测：

  • innodb_deadlock_detect = on 打开死锁检测，数据库发生死锁时自动回滚（默认选项）。
  • innodb_deadlock_detect = off 关闭死锁检测，发生死锁时，用锁超时来处理。通过设置锁超时参数 innodb_lock_wait_timeout 可以在超时发生时回滚被阻塞的事务。

设置 MySQL 事务锁超时时间 innodb_lock_wait_timeout

MySQL 数据库采用 InnoDB 模式，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50 秒，一旦数据库锁超过这个时间就会报错。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_lock_wait_timeout=120;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120   |
+--------------------------+-------+

1 row in set (0.00 sec)

设置 InnoDB Monitors 方法

还可以通过设置 InnDB Monitors 来进一步观察锁冲突详细信息

建立 test 库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql>create database test;
Query OK, 1 row affected (0.20 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table innodb_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (1.04 sec)

mysql> create table innodb_tablespace_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.70 sec)

mysql> create table innodb_lock_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.36 sec)

mysql> create table innodb_table_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

可以通过 show engine innodb status 命令查看死锁信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> show engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-05-10 09:17:10 0x7f1fbc21a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 53 srv_active, 0 srv_shutdown, 240099 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2007
OS WAIT ARRAY INFO: signal count 1987
RW-shared spins 3878, rounds 5594, OS waits 1735
RW-excl spins 3, rounds 91, OS waits 4
RW-sx spins 1, rounds 30, OS waits 1
Spin rounds per wait: 1.44 RW-shared, 30.33 RW-excl, 30.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 78405
Purge done for trx's n:o < 78404 undo n:o < 10 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421249967052640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
.............................................................................
.............................................................................
.............................................................................

II：请求排队

修改源代码，将排队提到进入引擎层前，降低引擎层面的并发度。

如果请求一股脑的涌入数据库，势必会由于争抢资源造成性能下降，通过排队，让请求从混沌到有序，从而避免数据库在协调大量请求时过载。

请求排队：如果请求一股脑的涌入数据库，势必会由于争抢资源造成性能下降，通过排队，让请求从混沌到有序，从而避免数据库在协调大量请求时过载。

III：请求合并（组提交）

请求合并（组提交），降低 server 和引擎的交互次数，降低 IO 消耗。

甲买了一个商品，乙也买了同一个商品，与其把甲乙当做当做单独的请求分别执行一次商品库存减一的操作，不如把他们合并后统一执行一次商品库存减二的操作，请求合并的越多，效率提升的就越大。

实操建议

不过结合我们的实际，死锁监测可以关闭，但是，改 mysql 源码这种高大上的解决方案显然有那么一点不切实际。

InnoDB 锁定模式及实现机制

考虑到行级锁定均由各个存储引擎自行实现，而且具体实现也各有差别，而 InnoDB 是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下 InnoDB 的锁定特性。

总的来说，InnoDB 的锁定机制和 Oracle 数据库有不少相似之处。InnoDB 的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB 也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB 的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是 InnoDB 自动加的，不需用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

1
2
共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE

用 SELECT … IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT… FOR UPDATE 方式获得排他锁。

间隙锁（Next-Key 锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；

对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP)”，InnoDB 也会对这个“间隙” 加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。

例：

假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,…,100,101，下面的 SQL：

1
mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101（这些记录并不存在）的 “间隙” 加锁。

InnoDB 使用间隙锁的目的：

（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了 empid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

（2）为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

除了间隙锁给 InnoDB 带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：

（1）当 Query 无法利用索引的时候，InnoDB 会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

（2）当 Query 使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该 Query 的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

（3）当 Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁。

并发事务有什么什么问题？应该如何解决？

并发事务可能造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下：

  • 加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 提供数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取，从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

什么是 MVCC？

MVCC 全称是多版本并发控制系统，InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决幻读问题。

MVCC 是怎么工作的？

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动新增，事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下 MVCC 如何工作？

  • SELECT：InnoDB 会根据以下条件检查每一行记录：第一，InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的。第二，行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
  • INSERT：InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。
  • DELETE：InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。
  • UPDATE：InnoDB 为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识保存这两个版本号，使大多数操作都不用加锁。它不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

快照读和当前读

在 mysql 中 select 分为快照读和当前读，执行下面的语句

1
select * from table where id = ?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为 Serializable 中不成立）

select 加锁分析

下面六句 Sql 的区别呢

1
2
3
4
5
6
select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update

在不同的事务隔离级别下，是否加锁，加的是共享锁还是排他锁，是否存在间隙锁，您能说出来嘛？

要回答这个问题，先问自己三个问题

  • 当前事务隔离级别是什么
  • id 列是否存在索引
  • 如果存在索引是聚簇索引还是非聚簇索引呢？

关于 mysql 的索引，啰嗦一下：

  • InnoDB 一定存在聚簇索引，默认以主键作为聚簇索引
  • 有几个索引，就有几棵 B + 树 (不考虑 hash 索引的情形)
  • 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引 B + 树。

锁类型

  • 共享锁 (S 锁): 假设事务 T1 对数据 A 加上共享锁，那么事务 T2 可以读数据 A，不能修改数据 A。
  • 排他锁 (X 锁): 假设事务 T1 对数据 A 加上排他锁 ，那么事务 T2 不能读数据 A，不能修改数据 A。
    我们通过 update、delete 等语句加上的锁都是行级别的锁。只有 LOCK TABLE … READ 和 LOCK TABLE … WRITE 才能申请表级别的锁。
  • 意向共享锁 (IS 锁): 一个事务在获取（任何一行 / 或者全表）S 锁之前，一定会先在所在的表上加 IS 锁。
  • 意向排他锁 (IX 锁): 一个事务在获取（任何一行 / 或者全表）X 锁之前，一定会先在所在的表上加 IX 锁。

意向锁存在的目的?

这里说一下意向锁存在的目的。假设事务 T1，用 X 锁来锁住了表上的几条记录，那么此时表上存在 IX 锁，即意向排他锁。那么此时事务 T2 要进行 LOCK TABLE … WRITE 的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

  • Record Locks：简单翻译为行锁吧。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，InnoDB 一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！
  • Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在 Read Committed 隔离级别下，不会使用间隙锁。

这里我对官网补充一下，隔离级别比 Read Committed 低的情况下，也不会使用间隙锁，如隔离级别为 Read Uncommited 时，也不存在间隙锁。当隔离级别为 Repeatable Read 和 Serializable 时，就会存在间隙锁。

  • Next-Key Locks：这个理解为 Record Lock + 索引前面的 Gap Lock。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13 和 20。那么，间隙锁的范围如下
1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

索引原理介绍

先来一张带主键的表，如下所示，pId 是主键

pIdnamebirthday
5zhangsan2016-10-02
8lisi2015-10-04
11wangwu2016-09-02
13zhaoliu2015-10-07

画出该表的结构图如下

如上图所示，分为上下两个部分，上半部分是由主键形成的 B + 树，下半部分就是磁盘上真实的数据！那么，当我们， 执行下面的语句

1
select * from table where pId='11'

那么，执行过程如下

如上图所示，从根开始，经过 3 次查找，就可以找到真实数据。如果不使用索引，那就要在磁盘上，进行逐行扫描，直到找到数据位置。显然，使用索引速度会快。但是在写入数据的时候，需要维护这颗 B + 树的结构，因此写入性能会下降！

聚簇索引、非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
  • 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

在 InnoDB 中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

  1. InnoDB 使用的是聚簇索引，将主键组织到一棵 B + 树中，而行数据就储存在叶子节点上，若使用 “where id = 14” 这样的条件查找主键，则按照 B + 树的检索算法即可查找到对应的叶节点，之后获得行数据。
  2. 若对 Name 列进行条件搜索，则需要两个步骤：第一步在辅助索引 B + 树中检索 Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引 B + 树种再执行一次 B + 树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会 隐式定义一个主键（类似 oracle 中的 RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM 使用的是非聚簇索引，非聚簇索引的两棵 B + 树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B + 树的节点存储了主键，辅助键索引 B + 树存储了辅助键。表数据存储在独立的地方，这两颗 B + 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

使用聚簇索引的优势：

  • 每次使用辅助索引检索都要经过两次 B + 树查找， 看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？
  1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了 Buffer 中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 Id 来组织数据，获得数据更快。
  2. 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次 IO 读写的缓存中没有，需要发生一次新的 IO 操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

注：我们知道一次 io 读写，可以获取到 16K 大小的资源，我们称之为读取到的数据区域为 Page。而我们的 B 树，B + 树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次 IO 操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行 IO 操作了。除非发生了页的分裂，即要查询的行数据不在上次 IO 操作的换村里，才会触发新的 IO 操作。

  1. 因为 MyISAM 的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行 I/O 读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次 I/O。（强烈的对比）
  2. 不过，如果涉及到大数据量的排序、全表扫描、count 之类的操作的话，还是 MyISAM 占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

聚簇索引需要注意的地方

当使用主键为聚簇索引时，主键最好不要使用 uuid，因为 uuid 的值太过离散，不适合排序且可能出线新增加记录的 uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。

建议使用 int 类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到 IO 操作读取到的数据量。

为什么主键通常建议使用自增 id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增 id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

四个隔离级别

我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:

  • Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
  • Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
  • Repeatable Read(RR): 可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免! 我会在下一篇文章说明! 另外就是记住从该级别才开始加入间隙锁 (这句话记下来，后面有用到)!
  • Serializable：串行化，该级别下读写串行化，且所有的 select 语句后都自动加上 lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

select 分析的表数据

为了便于说明，我来个例子，假设有表数据如下，pId 为主键索引

pId(int)name(varchar)num(int)
1aaa100
2bbb200
7ccc200

隔离级别：RC/RU ，条件列: 非索引

(1)select * from table where num = 200

不加任何锁，是快照读。

(2)select * from table where num > 200

不加任何锁，是快照读。

(3)select * from table where num = 200 lock in share mode

当 num = 200，有两条记录。这两条记录对应的 pId=2，7，因此在 pId=2，7 的聚簇索引上加行级 S 锁，采用当前读。

(4)select * from table where num > 200 lock in share mode

当 num > 200，有一条记录。这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加上行级 S 锁，采用当前读。

(5)select * from table where num = 200 for update

当 num = 200，有两条记录。这两条记录对应的 pId=2，7，因此在 pId=2，7 的聚簇索引上加行级 X 锁，采用当前读。

(6)select * from table where num > 200 for update

当 num > 200，有一条记录。这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加上行级 X 锁，采用当前读。

隔离级别：RC/RU ，条件列: 聚簇索引

大家应该知道 pId 是主键列，因此 pId 用的就是聚簇索引。此情况其实和 RC/RU + 条件列非索引情况是类似的。

(1)select * from table where pId = 2

不加任何锁，是快照读。

(2)select * from table where pId > 2

不加任何锁，是快照读。

(3)select * from table where pId = 2 lock in share mode

在 pId=2 的聚簇索引上，加 S 锁，为当前读。

(4)select * from table where pId > 2 lock in share mode

在 pId=3，7 的聚簇索引上，加 S 锁，为当前读。

(5)select * from table where pId = 2 for update

在 pId=2 的聚簇索引上，加 X 锁，为当前读。

(6)select * from table where pId > 2 for update

在 pId=3，7 的聚簇索引上，加 X 锁，为当前读。

为什么条件列加不加索引，加锁情况是一样的？

其实是不一样的。在 RC/RU 隔离级别中，MySQL 做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server 层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是 RC/RU + 条件列非索引比本例多了一个释放不符合条件的锁的过程！

隔离级别：RC/RU ，条件列: 非聚簇索引

在 num 列上建上非唯一索引。此时有一棵聚簇索引 (主键索引，pId) 形成的 B + 索引树，其叶子节点为硬盘上的真实数据。以及另一棵非聚簇索引 (非唯一索引，num) 形成的 B + 索引树，其叶子节点依然为索引节点，保存了 num 列的字段值，和对应的聚簇索引。

(1)select * from table where num = 200

不加任何锁，是快照读。

(2)select * from table where num > 200

不加任何锁，是快照读。

(3)select * from table where num = 200 lock in share mode

当 num = 200，由于 num 列上有索引，因此先在 num = 200 的两条索引记录上加行级 S 锁。接着，去聚簇索引树上查询，这两条记录对应的 pId=2，7，因此在 pId=2，7 的聚簇索引上加行级 S 锁，采用当前读。

(4)select * from table where num > 200 lock in share mode

当 num > 200，由于 num 列上有索引，因此先在符合条件的 num = 300 的一条索引记录上加行级 S 锁。接着，去聚簇索引树上查询，这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加行级 S 锁，采用当前读。

(5)select * from table where num = 200 for update

当 num = 200，由于 num 列上有索引，因此先在 num = 200 的两条索引记录上加行级 X 锁。接着，去聚簇索引树上查询，这两条记录对应的 pId=2，7，因此在 pId=2，7 的聚簇索引上加行级 X 锁，采用当前读。

(6)select * from table where num > 200 for update

当 num > 200，由于 num 列上有索引，因此先在符合条件的 num = 300 的一条索引记录上加行级 X 锁。接着，去聚簇索引树上查询，这条记录对应的 pId=3，因此在 pId=3 的聚簇索引上加行级 X 锁，采用当前读。

隔离级别：RR/Serializable，条件列: 非索引

RR 级别需要多考虑的就是 gap lock，他的加锁特征在于，无论你怎么查都是锁全表。接下来分析开始

(1)select * from table where num = 200

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 S 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

(2)select * from table where num > 200

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 S 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

(3)select * from table where num = 200 lock in share mode

在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 S 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

(4)select * from table where num > 200 lock in share mode

在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 S 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

(5)select * from table where num = 200 for update

在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 X 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

(6)select * from table where num > 200 for update

在 pId = 1,2,3,7（全表所有记录）的聚簇索引上加 X 锁。并且在聚簇索引的所有间隙 (-∞,1)(1,2)(2,3)(3,7)(7,+∞) 加 gap lock

隔离级别：RR/Serializable，条件列: 聚簇索引

大家应该知道 pId 是主键列，因此 pId 用的就是聚簇索引。该情况的加锁特征在于，如果 where 后的条件为精确查询 (= 的情况)，那么只存在 record lock。如果 where 后的条件为范围查询(> 或<的情况)，那么存在的是 record lock+gap lock。

(1)select * from table where pId = 2

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在 pId=2 的聚簇索引上加 S 锁，不存在 gap lock。

(2)select * from table where pId > 2

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在 pId=3,7 的聚簇索引上加 S 锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

(3)select * from table where pId = 2 lock in share mode

是当前读，在 pId=2 的聚簇索引上加 S 锁，不存在 gap lock。

(4)select * from table where pId > 2 lock in share mode

是当前读，在 pId=3,7 的聚簇索引上加 S 锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

(5)select * from table where pId = 2 for update

是当前读，在 pId=2 的聚簇索引上加 X 锁。

(6)select * from table where pId > 2 for update

在 pId=3,7 的聚簇索引上加 X 锁。在 (2,3)(3,7)(7,+∞) 加上 gap lock

(7)select * from table where pId = 6 [lock in share mode|for update]

注意了，pId=6 是不存在的列，这种情况会在 (3,7) 上加 gap lock。

(8)select * from table where pId > 18 [lock in share mode|for update]

注意了，pId>18，查询结果是空的。在这种情况下，是在 (7,+∞) 上加 gap lock。

隔离级别：RR/Serializable，条件列: 非聚簇索引

这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。

先说一下，唯一索引的情况。如果是唯一索引，情况和 RR/Serializable + 条件列是聚簇索引类似，唯一有区别的是: 这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！大家可以自行推敲!

下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在 record lock，还存在 gap lock。而通过唯一索引进行精确查询后，只存在 record lock，不存在 gap lock。老规矩在 num 列建立非唯一索引

(1)select * from table where num = 200

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在 pId=2，7 的聚簇索引上加 S 锁，在 num=200 的非聚集索引上加 S 锁，在 (100,200)(200,300) 加上 gap lock。

(2)select * from table where num > 200

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在 pId=3 的聚簇索引上加 S 锁，在 num=300 的非聚集索引上加 S 锁。在 (200,300)(300,+∞) 加上 gap lock

(3)select * from table where num = 200 lock in share mode

是当前读，在 pId=2，7 的聚簇索引上加 S 锁，在 num=200 的非聚集索引上加 S 锁，在 (100,200)(200,300) 加上 gap lock。

(4)select * from table where num > 200 lock in share mode

是当前读，在 pId=3 的聚簇索引上加 S 锁，在 num=300 的非聚集索引上加 S 锁。在 (200,300)(300,+∞) 加上 gap lock。

(5)select * from table where num = 200 for update

是当前读，在 pId=2，7 的聚簇索引上加 S 锁，在 num=200 的非聚集索引上加 X 锁，在 (100,200)(200,300) 加上 gap lock。

(6)select * from table where num > 200 for update

是当前读，在 pId=3 的聚簇索引上加 S 锁，在 num=300 的非聚集索引上加 X 锁。在 (200,300)(300,+∞) 加上 gap lock

(7)select * from table where num = 250 [lock in share mode|for update]

注意了，num=250 是不存在的列，这种情况会在 (200,300) 上加 gap lock。

(8)select * from table where num > 400 [lock in share mode|for update]

注意了，pId>400，查询结果是空的。在这种情况下，是在 (400,+∞) 上加 gap lock。

死锁

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

如何避免死锁？

  • 为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用 SELECT … FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的；
  • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁；
  • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会；
  • 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；
  • 在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题；
  • 当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第 1 个线程提交后，第 2 个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第 3 个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行 ROLLBACK 释放获得的排他锁

InnoDB 默认是如何对待死锁的？

InnoDB 默认是使用设置死锁时间来让死锁超时的策略，默认 innodb_lock_wait_timeout 设置的时长是 50s。

如何开启死锁检测？

设置 innodb_deadlock_detect 设置为 on 可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。

解决方案 2：乐观锁

乐观锁

乐观锁并不是真实存在的锁，而是在更新的时候判断此时的库存是否是之前查询出的库存，如果相同，表示没人修改，可以更新库存，否则表示别人抢过资源，不再执行库存更新。类似如下操作：

1
2
update tb_sku set stock=2 where id=1 and stock=7;
SKU.objects.filter(id=1, stock=7).update(stock=2)

使用乐观锁需修改数据库的事务隔离级别

使用乐观锁的时候，如果一个事务修改了库存并提交了事务，那其他的事务应该可以读取到修改后的数据值，所以不能使用可重复读的隔离级别，应该修改为读取已提交（Read committed）。

修改方式：

MySQL 事务隔离级别

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
不可重复读（read-committed）否是是
可重复读（repeatable-read）否否是
串行化（serializable）否否否

mysql 默认的事务隔离级别为 repeatable-read

并发事务会带来哪些问题？

1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

用例子说明各个隔离级别的情况

1、读未提交：

（1）打开一个客户端 A，并设置当前事务模式为 read uncommitted（未提交读），查询表 account 的初始值：

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新表 account：

（3）这时，虽然客户端 B 的事务还没提交，但是客户端 A 就可以查询到 B 已经更新的数据：

（4）一旦客户端 B 的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端 A 查询到的数据其实就是脏数据：

（5）在客户端 A 执行更新语句 update account set balance = balance - 50 where id =1，lilei 的 balance 没有变成 350，居然是 400，是不是很奇怪，数据不一致啊，如果你这么想就太天真 了，在应用程序中，我们会用 400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别。

2、读已提交

（1）打开一个客户端 A，并设置当前事务模式为 read committed（提交读），查询表 account 的所有记录：

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新表 account：

（3）这时，客户端 B 的事务还没提交，客户端 A 不能查询到 B 已经更新的数据，解决了脏读问题：

（4）客户端 B 的事务提交

（5）客户端 A 执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

3、可重复读

（1）打开一个客户端 A，并设置当前事务模式为 repeatable read，查询表 account 的所有记录。

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新表 account 并提交。

（3）在客户端 A 查询表 account 的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题。

（4）在客户端 A，接着执行 update balance = balance - 50 where id = 1，balance 没有变成 400-50=350，lilei 的 balance 值用的是步骤（2）中的 350 来算的，所以是 300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了 MVCC 机制，select 操作不会更新版本号，是快照读（历史版本）；insert、update 和 delete 会更新版本号，是当前读（当前版本）。

（5）重新打开客户端 B，插入一条新数据后提交。

（6）在客户端 A 查询表 account 的所有记录，没有 查出 新增数据，所以没有出现幻读。

4. 串行化

（1）打开一个客户端 A，并设置当前事务模式为 serializable，查询表 account 的初始值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | lilei  |   10000 |
|    2 | hanmei |   10000 |
|    3 | lucy   |   10000 |
|    4 | lily   |   10000 |
+------+--------+---------+
4 rows in set (0.00 sec)

（2）打开一个客户端 B，并设置当前事务模式为 serializable，插入一条记录报错，表被锁了插入失败，mysql 中事务隔离级别为 serializable 时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

1
2
3
4
5
6
7
8
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

补充：

1、事务隔离级别为读提交时，写数据只会锁住相应的行。

2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是 next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

3、事务隔离级别为串行化时，读写数据都会锁住整张表。

4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

乐观锁在高并发场景下的问题

乐观锁在高并发场景下的问题，是严重的空自旋。

具体可以参考 入大厂必备的基础书籍： 《Java 高并发核心编程 卷 2》

超卖解决方案 3：分阶段排队下单方案

分阶段排队下单方案的思想来源

最优的解决方案，其实思想来自于 JUC 的原理

JUC 是如何提高性能的，引入队列

原始的 CLH 队列

用于减少线程争用的最简单的队列，CLH 队列，具体可以参考 入大厂必备的基础书籍： 《Java 高并发核心编程 卷 2》

JUC 的 AQS 内部队列

AQS 内部队列是 JUC 高性能的基础，AQS 队列，具体可以参考 入大厂必备的基础书籍： 《Java 高并发核心编程 卷 2》

分阶段排队下单方案

将提交操作变成两段式：

  • 第一阶段申请，申请预减减库，申请成功之后，进入消息队列；
  • 第二阶段确认，从消息队列消费申请令牌，然后完成下单操作。 查库存 -> 创建订单 -> 扣减库存。通过分布式锁保障解决多个 provider 实例并发下单产生的超卖问题。

申请阶段

将存库从 MySQL 前移到 Redis 中，所有的预减库存的操作放到内存中，由于 Redis 中不存在锁故不会出现互相等待，并且由于 Redis 的写性能和读性能都远高于 MySQL，这就解决了高并发下的性能问题。

确认阶段

然后通过队列等异步手段，将变化的数据异步写入到 DB 中。

引入队列，然后数据通过队列排序，按照次序更新到 DB 中，完全串行处理。当达到库存阀值的时候就不在消费队列，并关闭购买功能。这就解决了超卖问题。

分阶段排队架构图

基于分段的排队执行方案的性能提升

一个高性能秒杀的场景：

假设一个商品 1 分钟 6000 订单，每秒的 600 个下单操作。

在排队阶段，每秒的 600 个预减库存的操作，对于 Redis 来说，没有任何压力。甚至每秒的 6000 个预减库存的操作，对于 Redis 来说，也是压力不大。

但是在下单阶段，就不一样了。假设加锁之后，释放锁之前，查库存 -> 创建订单 -> 扣减库存，经过优化，每个 IO 操作 100ms，大概 200 毫秒，一秒钟 5 个订单。600 个订单需要 120s，2 分钟才能彻底消化。

如何提升下单阶段的性能呢？

可以使用 Redis 分段锁。

为了达到每秒 600 个订单，可以将锁分成 600 /5 =120 个段，每个段负责 5 个订单，600 个订单，在第二个阶段 1 秒钟下单完成。

有关 Redis 分段锁的详细知识，请阅读下面的博文：

Redis 分布式锁 （图解 - 秒懂 - 史上最全）

基于分段的排队执行方案优点

解决超卖问题，库存读写都在内存中，故同时解决性能问题。

基于分段的排队执行方案缺点

  • 数据不一致的问题：

由于异步写入 DB，可能存在数据不一致，存在某一时刻 DB 和 Redis 中数据不一致的风险。

  • 可能存在少买

可能存在少买，也就是如果拿到号的人不真正下订单，可能库存减为 0，但是订单数并没有达到库存阀值。

参考文献：
https://www.cnblogs.com/rjzheng/p/9950951.html
https://blog.csdn.net/caoxiaohong1005/article/details/78292457
https://www.cnblogs.com/wyaokai/p/10921323.html

超卖问题（图解 + 秒懂 + 史上最全）
https://fulequn.github.io/2024/05/Article202405081/
作者
Fulequn
发布于
2024年5月8日
许可协议