Select FOR UPDATE considered harmful in PostgreSQL
10 months ago
- #PostgreSQL
- #Database Concurrency
- #Row Locks
- SELECT FOR UPDATE can harm database concurrency by causing unnecessary locks.
- Lost updates occur when a row is modified between reading and updating in READ COMMITTED isolation.
- PostgreSQL uses FOR KEY SHARE locks to protect referenced rows in foreign key constraints.
- Different row lock modes in PostgreSQL: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE.
- SELECT FOR UPDATE is often too strong and can block INSERT operations on referencing tables.
- For most UPDATE operations, SELECT FOR NO KEY UPDATE is recommended to avoid blocking.
- Historical context: PostgreSQL originally had only FOR SHARE and FOR UPDATE locks, later expanded for better concurrency.