A transaction that updates a row and then re-reads it sees its own uncommitted change, even though no other transaction can. Beyond transaction-id stamps and the in-flight list, what extra bookkeeping does the engine need so that a single statement does not repeatedly process rows it has just written itself?

## Why self-visibility is a special case The general visibility rule rejects versions created by transactions that were in flight at snapshot time. Your own transaction is, by definition, in flight — so a literal application of the rule would make a transaction unable to see its own writes. Every engine therefore carves out an explicit exception: if a version's creator id equals my own transaction id, treat creation as visible; if the expirer id equals my own id, treat the row as deleted for me. This is what makes the ordinary programming model work — insert a row, then query it back; update a row, then read the new value — while other transactions continue to see the pre-change state until commit. ## Where transaction ids alone break down The transaction id is uniform across the whole transaction, so it cannot distinguish "written by an earlier statement of mine" from "written by the statement currently running". That distinction matters, because a statement can read and write the same rows. The canonical failure is the **Halloween problem**, named after the day it was found. Consider `UPDATE employees SET salary = salary * 1.1 WHERE salary < 50000` executed via an index on `salary`. The statement updates a row from 45,000 to 49,500 and produces a new version. If the scan then encounters that new version and self-visibility says "created by me, therefore visible", the row still satisfies `salary < 50000` and gets raised again — and again — until it crosses the threshold or the statement never terminates. The rows read must be the rows as of the statement's start, not as the statement is mutating them. The same hazard appears in an `INSERT ... SELECT` where source and target are the same table, and in delete-and-scan patterns. ## The command sequence number The fix is a finer-grained stamp: alongside the transaction id, each version records the **command (statement) sequence number** within the transaction that produced it. PostgreSQL calls this the command id (`cmin`/`cmax`); other engines implement the same idea through statement-scoped read views or scan-position rules. The self-visibility clause becomes: > a version created by my own transaction is visible to me only if its command number is **strictly less than** the command number of the statement currently executing. Consequences: - Statement 3 sees everything statements 1 and 2 did — multi-statement transactions read intuitively. - Statement 3 does **not** see rows statement 3 itself is writing, so each qualifying row is processed exactly once and the Halloween problem cannot arise. - Deletes are symmetric: a row expired by an earlier command of mine is gone for me; a row my current command just expired is still considered present by that same command's scan, which is precisely what keeps the scan stable. ## Related consequences worth knowing - **Trigger and constraint timing.** Code running inside a statement — row triggers, check expressions, functions in the statement — executes at the same command number and so inherits the same partial view. This is why a `BEFORE`/row-level trigger that queries the table it is modifying can see a surprising, statement-frozen view, and why an `AFTER` statement-level trigger sees the completed effects. - **Data-modifying CTEs.** In engines where a single statement contains several modifying subqueries, all of them share one command number, so none sees another's output within that statement. The result is a genuine source of confusion and a good interview follow-up. - **Cursors.** A cursor opened at one command number keeps that view, which is why rows fetched through it can be stale relative to writes the same transaction performed afterwards, unless the cursor was declared to be re-evaluated. - **Storage cost.** The command number only matters while the transaction is open — no other transaction ever needs it — so engines can reuse the same header space for other purposes once the transaction ends, rather than carrying it forever. ## What a strong answer sounds like Start with the plain fact — own writes are visible because the rule has a self exception. Then show you know the exception alone is insufficient, name the Halloween problem with a concrete looping-update example, and describe the command sequence number and the strictly-earlier rule that resolves it. Mentioning triggers or data-modifying CTEs as places where the statement-level view is observable turns a recited answer into a demonstrated one.