数据表使用软删除的情况下，实现不锁表保证字段业务唯一

使用 springboot 2.4.0，OpenJDK 11

背景

软删除机制

软删除 是一种删除数据的做法，每当需要删除数据时，业务不调用 SQL 的 DELETE 语句，而是把 SQL 的某个 field 标记为已删除，如 is_deleted = 1 或 status = 'DELETED'

软删除的优势是不是真实删除这条记录，便于以后审计或者恢复这条数据等
软删除也是有缺点的，其中一个就是导致某些字段不能使用 Unique Key

项目

假设当前有项目【会籍管理】，其中包括 member_no 字段，

当 status != 'DELETED' 时, member_no 不可以重复；
当 status == 'DELETED' 时, member_no 可以重复。

有缺陷的方案

假设 member_no 要设为 100

并发极低的场景

在项目早期，我们认为记录的并发量不高，所以在同一个事务中，先检查 member_no有没有被占用，如果没有，则执行插入或更新操作，SQL 如下：

建表：

CREATE TABLE membership
(
 id bigint auto_increment
        primary key,
 member_no int not null,
 status enum('NORMAL', 'DELETED') not null
)

查询、插入、更新、删除 SQL：

SELECT COUNT(*) FROM membership WHERE member_no = 100

UPDATE membership SET member_no=100 WHERE id = 1

INSERT INTO membership (member_no, status) VALUES (100, 'NORMAL')

UPDATE membership SET status='DELETED' WHERE id = 1

但是因为 SELECT + UPDATE/INSERT 在高并发的场景下，有可能出现 A1 B1 B2 A2 的情况，如下图，所以不是稳当的做法

修补方案

由于 member_no 字段被重复的 DELETED “挡住” 导致不能使用 Unique Key，那么引入一个辅助 id——blocked_id 与 member_no组成唯一索引 unique (member_no, blocked_id)，就可以解决这个问题了。

非 DELETED 的记录，我称为活的记录，blocked_id 为 0，每当有新的活的记录出现（无论来自 INSERT 还是 UPDATE），就发挥作用拦截重复的记录
DELETED 的记录，我称为死的记录，blocked_id 为 该行 id，由于 blocked_id 不为 0，那么唯一联合索引就不会拦截新的活的记录

建表：

CREATE TABLE membership
(
 id bigint auto_increment
        primary key,
 member_no int not null,
 status enum('NORMAL', 'DELETED') not null,
 blocked_id bigint not null
)

create unique index membership_member_no_blocked_id_uindex
    on membership (member_no, blocked_id);

插入、更新：

UPDATE membership SET member_no=100 WHERE id = 1 #由于不是软删除，所以不需要改变 blocked_id

INSERT INTO membership (member_no, status, blocked_id) VALUES (100, 'NORMAL', 0)

UPDATE membership SET status='DELETED' WHERE id = 1

测试分析

情况 1：真没有 -> 有

DB 中没有 member_no 为 100 的记录，现在 INSERT 一条：

INSERT INTO membership (id, member_no, status, blocked_id) VALUES (1, 100, 'NORMAL', 0)

结果：不会拦截。

情况 2：有 -> 假没有

基于情况 1，得到 id = 1，member_no = 100 的记录，软删除它：

UPDATE membership SET status='DELETED', blocked_id=id WHERE id = 1

结果：不会拦截。

情况 3：假没有 -> 有

基于情况 2，再次插入一条 member_no 为 100 的记录：

INSERT INTO membership (id, member_no, status, blocked_id) VALUES (2, 100, 'NORMAL', 0)

结果：不会拦截。

情况 4：有A -> 有B

基于情况 3，增加一条 member_no 为 101 的记录：

INSERT INTO membership (id, member_no, status, blocked_id) VALUES (3, 101, 'NORMAL', 0)

然后，对于 id=2 的记录，尝试改为 member_no=101

UPDATE membership SET member_no=101 WHERE id = 1

由于唯一索引 (member_no, blocked_id): 101-0 已经存在，所以：

结果：会拦截。

总结

常言道：计算机科学领域的任何问题都可以通过增加一个间接的中间层来解决。

我认为也是运用到这个思想，blocked_id 本质是一个辅助 ID，它的存在破坏了 UNIQUE KEY 对 member_no 的“用力过猛”效果，同时也兼顾 DELETED 记录能在 table 中同时存在多条的现象。

这个方案还可以节约一次 SELECT，而且不至于使用锁全表这种强势做法，是当前情景性价比最高的方案。