ACID stands for Atomicity, Consistency, Isolation, and Durability. The first three letters get equal billing in textbooks, but in day-to-day backend engineering, Isolation is the property that causes the most production incidents, because it's the only one that depends on timing — what one transaction can see or touch while another is still in flight.

The SQL standard defines isolation in terms of which "phenomena" a given level is allowed to permit between concurrent transactions. According to the PostgreSQL documentation, the standard's strictest level, Serializable, guarantees that any concurrent execution of serializable transactions produces the same result as running them one at a time in some order, while the other three levels are defined by which anomalies they still permit.

The three classic anomalies you need to design around are:

A fourth anomaly, the lost update, doesn't appear in the standard's phenomena table but is arguably the most common real-world bug: two transactions read the same row, both compute a new value based on the old one, and the second write silently overwrites the first. This is precisely the failure mode SELECT ... FOR UPDATE exists to prevent.

The Four Standard Isolation Levels

Two important implementation details are easy to miss if you only read the SQL standard and not your specific engine's docs:

• PostgreSQL only truly implements three distinct isolation levels. Per the PostgreSQL Transaction Isolation documentation, Read Uncommitted is silently promoted to Read Committed, and — notably — PostgreSQL's Repeatable Read already blocks phantom reads, which is stricter than the SQL standard requires and stricter than Repeatable Read in most other engines.
• MySQL/InnoDB's Repeatable Read also mitigates most phantom reads through "next-key locking" (a combination of row locks and gap locks), as detailed in the MySQL InnoDB Locking documentation, but its guarantees differ from PostgreSQL's snapshot-based approach, so code that relies on exact phantom-read behavior should never be assumed portable across engines.

The practical takeaway: never assume the SQL standard's table describes your database's actual behavior. Always confirm against your engine's official documentation before you design concurrency-sensitive logic around a specific isolation level.

Locks: The Mechanism Behind Isolation

Isolation levels are a contract. Locking (or, in PostgreSQL's case, Multi-Version Concurrency Control plus locking) is the mechanism that enforces it.

There are two broad lock modes relevant to this discussion:

• Shared (S) locks — allow multiple transactions to read the same row concurrently but block writers.
• Exclusive (X) locks — allow only the lock holder to read or write the row; all other transactions must wait.

And two broad lock scopes:

• Row-level locks — the default and preferred granularity in PostgreSQL, MySQL/InnoDB, and Oracle. High concurrency, low contention.
• Table-level locks — coarser, used for schema changes (ALTER TABLE) or explicit LOCK TABLE statements. High contention, should be avoided in hot paths.

Lock contention is what happens when multiple transactions compete for the same lock at overlapping times. A moderate amount of contention is normal and simply shows up as slightly higher latency. Severe contention shows up as connection pool exhaustion, timeouts, and — in the worst case — deadlocks.

Deadlocks: Why They Happen and How Databases Resolve Them

A deadlock occurs when two (or more) transactions each hold a lock the other needs, and neither can proceed. The textbook example:

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... pauses ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction B, running concurrently
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- ... pauses ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

If A locks row 1 and B locks row 2 at nearly the same time, A then waits for row 2 (held by B) while B waits for row 1 (held by A). Neither can proceed. Both PostgreSQL and MySQL detect this cycle automatically and abort one of the transactions with a deadlock error, letting the other proceed. The aborted transaction's application code is expected to catch the error and retry.

The single most effective deadlock-prevention technique: consistent lock ordering

The fix for the example above is almost always the same regardless of engine: always acquire locks in the same order across all transactions. If every transaction that touches multiple accounts always locks the lower id first, the cyclic wait condition becomes structurally impossible.

-- Always lock in ascending id order, regardless of transfer direction
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- perform both balance updates here
COMMIT;

Other proven mitigations, in rough order of impact:

• Keep transactions short. The longer a transaction holds a lock, the larger the window for contention. Do validation and computation outside the transaction where possible; do only the writes inside it.
• Touch the fewest rows necessary, in the narrowest scope. Avoid UPDATE statements without a WHERE clause narrowed by an indexed column — an unindexed predicate can force a full table scan and lock far more rows (or the whole table) than intended.
• Index the columns used in your WHERE and FOR UPDATE predicates. Without an index, the database may have to scan and lock rows it will ultimately discard, inflating both contention and deadlock risk. This is closely related to broader indexing strategy — if you haven't already tuned your indexes for your workload, it's worth pairing this with a proper index review (B-Tree isn't always the right choice; GIN, GiST, and partial indexes each solve different contention patterns).
• Retry on deadlock. Deadlocks are expected, not exceptional, in a busy OLTP system. Application code should catch the engine's deadlock error code and retry the transaction (typically with exponential backoff) rather than surfacing it to the user.
• Avoid interactive transactions. Never open a transaction, wait on user input or an external API call, and then commit. That lock can sit open for seconds or minutes, and every other transaction touching those rows queues up behind it.

SELECT … FOR UPDATE: The Core Tool for Preventing Lost Updates

SELECT ... FOR UPDATE reads a set of rows and simultaneously takes an exclusive row lock on each one, so no other transaction can update or delete them (or, depending on engine, even select them FOR UPDATE) until the locking transaction commits or rolls back.

The canonical use case: read-modify-write

Without locking, this sequence is unsafe under Read Committed:

BEGIN;
SELECT quantity FROM inventory WHERE sku = 'ABC123';
-- application computes new_quantity = quantity - 1
UPDATE inventory SET quantity = new_quantity WHERE sku = 'ABC123';
COMMIT;

If two transactions run this concurrently, both can read quantity = 5 before either commits, both compute 4, and the second UPDATE silently overwrites the first — a classic lost update, and the reason overselling happens in poorly built inventory systems.

The fix:

BEGIN;
SELECT quantity FROM inventory WHERE sku = 'ABC123' FOR UPDATE;
-- second concurrent transaction now blocks here until this one commits
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'ABC123';
COMMIT;

The second transaction's SELECT ... FOR UPDATE blocks until the first commits, then sees the already-decremented value. The lost update becomes structurally impossible.

NOWAIT and SKIP LOCKED

Both PostgreSQL and MySQL support modifiers that change what happens when a row is already locked, as documented in the MySQL Locking Reads reference:

• FOR UPDATE NOWAIT — fails immediately with an error instead of waiting, useful when you'd rather surface "try again" to the caller than block a connection.
• FOR UPDATE SKIP LOCKED — silently skips already-locked rows instead of waiting or failing. This is the standard pattern for building a job queue on top of a relational table, since multiple workers can each grab a different unlocked row without contending for the same one:

BEGIN;
SELECT id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- process the job
UPDATE job_queue SET status = 'done' WHERE id = :id;
COMMIT;

This pattern is one of the most common reasons teams reach for FOR UPDATE at all — it turns an ordinary table into a safe multi-consumer queue without external infrastructure.

FOR SHARE vs FOR UPDATE

Where you only need to prevent a row from being modified while still allowing other readers to also lock it non-exclusively (e.g., verifying a foreign key still exists before referencing it), use FOR SHARE instead of FOR UPDATE. It takes a shared lock rather than an exclusive one, permitting concurrent shared-lock readers but still blocking writers.

Mitigating Dirty Reads

Dirty reads only occur at Read Uncommitted, and in practice they are the easiest anomaly to eliminate: simply never use Read Uncommitted for logic that matters. Read Committed — the default in most engines — already guarantees a query only sees committed data, at negligible cost to concurrency compared to Read Uncommitted. There is rarely a legitimate reason to intentionally weaken isolation below Read Committed in an OLTP application; if you find yourself doing so for performance reasons, the underlying problem is almost always slow queries or missing indexes, not isolation level.

Mitigating Phantom Reads

Phantom reads are trickier because they involve new rows appearing, not existing rows changing — a plain row lock on an existing row can't prevent a brand-new row from being inserted into a range you've already scanned.

Three practical mitigations:

1. Use Serializable isolation for the specific transactions where phantom reads would cause a correctness bug (e.g., enforcing a uniqueness constraint across a computed condition, not just a single column). Serializable is more expensive and requires your application to handle serialization-failure errors with a retry loop, so reserve it for the transactions that actually need it rather than applying it globally.
2. Use SELECT ... FOR UPDATE combined with a unique constraint as a cheaper alternative for many real-world cases — for instance, locking a "parent" row that logically governs the range (a summary row, a counter row) forces all concurrent writers into the same lock, even though the phantom rows themselves aren't directly lockable.
3. Rely on a database-enforced constraint (unique index, exclusion constraint, or check constraint) rather than an application-level "check then insert" pattern wherever the invariant can be expressed declaratively. A constraint is atomic and immune to phantom-read races by construction; application-level range checks are not.

Choosing an Isolation Level: A Decision Framework

Rather than defaulting to "whatever the ORM sets," it's worth deciding per transaction:

• Read-only reporting/analytics query, staleness acceptable → Read Committed (default) is almost always sufficient and cheapest.
• Read-modify-write on a single row or small, known set of rows → Read Committed + SELECT ... FOR UPDATE on the specific rows.
• Multi-step logic that must see a consistent snapshot across several queries (e.g., computing a report inside a transaction that must match at the end) → Repeatable Read.
• Enforcing an invariant across a range or aggregate condition that new rows could violate (e.g., "no more than N active sessions per user") → Serializable, with application-level retry on serialization failure, or a declarative constraint if one is expressible.

The general engineering principle: use the lowest isolation level that still guarantees correctness for that specific transaction, and add row-level locking (FOR UPDATE) surgically where a race condition would otherwise cause a lost update. Reaching for Serializable everywhere "to be safe" usually just moves the problem from data corruption to a flood of retryable serialization errors under load.

Diagnosing Lock Contention in Production

Prevention matters most, but you also need visibility when contention does happen:

• PostgreSQL: pg_locks joined against pg_stat_activity shows exactly which sessions are blocked and by whom. log_lock_waits and deadlock_timeout in postgresql.conf control when a wait gets logged and how long the engine waits before running deadlock detection.
• MySQL/InnoDB: SHOW ENGINE INNODB STATUS surfaces the most recent detected deadlock, including the exact statements and locks involved. The performance_schema tables (data_locks, data_lock_waits) give a queryable, real-time view of the same information — see the MySQL InnoDB Locks Set by SQL Statements documentation for how specific statements map to lock types.

Set up alerting on deadlock rate and average lock wait time as first-class database health metrics — a rising trend in either almost always precedes a latency incident, and catching it early is far cheaper than diagnosing it after a customer-facing timeout spike.

Worked Example: A Safe Funds Transfer

Putting the lock-ordering, short-transaction, and FOR UPDATE principles together:

BEGIN;

-- Lock both rows up front, in a fixed order, to avoid deadlocks
SELECT id, balance FROM accounts
WHERE id IN (:from_id, :to_id)
ORDER BY id
FOR UPDATE;

-- Application-level check
-- if balance at :from_id < :amount then ROLLBACK and reject

UPDATE accounts SET balance = balance - :amount WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amount WHERE id = :to_id;

COMMIT;

This transaction is short, touches only the two rows it needs, locks them in a globally consistent order regardless of transfer direction, and relies on Read Committed plus explicit row locking rather than a heavier isolation level — giving correctness without the throughput cost of Serializable.

Conclusion

Deadlocks, dirty reads, and phantom reads aren't random database flakiness — they're predictable consequences of specific isolation levels and locking decisions, and each has a well-understood fix. Dirty reads disappear once you move past Read Uncommitted. Lost updates disappear once you add SELECT ... FOR UPDATE around your read-modify-write sequences. Deadlocks shrink dramatically once every code path acquires locks in a consistent order and keeps transactions short. Phantom reads are the one genuinely hard case, and they're best handled with Serializable isolation or a proper database constraint rather than an application-level workaround.

The underlying discipline is the same across PostgreSQL, MySQL, and every other major relational engine: pick the isolation level that matches the correctness requirement of that specific transaction, lock only what you need, hold locks for as short a time as possible, and always verify the exact behavior against your engine's own documentation rather than assuming the SQL standard's table applies unmodified.

Frequently Asked Questions

Does SELECT ... FOR UPDATE work the same way in every database? No. The core behavior (take an exclusive row lock on the selected rows) is consistent across PostgreSQL, MySQL/InnoDB, and Oracle, but details like default lock scope, interaction with isolation level, and support for NOWAIT/SKIP LOCKED vary by version and engine. Always confirm against the official documentation for your specific database and version.

Is Serializable isolation always the "safest" choice? It provides the strongest correctness guarantee, but at the cost of more serialization failures under concurrent load, each of which requires an application-level retry. For most OLTP workloads, Read Committed plus targeted SELECT ... FOR UPDATE locking achieves the needed correctness at much lower cost, with Serializable reserved for the specific transactions that truly require it.

Why do deadlocks still happen even with FOR UPDATE? FOR UPDATE prevents lost updates on the rows you lock, but if two transactions lock the same set of rows in a different order, a deadlock is still possible. The fix is consistent lock ordering (e.g., always sorting by primary key before locking), not avoiding FOR UPDATE itself.

Can I just catch and retry every deadlock error? Yes, and this is standard practice — but only for transactions that are safe to retry (i.e., idempotent or fully rolled back on failure). Retrying should use backoff to avoid the retried transactions immediately re-colliding under sustained contention.

References

• PostgreSQL Documentation — 13.2. Transaction Isolation
• MySQL 8.0 Reference Manual — 17.7.2.4 Locking Reads
• MySQL 8.0 Reference Manual — 17.7.3 Locks Set by Different SQL Statements in InnoDB