MySQL INSERT SELECT WHERE 竞争条件

Question

我正在开发一个票务系统，用户在领取之前一次性托管大量票(基本上是所有没有缺货的票)。这些票显示给用户，他们可以选择他们想要领取的票。

如果两个用户同时尝试托管同一张票但没有足够的票，则此托管系统可能会引入竞争条件，如:

剩余门票:1

用户 A 点击页面，检查剩余的票数。还剩 1 张票用户 B 点击页面，检查剩余的票数。还剩 1 张票

因为他们都有一张剩余的票，所以他们都会托管这张票，使剩余的票数为 -1。

我想尽可能避免锁定，并且想知道是否有一个带有子查询的语句

INSERT INTO ticket_escrows (`ticket`,`count`) 
SELECT ticket,tickets_per_escrow FROM tickets WHERE tickets.total > (
    COALESCE(
        SELECT SUM(ticket_escrows.count) FROM ticket_escrows 
        WHERE ticket_escrows.ticket = tickets.id
        AND ticket_escrows.valid = 1
    ,0)
    +
    COALESCE(
        SELECT SUM(ticket_claims.count) 
        FROM ticket_claims
        WHERE ticket_claims.ticket = tickets.id
    ,0)
)

将是原子的，允许我在不锁定的情况下防止竞争条件。

具体来说，我想知道上述查询是否会阻止以下情况的发生:

Max tickets: 50 Claimed/Escrowed tickets: 49
T1: start tx -> sums ticket escrows --> 40
T2: start tx -> sums ticket escrows --> 40
T1: sums ticket claims --> 9
T2: sums ticket claims --> 9
T1: Inserts new escrow since there is 1 ticket left --> 0 tickets left
T2: Inserts new escrow since there is 1 ticket left --> -1 tickets left

我正在使用 InnoDB。

Answer 1

要回答您的问题“如果带有子查询的语句......将是原子的”:在您的情况下，是的。

只有包含在事务中时，它才是原子的。由于您声明您正在使用 InnoDB，因此即使带有子查询的查询也是 SQL 语句，因此在事务中执行。引用 documentation :

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own.

...If a statement returns an error, the commit or rollback behavior depends on the error.

此外，isolations levels

In terms of the SQL:1992 transaction isolation levels, the default InnoDB level is REPEATABLE READ

REPEATABLE READ 对你来说可能不够，这取决于你的程序逻辑。它防止事务写入另一个事务读取的数据，直到读取事务完成，phantom reads是可能的，但是。检查SET TRANSACTION 了解如何更改隔离级别。

---

要回答您的第二个问题“如果上述查询将阻止以下情况发生...”:在具有 SERIALIZABLE 隔离级别的事务中，它不会发生。我相信默认级别在您的情况下也应该是安全的(假设 tickets.total 没有改变)，但我更希望有人确认它。