数据库是否总是在查询或更新后锁定不存在的行? [英] Do databases always lock non-existent rows after a query or update?

问题描述

给定:

客户[id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(30), count INT]

我想自动执行以下操作:如果客户已经存在，则更新他；否则，插入一个新客户.

I'd like to execute the following atomically: Update the customer if he already exists; otherwise, insert a new customer.

理论上，这听起来非常适合 SQL-MERGE，但是我使用的数据库不支持 MERGE with AUTO_INCREMENT 列.

In theory this sounds like a perfect fit for SQL-MERGE but the database I am using doesn't support MERGE with AUTO_INCREMENT columns.

https://stackoverflow.com/a/1727788/14731 似乎表明如果您执行查询或针对不存在的行更新语句，数据库将锁定索引从而防止并发插入.

https://stackoverflow.com/a/1727788/14731 seems to indicate that if you execute a query or update statement against a non-existent row, the database will lock the index thereby preventing concurrent inserts.

这种行为是否受到 SQL 标准的保证?有没有这样的数据库?

Is this behavior guaranteed by the SQL standard? Are there any databases that do not behave this way?

更新:对不起，我应该在前面提到这一点:解决方案必须使用 READ_COMMITTED 事务隔离除非在这种情况下这是不可能的，我将接受使用 SERIALIZABLE.

UPDATE: Sorry, I should have mentioned this earlier: the solution must use READ_COMMITTED transaction isolation unless that is impossible in which case I will accept the use of SERIALIZABLE.

推荐答案

回答我自己的问题，因为围绕该主题似乎有很多困惑.看来:

Answering my own question since there seems to be a lot of confusion around the topic. It seems that:

-- BAD! DO NOT DO THIS! --
insert customer (email, count) 
select '[e-mail]', 0
where not exists (
      select 1 from customer
      where email = '[e-mail]'
)

对竞争条件开放(请参阅仅在不存在时插入一行).从我收集到的信息来看，这个问题的唯一便携式解决方案:

is open to race-conditions (see Only inserting a row if it's not already there). From what I've been able to gather, the only portable solution to this problem:

1. 选择要合并的键.这可能是主键或另一个唯一键，但它必须具有唯一约束.
2. 尝试插入一个新行.如果该行已经存在，您必须捕获将发生的错误.
3. 困难的部分已经结束.此时，该行保证存在，并且您在其上持有写锁(由于上一步中的 insert )这一事实可以保护您免受竞争条件的影响.
4. 继续，如果需要，update 或 select 其主键.

1. Pick a key to merge against. This could be the primary key, or another unique key, but it must have a unique constraint.
2. Try to insert a new row. You must catch the error that will occur if the row already exists.
3. The hard part is over. At this point, the row is guaranteed to exist and you are protected from race-conditions by the fact that you are holding a write-lock on it (due to the insert from the previous step).
4. Go ahead and update if needed or select its primary key.

这篇关于数据库是否总是在查询或更新后锁定不存在的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！