Controlling Isolation Levels and Database Locks
PostgreSQL lets you control isolation levels at different scopes:
- Database level: Using ALTER DATABASE SET DEFAULT_TRANSACTION_ISOLATION TO …
- Session level: Using SET SESSION CHARACTERISTICS
- Transaction level: After a transaction has been initiated with BEGIN.
By default, PostgreSQL uses Read Committed. At this level, concurrent transactions can silently overwrite each other's changes (lost updates) if not carefully managed.
SELECT FOR UPDATE vs. SELECT FOR NO KEY UPDATE
The command SELECT FOR UPDATE provides a simple way to lock rows that one plans to change in a transaction. Rows that are flagged as SELECT FOR UPDATE are locked and cannot be altered by concurrent transactions until the current transaction commits or rolls back. This applies to deletions and updates of any column values in the selected rows, but it also applies to foreign key relationships. When a SELECT FOR UPDATE lock is applied to a parent table (e.g....
