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

应用 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，而且不至于应用 锁全表 这种强势做法，是以后情景性价比最高的计划。