Geek Answers Handbook

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

Given:

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

I would like to do the following atomically: update the client if it already exists; otherwise, insert a new client.

In theory, this sounds perfect for SQL-MERGE , but the database I use does not support MERGE with AUTO_INCREMENT columns .

Stack Overflow.site/questions/654577 / ... seems to indicate that if you execute a query or update statement for a nonexistent row, the database locks the index, thereby preventing parallel inserts.

Is this behavior a guaranteed SQL standard? Are there databases that don't behave this way?

UPDATE . Unfortunately, I should have mentioned this earlier: the solution should use transaction isolation READ_COMMITTED if this is not possible, in which case I agree to use SERIALIZABLE.

0
sql insert locking h2
source share
4 answers

Answering my own question, as it seems that there is a lot of confusion around the topic. It seems that:

 -- BAD! DO NOT DO THIS! -- insert customer (email, count) select 'foo@example.com', 0 where not exists ( select 1 from customer where email = 'foo@example.com' )

open to race conditions (see Insert line only if it does not already exist ). From what I was able to assemble, the only portable solution to this problem:

  • Select the key to merge. This can be a primary key or another unique key, but must have a unique constraint.
  • Try insert new line. You must catch the error that will occur if the row already exists.
  • The tough part is over. At this stage, the existence of the string is guaranteed, and you are protected from the race conditions by the fact that you keep a write lock on it (due to insert from the previous step).
  • Go ahead and update , if necessary, or select its primary key.
+2
source share

Use Russell Fox code, but use SERIALIZABLE isolation. This takes a range lock so that a nonexistent row is logically locked (along with all other nonexistent rows in the surrounding key range).

So it looks like this:

 BEGIN TRAN IF EXISTS (SELECT 1 FROM foo WITH (UPDLOCK, HOLDLOCK) WHERE [email] = 'thisemail') BEGIN UPDATE foo... END ELSE BEGIN INSERT INTO foo... END COMMIT

Most of the code is taken from its answer, but corrected taking into account the semantics of mutual exclusion.

+1
source share

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

Here is the correct one.

 insert customer (email, count) select 'foo@example.com', 0 where not exists ( select 1 from customer where email = 'foo@example.com' ) update customer set count = count + 1 where email = 'foo@example.com'

If you like, you can insert counter 1 and skip update , if the inserted row, but expressed in your DBMS, returns 1.

The above syntax is completely standard and makes no assumptions about locking mechanisms or isolation levels. If this does not work, your DBMS is broken.

Many people mistakenly believe that select fulfills the "first" and thus introduces a race condition. No: select is part of insert . The insert is atomic. No race.

+1
source share
 IF EXISTS (SELECT 1 FROM foo WHERE [email] = 'thisemail') BEGIN UPDATE foo... END ELSE BEGIN INSERT INTO foo... END
0
source share

More articles:


All Articles