Does "select for update" prevent other connections inserting when the row is not present

Question

Hi all,

I'm interested in whether a select for update query will lock a non-existent row.

e.g.

Table FooBar with two columns, foo and bar, foo has a unique index

• Issue query select bar from FooBar where foo = ? for update
• If query returns zero rows
  • Issue query insert into FooBar (foo, bar) values (?, ?)

Now is it possible that the insert would cause an index violation or does the select for update prevent that?

Interested in behaviour on SQLServer(2005/8), Oracle and MySQL.

Answer 1

In Oracle, the SELECT ... FOR UPDATE has no effect on a non-existent row (the statement simply raises a No Data Found exception). The INSERT statement will prevent a duplicates of unique/primary key values. Any other transactions attempting to insert the same key values will block until the first transaction commits (at which time the blocked transaction will get a duplicate key error) or rolls back (at which time the blocked transaction continues).

Answer 2

SQL Server only has the FOR UPDATE as part of a cursor. And, it only applies to UPDATE statements that are associated with the current row in the cursor.

So, the FOR UPDATE has no relationship with INSERT. Therefore, I think your answer is that it's not applicable in SQL Server.

Now, it may be possible to simulate the FOR UPDATE behavior with transactions and locking strategies. But, that may be more than what you're looking for.

Answer 3

I wrote a detailed analysis of this thing on SQL Server: Developing Modifications that Survive Concurrency

ANyway, you need to use SERIALIZABLE isolation level, and you really need to stress test.

Answer 4

On Oracle:

Session 1

create table t (id number);
alter table t add constraint pk primary key(id);

SELECT *
FROM t
WHERE id = 1
FOR UPDATE;
-- 0 rows returned
-- this creates row level lock on table, preventing others from locking table in exclusive mode

Session 2

SELECT *
FROM t 
FOR UPDATE;
-- 0 rows returned
-- there are no problems with locking here

rollback; -- releases lock


INSERT INTO t
VALUES (1);
-- 1 row inserted without problems