sql - 如何保证带有子查询的原子 SQL 插入？

Question

给定一个这样的简化表结构:

 CREATE TABLE t1 (
        id INT,
        num INT,
        CONSTRAINT t1_pk
        PRIMARY KEY (id),
        CONSTRAINT t1_uk
        UNIQUE (id, num)
    )

我可以使用这样的子查询来插入记录而不引起竞争条件吗？

INSERT INTO t1 (
    id,
    num
) VALUES (
    1,
    (
        SELECT MAX(num) + 1
        FROM   t1
    )
)

或者子查询不是原子的？我担心同时 INSERT 会为 num 获取相同的值，然后导致违反唯一约束。

Answer 1

是的，这肯定会产生竞争条件，因为虽然所有语句都保证是原子的，但这并不要求它们在查询执行的各个部分期间对不变的数据集进行操作。

一位客户提交了您的上述查询。只要引擎找到 MAX(num)虽然只持有与其他读取器兼容的锁，但另一个客户端可以找到相同的 MAX(num)在 INSERT 之前执行。

据我所知，解决这个问题有四种方法:

1. 使用 sequence . 在 INSERT你可以做sequencename.nextval返回下一个要插入的唯一编号。

   SQL> create sequence t1num;
   
   Sequence created.
   
   SQL> select t1num.nextval from dual;
   
      NEXTVAL
   ----------
            1
   
   SQL> select t1num.nextval from dual;
   
      NEXTVAL
   ----------
            2
   

2. 失败时重试。 我读到了一篇可信的文章，该文章介绍了一个每秒事务数非常高的系统，该系统的场景与此不完全相同，但遭受相同的竞争条件INSERT可能使用了错误的值。他们发现，首先给出 num 可以达到最高的 TPS。唯一约束，然后正常进行，如果 INSERT由于违反唯一性约束而被拒绝，客户端将简单地重试。

3. 添加一个锁定提示，强制引擎阻止其他读取器，直到 INSERT完成。虽然这在技术上可能很容易，但它可能不适合高并发。如果 MAX()单次搜索执行，阻塞时间不长，不会阻塞很多客户端，理论上可以接受，但大多数系统会随着时间的推移而增长，很快就会出现这种风险。

4. 使用单独的单行辅助表来记录 num 的下一个/最近的值. 执行同时读取和UPDATE在辅助表上，然后将读取的值分别用于 INSERT到主 table 。在我看来，这有点令人烦恼，因为它不是单个查询，而且它确实存在如果客户端设法“保留” num 的值的问题。 ，但由于任何原因未能实际执行 INSERT ，则 num 的值中可能会出现间隙在表格中。