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 UPDATEexplicitly.This depends on the concurrency control your database system is using.
MyISAMinMySQL(and several other old systems) does lock the whole table for the duration of a query.In
SQL Server,SELECTqueries place shared locks on the records / pages / tables they have examined, whileDMLqueries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so eitherSELECTorDELETEquery will lock until another session commits.In databases which use
MVCC(likeOracle,PostgreSQL,MySQLwithInnoDB), aDMLquery 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 UPDATEwould come handy: it would lock eitherSELECTor theDELETEquery until another session commits, just asSQL Serverdoes.Generally,
REPEATABLE READdoes not forbid phantom rows (rows that appeared or disappeared in another transaction, rather than being modified)In
Oracleand earlierPostgreSQLversions,REPEATABLE READis 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 1query 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 UPDATEIn
InnoDB,REPEATABLE READandSERIALIZABLEare different things: readers inSERIALIZABLEmode set next-key locks on the records they evaluate, effectively preventing the concurrentDMLon them. So you don't need aSELECT FOR UPDATEin serializable mode, but do need them inREPEATABLE READorREAD 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
MVCCor 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".