Overview
This page is the atomic definition. Concurrency patterns live at postgres.
Definition
Optimistic locking is a concurrency control technique based on the assumption that conflicts are rare. Instead of holding a database row lock for the entire read-modify-write cycle, the application reads a row along with a version (or updated_at timestamp), performs its business logic, and then issues an UPDATE that includes the original version in the WHERE clause. If another transaction updated the row between the read and the write, the version no longer matches, zero rows are updated, and the application detects the conflict and retries. No long-held locks means no deadlock risk and lower contention. Prisma implements optimistic locking via the @version field; Hibernate uses @Version; many ORMs support the pattern. Pessimistic locking (SELECT FOR UPDATE) is the alternative: it holds a row lock throughout the transaction, guaranteeing no conflict but increasing contention.
When it applies
Use optimistic locking for read-heavy workflows where conflicts are rare, such as user profile edits, document collaboration, and form submissions. Use pessimistic locking (SELECT FOR UPDATE) when conflict probability is high or when acquiring the lock early is cheaper than retry overhead.
Example
-- Read with version:
SELECT id, balance, version FROM accounts WHERE id = 42;
-- Write with version check:
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 42 AND version = 7; -- 0 rows updated = conflictRelated concepts
- postgres - PostgreSQL
SELECT FOR UPDATEvs. application-level version checks. - prisma - Prisma’s
@versionannotation for optimistic locking. - deadlock - optimistic locking eliminates deadlocks at the cost of retry logic.
- transaction-isolation - isolation level affects what version the SELECT sees.
Citing this term
See Optimistic locking (llmbestpractices.com/glossary/optimistic-locking).