Does "SELECT FOR UPDATE" prevent other connections inserting when the row is not present?

I'm interested in whether a SELECT FOR UPDATE query will lock a non-existent row.

Example

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

Issue query SELECT bar FROM FooBar WHERE foo = ? FOR UPDATE
If the first query returns zero rows, issue a 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 behavior on SQLServer (2005/8), Oracle and MySQL.

Edit Report

1 Answer

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

answered 2010-08-30T19:58:59.997

Does "SELECT FOR UPDATE" prevent other connections inserting when the row is not present?

Example

1 Answer

Your Answer