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

Question

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.

Answer 1

MySQL

SELECT ... FOR UPDATE with UPDATE

Using transactions with InnoDB (auto-commit turned off), a SELECT ... FOR UPDATE allows one session to temporarily lock down a particular record (or records) so that no other session can update it. Then, within the same transaction, the session can actually perform an UPDATE on the same record and commit or roll back the transaction. This would allow you to lock down the record so no other session could update it while perhaps you do some other business logic.

This is accomplished with locking. InnoDB utilizes indexes for locking records, so locking an existing record seems easy--simply lock the index for that record.

SELECT ... FOR UPDATE with INSERT

However, to use SELECT ... FOR UPDATE with INSERT, how do you lock an index for a record that doesn't exist yet? If you are using the default isolation level of REPEATABLE READ, InnoDB will also utilize gap locks. As long as you know the id (or even range of ids) to lock, then InnoDB can lock the gap so no other record can be inserted in that gap until we're done with it.

If your id column were an auto-increment column, then SELECT ... FOR UPDATE with INSERT INTO would be problematic because you wouldn't know what the new id was until you inserted it. However, since you know the id that you wish to insert, SELECT ... FOR UPDATE with INSERT will work.

CAVEAT

On the default isolation level, SELECT ... FOR UPDATE on a non-existent record does not block other transactions. So, if two transactions both do a SELECT ... FOR UPDATE on the same non-existent index record, they'll both get the lock, and neither transaction will be able to update the record. In fact, if they try, a deadlock will be detected.

Therefore, if you don't want to deal with a deadlock, you might just do the following:

INSERT INTO ...

Start a transaction, and perform the INSERT. Do your business logic, and either commit or rollback the transaction. As soon as you do the INSERT on the non-existent record index on the first transaction, all other transactions will block if they attempt to INSERT a record with the same unique index. If the second transaction attempts to insert a record with the same index after the first transaction commits the insert, then it will get a "duplicate key" error. Handle accordingly.

SELECT ... LOCK IN SHARE MODE

If you select with LOCK IN SHARE MODE before the INSERT, if a previous transaction has inserted that record but hasn't committed yet, the SELECT ... LOCK IN SHARE MODE will block until the previous transaction has completed.

So to reduce the chance of duplicate key errors, especially if you hold the locks for awhile while performing business logic before committing them or rolling them back:

1. SELECT bar FROM FooBar WHERE foo = ? LOCK FOR UPDATE
2. If no records returned, then
3. INSERT INTO FooBar (foo, bar) VALUES (?, ?)