简介

本文从『优惠券发放』问题的讨论出发，介绍几个解决方案并分析其优缺点，然后讨论一下常见的update just one unused row模式，最后聊一下 MySQL 8.0.1 的 SKIP LOCKED 和 NOWAIT 特性。

『券发放』问题

券发放，一般使用『预生成』模式。在发放前，先将券全部生成好，存在一张全表，这张表一般至少包含三个字段：

• 一个全局唯一的券码字段；
• 一个状态字段表示是否发放；
• 一个用户 ID 字段记录券发给了谁；

当用户请求领券时，会从表中选择一条状态为『未领取』的记录，将其状态置为『已领取』，用户 ID置为『领券者的 ID』，最后将券码返给用户。

假设券表结构如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE `coupon`
(
    `id`          BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `code`        VARCHAR(64) NOT NULL DEFAULT '' COMMENT '唯一劵码',
    `status`      TINYINT(10) NOT NULL DEFAULT 0 COMMENT '劵状态, 0: 未领取，1：已领取，2：已核销',
    `user_id`     BIGINT(20)  NOT NULL DEFAULT 0 COMMENT '用户 ID',
    `create_time` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_status` (`status`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='优惠券表';

券表中的数据如下：

假设领券的用户 ID 为 1000。

方法一：乐观锁-CAS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 1、选择一张券，假设选中的券码为 `c1`；
SELECT *
FROM coupon
WHERE `status` = 0
LIMIT 1;

# 2、修改券记录的状态
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000
WHERE `code` = 'c1'
  AND `status` = 0;

# 3、返回券码 `c1`

上述方法，在并发情况下可能存在问题：

由于线程一执行完第三步后，已将c1这条记录的status的值改为 1，因此线程二执行第四步会失败。所以需要重试。

这可以看做一种『乐观锁』模式，『乐观锁』不适合高并发场景。

方法二：悲观锁-SELECT FOR UPDATE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 1、开启事务（InnoDB 默认的 RR 级别）
begin;

# 2、选择一张券，假设选中的券码为 `c1`；
SELECT *
FROM coupon
WHERE `status` = 0
LIMIT 1
FOR
UPDATE;

# 3、修改券记录的状态
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000
WHERE `code` = 'c1'
  AND `status` = 0;

# 4、提交事务
commit;

# 5、返回券码 `c1`

为了解决并发的情况下某个操作失败， 可以开启一个事务，然后使用 select for update 先对记录进行加锁，然后再修改记录状态。 假设操作顺序如下：

线程二在执行第 4 步时，由于线程一已经抢到记录c1的锁，线程二会被阻塞，直到线程一提交事务（ 根据2PL，提交事务时才会释放锁）；线程二在 7 步获取锁后，由于记录c1的status的值已被线程一改为 1，所以此时查询到的记录是c2。

方法二虽然可以保证券发放不会失败，但在高并发下，会有大量线程阻塞。

方法三：使用 LAST_INSERT_ID 优化锁的持有时间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 1、将状态为未领取的一张券发放给用户
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000,
    `id`      = LAST_INSERT_ID(`id`)
WHERE `status` = 0
LIMIT 1;

# 2、查询步骤 1 修改的券的 ID，假设 id 为 1
SELECT LAST_INSERT_ID();

# 3、查询券码，假设券码值为 `c1`
SELECT `code`
FROM coupon
WHERE id = 1;

# 4、返回券码 `c1`

方法二中，根据 2PL（两阶段锁协议），锁的持有时间从 select for update 一直持续到 commit。 通过使用 LAST_INSERT_ID 可以避免开启事务，锁的持有时间非常短。

不过这个方法没法避免锁争抢，因为根据 InnoDB 的锁机制，在扫描到满足条件的记录时会进行加锁(由于 idx_status 是非唯一索引，因此会加 next-key 锁) ，所以并发执行时仍然会存在阻塞的情况。

这其实就是经典的**『热点数据更新』**问题。

此外，要这个方法是 multi-user safe 的，所以在并发情况下也是安全的:

It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.

see MySQL 8.0 Reference Manual

最后，还可以对上面的 SQL 进一步优化，使得只需执行 2 次 SQL：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 1、将状态为未领取的一张券发放给用户
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000,
    `id`      = LAST_INSERT_ID(`id`)
WHERE `status` = 0
LIMIT 1;

# 2、查询券码
SELECT `code`
FROM coupon,
     (SELECT LAST_INSERT_ID() id) AS t
WHERE coupon.id = t.id;

# 3、返回券码 `c1`

方法四：使用 SKIP LOCKED 避免锁争抢

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 1、开启事务（InnoDB 默认的 RR 级别）
begin;

# 2、是用 `SELECT FOR UPDATE SKIP LOCKED` 选择一条券
SELECT *
FROM coupon
WHERE `status` = 0
LIMIT 1
FOR
UPDATE SKIP LOCKED;

# 3、修改券记录的状态
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000
WHERE `code` = 'c1'
  AND `status` = 0;

# 4、提交事务
commit;

# 5、返回券码 `c1`

假设操作顺序如下：

不同于方法二，线程二在执行第 4 步时，不会阻塞在记录c1上，而是会跳过这条记录，直接对下一条记录c2进行加锁。

这个方法，锁的持有时间与方法二相同，但避免了争抢锁，也能达到一个很好的性能。

注意，SKIP LOCKED 是 MySQL 8.0.1 引入的新特性。

方法五：使用 Redis 减少 SQL 的执行次数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 0、初始化 Redis: 在活动开始前，先从数据库中查询所有未发放券的券码，放入 Redis 中的队列中
LPUSH coupon_list_key (SELECT code from coupon WHERE status = 0)

# 1、发放时，从 Redis 队列中移除并返回一个券码，假设值为`c1`
LPOP coupon_list_key

# 2、修改券记录的状态
UPDATE coupon
SET `status`  = 1,
    `user_id` = 1000
WHERE `code` = 'c1'
  AND `status` = 0;

# 3、返回券码 `c1`

使用 Redis，既能保证正确性，也有更好的性能，因为：

• Redis 是单线程执行命令的，因此从 Redis 返回的券码是唯一的，执行第 2 步不会争抢锁也不会失败；
• LPOP 命令是从队列头移除一个元素，时间复杂度仅为 O(1)；
• 仅需要执行一条 UPDATE 语句；

除此之外，还可以通过引入 MQ 将 UPDATE 语句改为异步执行，进一步提高并发度。

不过，引入 Redis 会使得系统变得更复杂，因为：

• 需要额外编写一个模块用来初始化 Redis；
• 在初始化 Redis 要特别小心，不能将『已发放』的券放入 Redis；
• 从 Redis 出队成功，但写数据库失败，会导致券码被浪费，需要回滚 Redis；
• 回滚 Redis 可能实现起来比较复杂，因为将券码放回 Redis 时可能失败，需要重试机；
• Redis 主从同步可能存在延迟，此时主从切换可能导致 Redis 返回重复的券码，导致方法失败，需要重试机制；

小节

• 方法一和方法二不适合高并发场景，前者失败率高，后者性能特别差；
• 方法三持有锁的时间很短，性能比较好，实现也很简单，但存在热点数据更新问题；
• 方法四持有锁的时间稍长，因为锁的是不同的记录，因此不存在热点数据，性能比较好，实现也简单，但只能在 MySQL 8.0.1 及以上才能使用；
• 方法五引入 Redis，在性能上有最大的潜力，但实现起来更复杂；

方法三、四、五孰优孰劣，留待实践进行检验。

Update just one unused row

仅更新一行未使用的数据，是一种非常常见的场景，看似非常容易解决：

1
2
3
4
5

# 乐观锁 CAS 更新
UPDATE record
SET `status` = 'used'
WHERE `status` = 'unused'
LIMIT 1;

但当我们需要获取被更新数据的 ID 时，问题瞬间变得复杂起来。

获取变更 ID，除了上面介绍的使用悲观锁SELECT FOR UPDATE和LAST_INSERT_ID两个方法外，还可以通过临时变量来实现：

SET @update_id := 0; UPDATE some_table SET column_name = ‘value’, id = (SELECT @update_id := id) WHERE some_other_column = ‘blah’ LIMIT 1; SELECT @update_id;

详情见：How to get ID of the last updated row in MySQL?:

这种方法在需要一次更新多条记录时特别有用。

MySQL 8.0.1 SKIP LOCKED and NOWAIT

推荐一篇文章：MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows 这篇文章是在 2017 年 4 月 12 由 Martin Hansson 发布在 MySQL 官方博客上的。它主要是通过一个订票系统，讲解 SKIP LOCKED 和 NOWAIT 的使用方法。

另外，需要特别注意：

Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.

Reference

• https://dba.stackexchange.com/questions/131051/update-just-one-unused-row
• https://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql
• https://gist.github.com/PieterScheffers/189cad9510d304118c33135965e9cddb
• https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
• https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html