SELECT FOR UPDATE Without Index — Full Table Lock Trap

Every production database under real traffic is a battlefield of competing transactions — hundreds of reads and writes colliding every second. Without a disciplined locking strategy, two transactions can read the same account balance, both decide to deduct money, and you end up with funds that vanished into thin air. This is not a hypothetical. It's the kind of bug that costs companies millions and surfaces only under high concurrency — exactly the conditions your monitoring never catches in staging.

Database locking mechanisms are the referee in that battlefield. They define the rules for who gets to read data, who gets to modify it, in what order, and what happens when two transactions want mutually incompatible things at the same time. Understanding them at the internals level — not just 'shared lock good, exclusive lock bad' — is what separates engineers who write correct concurrent code from those who ship subtle data-corruption bugs disguised as race conditions.

By the end of this article you'll understand exactly how shared, exclusive, intent, and row-level locks work internally, how MVCC sidesteps many locking problems, how to read a deadlock graph and resolve it, and the specific isolation level choices that make or break performance in high-throughput systems. You'll also see runnable examples that demonstrate real lock contention and how to diagnose it live in PostgreSQL and MySQL.

Lock Types Demystified — Shared, Exclusive, and the Intent Hierarchy

At the lowest level, every database lock falls into one of two fundamental modes: Shared (S) and Exclusive (X). A Shared lock says 'I'm reading this — others can read too, but nobody rewrites it while I'm looking.' An Exclusive lock says 'I'm writing this — nobody reads or writes until I'm done.' These two modes form the compatibility matrix that every lock manager checks before granting a request.

But modern databases don't just lock individual rows — they lock at multiple granularities: table, page, and row. To avoid scanning every row lock to decide whether a table lock is safe, databases use Intent locks. An Intent Shared (IS) lock on a table means 'some rows in here have Shared locks.' An Intent Exclusive (IX) lock means 'some rows in here have Exclusive locks.' A Shared Intent Exclusive (SIX) lock is the brutal one: the whole table is being read, plus some rows are being modified.

This hierarchy is why a full-table ALTER TABLE can block every single concurrent INSERT — it needs an AccessExclusive lock that's incompatible with every other mode, including IS. Knowing this stops you from running schema migrations on a busy table without precautions like lock_timeout and retries.

Understanding the compatibility matrix is also critical for writing efficient queries. For instance, a SELECT FOR UPDATE acquires RowExclusiveLock at the table level and ExclusiveLock on the row — this blocks other FOR UPDATE/SHARE queries on the same row, but plain SELECTs still work thanks to MVCC. The intent lock protocol ensures the lock manager can quickly determine safety without scanning all row locks.

MVCC — How PostgreSQL and MySQL Serve Reads Without Locking

Multi-Version Concurrency Control (MVCC) is the single most important reason modern databases can handle thousands of concurrent users without readers and writers constantly blocking each other. Instead of one authoritative copy of a row, the database keeps multiple versions — each stamped with the transaction ID that created it and (optionally) the one that deleted it.

When you start a transaction in PostgreSQL under READ COMMITTED, the database hands you a snapshot of the world as it existed at that instant. Your queries read from that snapshot. Meanwhile, writers are creating new row versions in the heap. Your snapshot never sees those new versions — you're reading stale-but-consistent data from the past. This is why a plain SELECT never blocks waiting for a writer to finish.

MySQL InnoDB implements MVCC differently: it stores the old row versions in a separate undo log rather than in the heap alongside new versions. Reads below REPEATABLE READ isolate reconstruct old versions by applying undo records backward. This means long-running read transactions don't just hold a snapshot — they hold a reference to undo log chains that InnoDB cannot purge, causing the infamous 'undo log growing unboundedly' problem under heavy write load.

The key insight: MVCC eliminates most read-write lock conflicts, but it does NOT eliminate write-write conflicts. Two transactions trying to UPDATE the same row still need to serialize — MVCC just handles the read side.

A common production mistake is assuming MVCC makes all concurrency problems go away. It doesn't. You still need to handle serialization errors, deadlocks, and ensure your isolation level matches your consistency requirements. Long-running transactions under REPEATABLE READ can cause table bloat and degraded performance because old row versions cannot be garbage-collected.

Deadlocks — How They Form, How to Detect Them, and How to Prevent Them

A deadlock is two (or more) transactions caught in a circular wait — Transaction A holds a lock that B needs, while B holds a lock that A needs. Neither can proceed. Without intervention, they'd wait forever. Every serious database engine has a deadlock detector that periodically scans the wait-for graph, finds cycles, picks a victim (usually the transaction that's done the least work), rolls it back, and lets the winner proceed.

The most common production deadlock pattern isn't exotic — it's two transactions updating the same two rows in opposite orders. Transaction A locks row 1 then tries to lock row 2. Transaction B locks row 2 then tries to lock row 1. The fix is almost always enforcing a consistent lock ordering: always acquire locks in the same sequence (e.g., always lock the lower account_id first in a money-transfer operation).

A subtler deadlock source is the gap between a SELECT and a subsequent UPDATE. Transaction A reads a row, does application logic for 200ms, then comes back to update it. Transaction B has done the same thing in parallel. When both arrive at the UPDATE, one wins the lock and the other either deadlocks or serializes. Using SELECT ... FOR UPDATE at read time eliminates this gap by acquiring the exclusive lock immediately.

In PostgreSQL, deadlock details land in the server log and in pg_stat_activity. In MySQL InnoDB, SHOW ENGINE INNODB STATUS gives you the full deadlock report with the exact queries and lock wait chains.

Detecting deadlocks in production: Enable deadlock logging. In PostgreSQL, set deadlock_timeout (default 1s) and inspect logs. In MySQL, enable innodb_print_all_deadlocks to capture every deadlock event. Then instrument your application to log the error code and retry count. A high retry rate tells you your lock ordering is wrong even before users complain.

Isolation Levels, Lock Escalation, and Choosing the Right Strategy in Production

Isolation levels are your contract with the database about what anomalies you're willing to tolerate in exchange for concurrency. READ UNCOMMITTED (dirty reads allowed) is almost never correct. READ COMMITTED is the PostgreSQL default and the right choice for most OLTP workloads — each statement gets a fresh snapshot, so writes from other committed transactions are visible immediately. REPEATABLE READ prevents non-repeatable reads by freezing the snapshot at transaction start. SERIALIZABLE gives you full correctness at the cost of more aborts under contention.

Lock escalation is a real performance trap in SQL Server and older MySQL configurations. When a single transaction acquires too many row locks (often >5,000 in SQL Server), the engine escalates to a table lock to save memory. This silently destroys concurrency for all other sessions touching that table. The fix is batching large bulk operations into smaller transactions or using table-level hints explicitly.

For high-throughput write-heavy systems, consider Optimistic Locking — don't lock at read time, just check at write time that nobody else modified the row since you read it. Implement this with a version column or a last_modified timestamp. This works brilliantly when conflicts are rare. When conflicts are frequent, optimistic locking creates a storm of retries and is worse than pessimistic locking.

In PostgreSQL, the SERIALIZABLE isolation level uses Serializable Snapshot Isolation (SSI). It works by detecting read-write conflicts through predicate locks — it tracks which ranges of rows a transaction read, not just which rows it wrote. Under heavy contention, SSI aborts many transactions that would have succeeded under REPEATABLE READ. Don't enable it unless you really need it, and be prepared to handle the serialization failures.

Monitoring Lock Contention in Production — Tools and Diagnostic Queries

You can't fix what you can't see. Lock contention in production is often invisible until transactions start timing out or the connection pool is exhausted. Proactive monitoring of lock wait events and lock queue depths is essential.

In PostgreSQL, the pg_locks and pg_stat_activity views are your primary diagnostic tools. Query for un-granted locks to see who's waiting. The wait_event and wait_event_type columns tell you exactly what the backend is waiting on: 'Lock/relation' means waiting for a table-level lock, 'Lock/tuple' means waiting for a row-level lock.

MySQL InnoDB provides SHOW ENGINE INNODB STATUS which includes sections for LATEST DETECTED DEADLOCK, TRANSACTIONS, and LOCK WAIT. It's verbose but contains exact transaction IDs, lock modes, and the blocked and blocking queries. Enable innodb_print_all_deadlocks and innodb_status_output_locks for continuous monitoring.

Both databases support lock_timeout (PostgreSQL) and innodb_lock_wait_timeout (MySQL) to abort transactions that wait too long. Set these aggressively in production to prevent cascading wait chains.

For real-time lock monitoring, set up metrics: lock wait count, average wait time, and deadlock rate. Alert when any of these exceed your baseline. Tools like pg_stat_statements and Performance Schema (MySQL) can help identify queries that frequently cause lock waits.

Frequently Asked Questions