Please help me understand the use-case behind SELECT ... FOR UPDATE
.
Question 1: Is the following a good example of when SELECT ... FOR UPDATE
should be used?
Given:
- rooms[id]
- tags[id, name]
- room_tags[room_id, tag_id]
- room_id and tag_id are foreign keys
The application wants to list all rooms and their tags, but needs to differentiate between rooms with no tags versus rooms that have been removed. If SELECT ... FOR UPDATE is not used, what could happen is:
- Initially:
- rooms contains
[id = 1]
- tags contains
[id = 1, name = 'cats']
- room_tags contains
[room_id = 1, tag_id = 1]
- rooms contains
- Thread 1:
SELECT id FROM rooms;
returns [id = 1]
- Thread 2:
DELETE FROM room_tags WHERE room_id = 1;
- Thread 2:
DELETE FROM rooms WHERE id = 1;
- Thread 2: [commits the transaction]
- Thread 1:
SELECT tags.name FROM room_tags, tags WHERE room_tags.room_id = 1 AND tags.id = room_tags.tag_id;
- returns an empty list
Now Thread 1 thinks that room 1 has no tags, but in reality the room has been removed. To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE
, thereby preventing Thread 2 from deleting from rooms
until Thread 1 is done. Is that correct?
Question 2: When should one use SERIALIZABLE
transaction isolation versus READ_COMMITTED
with SELECT ... FOR UPDATE
?
Answers are expected to be portable (not database-specific). If that's not possible, please explain why.
The only portable way to achieve consistency between rooms and tags and making sure rooms are never returned after they had been deleted is locking them with
SELECT FOR UPDATE
.However in some systems locking is a side effect of concurrency control, and you achieve the same results without specifying
FOR UPDATE
explicitly.This depends on the concurrency control your database system is using.
MyISAM
inMySQL
(and several other old systems) does lock the whole table for the duration of a query.In
SQL Server
,SELECT
queries place shared locks on the records / pages / tables they have examined, whileDML
queries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so eitherSELECT
orDELETE
query will lock until another session commits.In databases which use
MVCC
(likeOracle
,PostgreSQL
,MySQL
withInnoDB
), aDML
query creates a copy of the record (in one or another way) and generally readers do not block writers and vice versa. For these databases, aSELECT FOR UPDATE
would come handy: it would lock eitherSELECT
or theDELETE
query until another session commits, just asSQL Server
does.Generally,
REPEATABLE READ
does not forbid phantom rows (rows that appeared or disappeared in another transaction, rather than being modified)In
Oracle
and earlierPostgreSQL
versions,REPEATABLE READ
is actually a synonym forSERIALIZABLE
. Basically, this means that the transaction does not see changes made after it has started. So in this setup, the lastThread 1
query will return the room as if it has never been deleted (which may or may not be what you wanted). If you don't want to show the rooms after they have been deleted, you should lock the rows withSELECT FOR UPDATE
In
InnoDB
,REPEATABLE READ
andSERIALIZABLE
are different things: readers inSERIALIZABLE
mode set next-key locks on the records they evaluate, effectively preventing the concurrentDML
on them. So you don't need aSELECT FOR UPDATE
in serializable mode, but do need them inREPEATABLE READ
orREAD COMMITED
.Note that the standard on isolation modes does prescribe that you don't see certain quirks in your queries but does not define how (with locking or with
MVCC
or otherwise).When I say "you don't need
SELECT FOR UPDATE
" I really should have added "because of side effects of certain database engine implementation".