如何保证带有子查询的原子 SQL 插入? [英] How to guarantee atomic SQL inserts with subqueries?

问题描述

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

Given a simplified table structure like this:

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

我可以使用这样的子查询来插入记录而不导致竞争条件吗?

Can I use a subquery like this for inserting records without causing a race condition?

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

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

Or are subqueries not atomic? I'm worried about simultaneous INSERTs grabbing the same value for num and then causing a unique constraint violation.

推荐答案

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

Yes, this can most certainly create a race condition, because while all statements are guaranteed atomic, this does not require them to have operated across an unchanging data set during the separate parts of the query's execution.

客户提交了您的上述查询.只要引擎找到 MAX(num) 并且只持有与其他阅读器兼容的锁，那么另一个客户端就可以在 MAX(num) 之前找到相同的 MAX(num)code>INSERT 被执行.

A client submits your above query. So long as the engine finds the MAX(num) while holding only locks that are compatible with other readers, then another client can find the same MAX(num) before the INSERT is performed.

我知道有四种方法可以解决这个问题:

There are four ways around this problem that I know of:

1. 使用序列.在INSERT 您只需执行 sequencename.nextval 即可返回要插入的下一个唯一编号.

1. Use a sequence. In the INSERT you can just do sequencename.nextval to return the next unique number to be inserted.

SQL> create sequence t1num;

Sequence created.

SQL> select t1num.nextval from dual;

   NEXTVAL
----------
         1

SQL> select t1num.nextval from dual;

   NEXTVAL
----------
         2

失败时重试.我读过一篇关于每秒事务数非常高的系统的可靠文章，该系统的场景与此不完全相同，但遭受相同的竞争条件INSERT 可能使用了错误的值.他们发现最高的 TPS 是通过首先给 num 一个唯一约束，然后正常进行，如果 INSERT 由于违反唯一约束而被拒绝，客户端只会重试.

Retry on failure. I read a credible article about a very high transactions-per-second system that had a scenario not exactly like this one but suffering from the same race condition of the INSERT possibly using the wrong value. They found that the highest TPS was achieved by first giving num a unique constraint, then proceeding as normal, and if the INSERT was rejected due to a violation of the unique constraint, the client would simply retry.

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

Add a locking hint that forces the engine to block other readers until the INSERT is completed. While this may be easy technically, it probably won't be suitable for high concurrency. If the MAX() is performed with a single seek, and the blocking is not long and does not block many clients, it could theoretically be acceptable, but most systems grow over time, quickly rendering this risky.

使用单独的一行辅助表来记录 num 的下一个/最近的值.执行同时读取和 UPDATE，然后分别使用读取的值INSERT到主表.在我看来，这不是一个单一的查询有一些烦恼，而且它确实有一个问题，如果客户端设法保留"一个 num 的值，但由于任何原因实际上失败了执行INSERT，则表中num的值会出现缺口.

Use a separate one-row helper table to record the next/most recent value for num. Perform a simultaneous read and UPDATE on the helper table, then use the read value separately to INSERT to the main table. In my mind, this has some annoyance of not being a single query, plus it does have the issue that if the client manages to "reserve" a value of num, but then fails for any reason to actually perform the INSERT, then a gap can occur in the values of num in the table.

这篇关于如何保证带有子查询的原子 SQL 插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！