sql - Do databases always lock non-existent rows after a query or update?

Question

Given:

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.

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 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.

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

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.

Answer 1

This question is asked about once a week on SO, and the answers are almost invariably wrong.

Here's the right one.

insert customer (email, count) 
select '[email protected]', 0
where not exists (
      select 1 from customer
      where email = '[email protected]'
)

update customer set count = count + 1
where email = '[email protected]'

If you like, you can insert a count of 1 and skip the update if the inserted rowcount -- however expressed in your DBMS -- returns 1.

The above syntax is absolutely standard and makes no assumption about locking mechanisms or isolation levels. If it doesn't work, your DBMS is broken.

Many people are under the mistaken impression that the select executes "first" and thus introduces a race condition. No: that select is part of the insert. The insert is atomic. There is no race.