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

问题描述

给出:

customer[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 ，但是我正在使用的数据库不支持带有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 似乎表明您执行查询还是如果针对不存在的行执行update语句，数据库将锁定索引，从而防止并发插入.

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. 尝试insert新行.您必须捕获如果该行已存在将发生的错误.
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屋！