MySQL-8 新语法 nowait 与 skip locked 优化并发写入性能

背景

其实 nowait 和 skip locked 这两个新特性已经出来好久了；之所以现在才写，是因为最近用他们帮朋友解决了一个生产问题。也算是体验了一下其价值，在这里安利一下。

还是说正事。朋友的业务是一套类似于库存管理的系统，其中最关键的一张表差不多是这样设计的。

create table item(
    id bigint not null primary key auto_increment,
    name varchar(256) default '' not null comment '商品名',
    counts int default 0 comment '库存数量',
    unique index uidx_name(name)
);

加库存：假设采购了 100 瓶“农夫山泉”，加库存的时候他们的 SQL 像下面这样。

update counts = counts + 100 where name = "农夫山泉";

减库存：假设卖出一瓶“农夫山泉”，他们在减库存的时候 SQL 像下面这样。

update counts = counts - 1 
    where production_desc = "农夫山泉" and counts >= 1;

从业务逻辑上看没有问题，但是从性能上看有问题。因为同一类商品，在数据库中用一行数据表示，也就是说所有对这类商品的写操作都要取得这一行数据的“排他锁”。

设计上就导致了，同一行数据上的写操作都串行化，在业务层表现出来的就是并发上不去。

现在我们从技术反推对业务场景的影响。从上面的 SQL 可以看到 “加库存” 和 “减库存 ” 都是 update 语句，这就导致这两个场景会有冲突；更要命的是两个“减库存”的场景也会相互冲突(同样的原因)。

调整表结构

以前的设计是整个“农夫山泉”这类商品在数据库中表现为一行，现在把每一瓶“农夫山泉”作为独立的一行，这样加库存的 insert 语句和减库存的 update 语句就不会冲突了。新的表结构看起来就像这样。

create table item(
    id bigint not null primary key auto_increment,
    name varchar(256) default '' not null comment '商品名',
    state varchar(8) default 'on-sale' comment 'on-sale | sold',
    index idx_name_state(name,state)
);

加库存：

insert into item(name,state) values('农夫山泉', 'on-sale');

减库存：

-- 开始事务
-- 排他锁定库存
select id from item 
    where name = '农夫山泉' and state = 'on-sale' 
    limit xxx for update ;
-- 如果库存是够的那么就减库存,如果库存不够后面这条 update 就不做了
update item set state = 'sold' where id in (ids)limit xxx;
-- 提交事务

这下好了，加库存和减库存的冲突已经解开了；但是减库存的 update 语句还是会相互冲突。

调整减库存的 SQL

先来分析一下减库存场景下阻塞的原因。假设减库存的事务 a 已经锁定了一行，还没有提交；这个时候事务 b 也要减库存，innodb 的行锁一行一行从前往后加，这个时候事务 b 也会扫到事务 a 已经锁定的行，并陷入等待(因为事务 a 已经锁定了，所以事务 b 要等事务 a 结束释放锁之后才能继续)。

大多数据情况下，事务 b 是白等了的。就是说当事务 b 好不容易等到事务 a 结束了，它会发现自己等了这么久的这一行数据的状态已经是 'sold'(已经卖出)。这一行对它来讲已经被上一个减库存的事务标记过了；对它没有意义，它要继续找那些还没有被锁定的数据行。

在 MySQL-8.0 之前不知道有多少像 b 事务这样的等待，"错付了"。在 MySQL-8.0 之后事务 b 只要调整一下 SQL 就能做到不用一直等待其它事务已经锁定的行；它战略性"放弃"这些其它事务锁定的行，继续往下找，找那些没有被任何事务锁定的数据行(对应 skip locked )；此外也可以直接报错退出(对应 nowait ) 这样能快速的返回，让上层应用程序更快做其它处理。

由于他的这套系统适用于“继续往下找，去找那还没有被任何事务锁定的行”。最终他们的 SQL 类似于这样。

-- 开始事务
-- 排他锁定库存，如果其它事务已经上锁，就跳过被其它事务锁定的行
select id from item 
    where name = '农夫山泉' and state = 'on-sale' 
    limit xxx for update skip locked ;
-- 如果库存是够的那么就减库存,如果库存不够后面这条 update 就不做了
update item set state = 'sold' where id in (ids)limit xxx;
-- 提交事务

问题

这种每一件商品在数据中用一行数据来表示的设计也不是没有问题；想想纺织厂造的袜子，动不动就是几亿双，这种设计早晚要把 MySQL 干死。

好的性能来自于好的设计。每件商品表现为一行数据，这样表里面的行就太多了，保存和处理都有一定的难度；每类商品表现为一行并发又上不去。

好在我们把这个两个方案拼在一起。 在这之前我们要立下一条约定，每一类商品在数据库中都表现为 100 行(所以业务逻辑要处理的事情就更多了)，这样的话表结构差不多要改成这样。

create table item(
    id bigint not null primary key auto_increment,
    name varchar(256) default '' not null comment '商品名',
    counts varchar(8) default 'on-sale' comment '逻辑库存数量',
    index idx_name(name)
);

最后他们也没有用这个方案，前面的处理方式就能解决问题了。简单&实用就是最好的设计。