如何保证带有子查询的原子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
    )
)

还是子查询不是原子的？我担心同时INSERTs获取相同的值num，然后导致唯一约束冲突。

Answer 1

是的，这肯定可以创建竞争条件，因为尽管保证所有语句都是原子的，但这并不要求它们在查询执行的各个部分中对不变的数据集进行操作。

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

我知道有四种方法可以解决此问题：

1. _使用 序列。_在中，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可能使用错误值的相同竞争条件下。他们发现，最高的TPS是通过首先赋予num唯一约束，然后照常进行的，如果INSERT由于违反唯一约束而被拒绝，则客户只需重试即可。

3. 添加一个锁定提示，以强制引擎阻止其他阅读器，直到INSERT完成。尽管从技术上讲这可能很容易，但它可能不适合高并发性。如果MAX()仅通过一次搜索执行，并且阻塞时间不长并且不会阻塞许多客户端，则从理论上讲是可以接受的，但是大多数系统会随着时间的推移而增长，从而迅速带来这种风险。

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