At the READ COMMITTED isolation level, a statement such as UPDATE orders SET status='SHIPPED' WHERE status='PENDING' reaches a row that a concurrent, still-uncommitted transaction has already modified. Describe what your statement does while it waits and what happens after the other transaction commits or rolls back.

## Two different rules in one statement An `UPDATE` or `DELETE` does two things: it finds rows (a read) and it modifies them (a write). Under READ COMMITTED these obey different rules, and the interaction is where most real bugs and most interview depth live. - **Finding** rows uses the statement's snapshot — the newest versions committed when the statement started. - **Modifying** a row requires the row's exclusive write lock, and no transaction may replace a version that an uncommitted transaction has already written. That prohibition — the *dirty write* rule — holds at every isolation level, including this one. ## The block When your statement selects a row for modification and discovers that a concurrent transaction has already written a newer, uncommitted version of it, your statement cannot proceed and cannot skip ahead: it **waits** on the lock held by that transaction. This is the one place READ COMMITTED makes a well-behaved statement wait, and it is why long-lived write transactions cause pile-ups even at a level where readers never block. While blocked, your statement is stuck for the *remaining lifetime of the other transaction*, not for some short duration. If a client opened a transaction, updated a hot row, and then went to lunch, your update waits for lunch to finish. This is the mechanism behind most "the database is hung" incidents at READ COMMITTED, and the reason engines expose lock-wait timeouts and blocked-session views. ## Resolution: rollback If the blocker rolls back, its version never existed. The version your snapshot saw is once again the current one, your `WHERE` evaluation still stands, and your update applies with no further ceremony. ## Resolution: commit If the blocker commits, the world has moved. Your snapshot is now stale for this specific row, and the engine has three unattractive options: fail your statement, apply your update blindly over the new version, or re-check. Typical MVCC engines **re-check**: 1. Fetch the newest committed version of the row. 2. If the row was deleted, there is nothing to update — skip it. 3. Re-evaluate the statement's `WHERE` predicate against that new version. - Still matches (`status` is still `'PENDING'`) → apply the update, computing any `SET` expressions from the **new** version's values. - No longer matches (someone else set it to `'CANCELLED'`) → skip the row silently. The striking consequence is that a writing statement under READ COMMITTED can read and act on data **newer than its own snapshot**. That is a deliberate, documented inconsistency in the level: without it, concurrent updates to the same row would routinely no-op or clobber, and the engine would either lose work or need to abort transactions. ## Why this makes single-statement updates safe Because the `SET` expression is recomputed against the refreshed version, this pattern is race-free under READ COMMITTED: ``` UPDATE counter SET n = n + 1 WHERE id = 1; ``` Two concurrent executions serialise on the row lock, and the second recomputes `n + 1` from the value the first committed. No increment is lost. This is the same pattern: ``` UPDATE stock SET qty = qty - 1 WHERE sku = 'A' AND qty > 0; ``` The `qty > 0` guard is re-evaluated against the fresh version, so the statement will not drive stock negative even under contention. Checking `qty` with a separate `SELECT` first and then updating gives no such protection, because that read used the snapshot and nothing revalidates it. ## Where the sharp edges are - **Silent skips.** A row that no longer matches after re-check is skipped, not reported. Your "rows affected" count may be lower than you expected, and code that ignores that count will believe work happened that did not. Always inspect the affected-row count on conditional updates and treat zero as a real outcome. - **The predicate must carry the precondition.** Re-checking only helps if the condition you care about is *in the `WHERE` clause*. A precondition living in application code is not re-evaluated by anyone. - **Non-determinism.** The same concurrent workload can produce different final states depending on lock arrival order; that is expected at this level, not a defect. - **Deadlocks.** Multi-row updates that touch rows in different orders can deadlock on these write locks. Apply a deterministic ordering, or be ready to retry on the engine's deadlock error. - **Not standardised.** The SQL standard says nothing about re-checking. Engines differ in the details — whether the predicate is re-evaluated, how a deleted row is treated, and what happens when a stronger level is used instead (there, the usual answer is to abort with a serialization failure rather than re-check). Say "typical MVCC engines" rather than asserting one behaviour universally. ## The takeaway Under READ COMMITTED, reads are snapshot-based and stale-tolerant; writes are lock-based and freshness-forced. Put every precondition you depend on into the `WHERE` clause of the statement that performs the write, check the affected-row count, and you get correct behaviour from the level without escalating it.