Transactions, Concurrency Control, and Isolation Levels Questions

Direct answer

The honest first move is to ask what point in time the read job's queries are actually anchored to, because that single fact determines everything else: whether it can see uncommitted data, whether it can see the same row differently twice, whether new rows can appear mid-run, and whether it can get blocked outright by the writer. There's no single "kind of inconsistency" here; there's a small set of distinct failure shapes, and which ones apply depends entirely on isolation level and on exactly when, relative to the writer, the read job's snapshot was taken.

Structured elaboration

Work through it in the order an interviewer would actually probe:

1. Can the read job see data the writer hasn't committed yet? Only if the read runs under an isolation level that permits dirty reads. In practice, most production RDBMSs make this hard to hit even when nominally asked for (Postgres maps Read Uncommitted to Read Committed internally and never returns uncommitted data), but on an engine or configuration where it is possible, the read job could aggregate a value the writer later rolls back, corrupting the output with data that never really existed.
2. If the read job touches the same row more than once, can it get two different answers? Yes, under Read Committed, if the writer commits a change between the read job's first and second look at that row: a non-repeatable read. A batch job that reads a summary count at the start of its run and a detail total at the end, expecting them to agree, is exactly the shape that breaks.
3. If the read job re-runs the same filtered query, can the row set itself change? Yes, same mechanism, a phantom read: rows the writer inserted or deleted mid-run appear or disappear between two identical queries in the same read transaction, even though no row the job already looked at changed value.
4. Does the timing of when the read job started, relative to the writer, matter even without any of the above? Yes, and this is the part that's easy to miss: under Read Committed, "start before the writer's transaction commits" and "start after" produce two completely different, individually consistent results, neither one wrong, just different snapshots of different moments. A batch job kicked off a few seconds earlier or later than usual can legitimately produce a different total purely from that timing, with no bug anywhere.
5. Can the read job get physically blocked rather than just see stale or shifting data? Yes, if the writer holds a lock the read job's own statements need (this happens on engines whose stronger isolation levels take locks on plain reads, or if the writer itself holds row or table locks for schema-changing operations). That's a different failure mode entirely: not wrong data, but the read job hanging or timing out.

Worked example

Points 2 and 3 above are not speculative; both were reproduced against a live Postgres 16 database. For point 2 (same value, two different reads, one transaction): a transaction under Read Committed reads accounts.balance as 500.00, a concurrent transaction commits an update to 750.00 in between, and the first transaction's second read of the same row returns 750.00. For point 3 (same query, two different row sets): a transaction counts on_call = true rows and gets 2, a concurrent INSERT of a new on-call row commits, and the same count query re-run in the same transaction returns 3. Re-running both sequences under REPEATABLE READ instead produced the same answer both times in each case, confirming the fix is the isolation level, not anything about the query itself.

Trade-offs & pitfalls

• The instinct to blame "the batch job is buggy" when its output doesn't match a prior run is usually wrong; check the isolation level and the exact wall-clock window the job's transaction was open for, before assuming the query logic is at fault.
• Snapshot reads (Repeatable Read or better) remove failure modes 2 and 3 above entirely, at essentially no cost to a read-only job, since a reader that never writes has nothing to block on and nothing to abort for. There's rarely a good reason for a read-only batch job to run under Read Committed once Repeatable Read is available.

Direct answer

PostgreSQL's MVCC (multi-version concurrency control) never updates a row in place: every UPDATE (and every DELETE) leaves the old row version on disk, marked dead, while a new version is written elsewhere. That old version has to stick around until no transaction's snapshot could possibly still need it, which is exactly why update- and delete-heavy tables accumulate dead tuples, bloat, over time. Detecting it is a matter of asking the engine directly how much of a table is live versus dead; remediating it means running VACUUM regularly enough that dead space never has the chance to build up in the first place.

Structured elaboration

Why MVCC causes bloat. Every row carries an xmin (creating transaction) and xmax (superseding transaction, if any). An UPDATE on Postgres 16, reproduced for real, physically relocates the row on every write (ctid, its physical address, changes each time) rather than editing bytes in place: a row updated twice moved from (0,7) to (0,14) to (0,15), leaving two dead versions behind. Nothing removes those dead versions automatically at write time, because another transaction's still-open snapshot might legitimately need to see the pre-update version; only a later cleanup pass can safely reclaim them, once no snapshot could possibly need them anymore.

Detecting bloat, with real numbers. A 50,000-row table, freshly created, measures 3320 kB on disk (pg_total_relation_size), reproduced directly against Postgres 16. With autovacuum (Postgres's background process that runs VACUUM automatically, without anyone scheduling it) disabled on that table to observe the raw state, a single bulk UPDATE touching every row (the shape of a routine nightly batch job) grows it to 6568 kB; the pgstattuple extension confirms why directly, reporting dead_tuple_count = 50000 (dead_tuple_percent = 39.04), exactly one dead version per live row, since the old version of every updated row is still sitting on disk next to its replacement. A second bulk update, still with no VACUUM run in between, grows the table further to 9032 kB, but pgstattuple measured right after that second update still reports dead_tuple_count = 50000, not 100,000. That is not a missing count: it is Postgres reclaiming part of the mess as it goes. The first update's transaction had already committed, so its dead row versions became invisible to every later snapshot immediately; when the second update needed room on a page that was now half-full of those dead versions, it opportunistically pruned them in place before writing its own new versions, reusing freed space rather than only ever adding to it. That pruning happens during ordinary reads and writes, with no VACUUM involved at all; it just does not return space to the operating system, which is what VACUUM and VACUUM FULL are for, covered next. On a comparable 20,000-row table, pgstattuple('bloat_demo2') reported dead_tuple_percent = 0 before any update and dead_tuple_percent = 38.25 after a single bulk update touching every row, real, measured numbers, not an estimate.

Remediation, with real behavior demonstrated. Plain VACUUM scans the table and marks dead tuple space reusable by future inserts and updates, without holding an exclusive lock and without shrinking the file on disk: running VACUUM (VERBOSE) on the bloated table reported tuples: 50000 removed, 50000 remain, matching the 50,000 dead tuples pgstattuple had just measured (the second update's dead versions only, since the first update's were already reclaimed in place by the mechanism above), and pg_total_relation_size immediately afterward was unchanged at 9032 kB, confirming that plain VACUUM frees space for the table to reuse internally but does not return it to the operating system. VACUUM FULL, by contrast, rewrites the entire table into a brand-new file with no wasted space (per the Postgres documentation, this "allows unused space to be returned to the operating system"), and running it on the same table dropped the size to 3288 kB, smaller than the original 3320 kB. The cost: VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table for the duration, per the documentation, versus plain VACUUM, which "does not obtain an exclusive lock" and "can operate in parallel with normal reading and writing of the table."

Trade-offs & pitfalls

• autovacuum should be doing the routine VACUUM work automatically; if n_dead_tup is climbing steadily in pg_stat_user_tables and last_autovacuum is stale or null, that's the first thing to check, not a manual VACUUM schedule. A full maintenance plan also weighs autovacuum tuning, VACUUM FULL risk and reindexing.
• VACUUM FULL's exclusive lock makes it an availability decision, not just a maintenance one: it is real, verified, downtime for that table for however long the rewrite takes. It should be an intentional, scheduled operation on a table that's already badly bloated, not a routine cron job.
• A table with a high dead-tuple percentage isn't necessarily "broken"; a moderate, steady-state percentage that autovacuum keeps in check is normal MVCC behavior, not a defect. The problem is specifically when dead tuples accumulate faster than vacuuming reclaims them.

Direct answer

A database transaction is a group of one or more SQL statements that the database treats as a single unit of work: either every statement in the group takes effect, or none of them do. COMMIT tells the engine "this unit succeeded, make it permanent." ROLLBACK tells it "abandon everything this unit has done so far." That boundary is what stops a multi-step operation that fails halfway through from leaving the table in whatever state the failure happened to catch it in.

Structured elaboration

• What opens and closes a transaction. BEGIN (or START TRANSACTION) opens one; every statement after that runs inside it until an explicit COMMIT or ROLLBACK. If you never issue BEGIN, most engines (Postgres, MySQL, SQL Server) run in autocommit mode: each individual statement is its own implicit transaction, committed the instant it succeeds.
• Why the default is dangerous for multi-step work. Autocommit is fine for one UPDATE. It is not fine for a loop that inserts several related rows per input record: without an explicit boundary, the engine will happily commit record 1's rows, then record 2's, and if record 3 violates a constraint it stops there, leaving records 1 and 2 permanently in the table with nothing recorded to say the run didn't finish.
• What COMMIT guarantees (durability). Before the engine returns success from COMMIT, it has written a durable log record for the change (Postgres calls this the write-ahead log, or WAL) and forced that log to disk. Even if the process crashes a millisecond later, crash recovery replays the log and the row is still there.
• What ROLLBACK guarantees (atomicity). Every write the transaction made, however many statements it took, is discarded as if none of them ran. The table looks exactly like it did before BEGIN, and a SELECT from another session never saw the intermediate state.

Worked example

Consider a nightly job that loads new user signups into user_batch_load(id, email, signup_date), where email is NOT NULL. Three input rows arrive, and the third one has a bad record with a missing email (a real upstream data defect, the kind that shows up in production).

Without a transaction boundary (autocommit, one statement at a time, which is what you get if the loader just runs each INSERT on its own):

INSERT INTO user_batch_load VALUES (1, 'a@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (2, 'b@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (3, NULL,            '2026-09-01');

Run against Postgres 16, the actual output is:

INSERT 0 1
INSERT 0 1
ERROR:  null value in column "email" of relation "user_batch_load" violates not-null constraint
DETAIL:  Failing row contains (3, null, 2026-09-01).

and SELECT * FROM user_batch_load afterward returns rows 1 and 2: the batch is half-loaded, and nothing in the table distinguishes "batch finished successfully with 2 rows" from "batch died partway through."

With an explicit transaction boundary, the same three statements wrapped in BEGIN ... on failure:

BEGIN;
INSERT INTO user_batch_load VALUES (1, 'a@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (2, 'b@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (3, NULL,            '2026-09-01');
-- engine returns the same NOT NULL error, and the session is now in an
-- aborted-transaction state until you issue ROLLBACK (or just disconnect,
-- which implicitly rolls back)

SELECT * FROM user_batch_load afterward returns 0 rows. Both scenarios were run against the same table on the same data; the only difference is the boundary. Because the failed attempt left the table exactly as it was before the run, the retry (after the upstream data is fixed) can be the identical batch with no risk of double-counting rows 1 and 2 a second time.

Trade-offs & pitfalls

• Scope the boundary to the unit of work, not to each statement or to the whole job. Wrapping every single INSERT in its own transaction defeats the point (autocommit already does that). Wrapping an entire multi-hour load in one transaction holds locks and keeps one long-running snapshot open the whole time, which blocks routine table maintenance and can make failure recovery slower, not safer. Commit in logically meaningful chunks (per input file, per batch of N records).
• ORMs and connection pools sometimes leave autocommit on by default; a developer can assume there's an implicit transaction wrapping a request when there isn't. Check the framework's actual default rather than assuming.
• A transaction only undoes database writes. It does not undo side effects outside the database, an email already sent, a message already published to a queue, so a batch job with external side effects needs its own idempotency strategy on top of the DB transaction, not a substitute for one.

Direct answer

The maintenance plan has three tools with three very different risk profiles: routine VACUUM is safe to run constantly and should mostly be left to autovacuum (Postgres's background process that runs VACUUM automatically, without anyone scheduling it); VACUUM FULL reclaims disk space completely but takes the table fully offline for the duration; and REINDEX fixes bloated indexes specifically (bloat: wasted on-disk space left behind by old row or index versions that have not yet been cleaned up), with a low-risk concurrent variant available for exactly the cases where downtime isn't acceptable. The plan is: tune autovacuum so routine VACUUM keeps up on its own, and reserve the two higher-risk operations for tables that are already badly bloated, scheduled deliberately, not run reactively during business hours.

Structured elaboration

What causes the bloat, briefly, since it's the basis for the whole plan. Postgres never updates a row in place; every UPDATE/DELETE leaves a dead version behind, verified directly on a 50,000-row table with autovacuum disabled to observe the raw state: one full-table update pushed its on-disk size from 3320 kB to 6568 kB with the row count unchanged, and a second full-table update, still with no VACUUM run in between, pushed it further to 9032 kB, since Postgres wrote yet another new version of every row without reclaiming anything on disk yet. pgstattuple on a comparable table measured dead_tuple_percent = 38.25 after just one bulk update.

How autovacuum configuration determines how much bloat accumulates. autovacuum triggers a VACUUM on a table once its estimated dead-tuple count crosses a threshold, controlled per-table or database-wide (autovacuum_vacuum_scale_factor plus autovacuum_vacuum_threshold). A high-write table with the default scale factor can accumulate a large absolute number of dead tuples before autovacuum even fires, because the threshold scales with table size; the practical fix for a heavily-updated table is lowering autovacuum_vacuum_scale_factor for that specific table (via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...)) so cleanup runs more often, in smaller increments, rather than letting dead tuples build up between runs.

The maintenance plan, in order of preference:

Trade-offs & pitfalls

• Never run VACUUM FULL reactively on a live table during business hours without first confirming the downtime is acceptable; it is a real, verified full-table lock, not a "slower but online" operation.
• pg_repack isn't installed by default (it wasn't available as an extension in a stock Postgres image tested here); if it's the chosen mitigation for a genuinely low-downtime requirement, it needs to be added to the environment ahead of the incident, not discovered as an option during one.
• The right long-term signal to watch is autovacuum keeping n_dead_tup roughly flat over time in pg_stat_user_tables; if it's trending upward despite autovacuum running, that's the cue to tune the scale factor before reaching for VACUUM FULL at all.

Direct answer

Standard SQL (SQL-92) defines four isolation levels, weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each one is defined by which of the classic anomalies (dirty read, non-repeatable read, phantom read) it still permits; the stronger the level, the fewer anomalies survive, and the more the database has to work to rule them out. PostgreSQL's actual default is READ COMMITTED, and in practice that means every individual statement (not every transaction) sees a fresh snapshot of whatever was committed at the moment that statement started, so a reader never sees another transaction's uncommitted work, but two statements in the same transaction can legitimately see different data if a writer commits in between.

The four levels and what they permit

An anomaly here means a way concurrent transactions can produce a result that would be impossible if transactions ran strictly one at a time. Dirty read: seeing another transaction's uncommitted, possibly-about-to-be-rolled-back change. Non-repeatable read: re-reading the same row twice in one transaction and getting two different (both committed) answers because someone else's write landed in between. Phantom read: re-running the same filtered query twice in one transaction and getting a different set of rows, because someone else inserted or deleted a matching row in between.

PostgreSQL's actual default, and what it guarantees

SHOW default_transaction_isolation; on a stock PostgreSQL 16 instance returns read committed. In practice that means: within one transaction, each new SQL statement gets its own fresh snapshot of the database as of the moment that statement begins, so a reader always sees a consistent, fully-committed view (never a partial or uncommitted write from another session), but it does not promise that two different statements in the same transaction see the same data, because a concurrent writer's commit lands between them.

-- T1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- returns 1000

-- T2 (runs to completion here)
BEGIN; UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;

-- back in T1
SELECT balance FROM accounts WHERE id = 1;   -- returns 900: same transaction, different answer
COMMIT;

This was run against PostgreSQL 16.14 in a throwaway container and printed exactly 1000 then 900, which is the non-repeatable read that READ COMMITTED explicitly permits. Nothing in that sequence is a dirty read: T1 never saw T2's row while T2's transaction was still open; it only saw T2's value after T2 committed.

Trade-offs and pitfalls

One subtlety worth knowing cold: the SQL standard's table says REPEATABLE READ still permits phantom reads, but PostgreSQL's actual REPEATABLE READ is implemented as full-transaction snapshot isolation (the same mechanism SERIALIZABLE builds on, just without the extra conflict-detection layer), so in practice a PostgreSQL REPEATABLE READ transaction that re-runs the same filtered COUNT(*) twice gets the same answer both times, something a strictly standard-conforming engine would not guarantee. Do not assume this generalizes to every engine: MySQL's InnoDB storage engine also calls its default level REPEATABLE READ but achieves phantom protection for locking reads through gap locks, a different mechanism with different side effects (SQL Server is another engine that deviates from the standard's table in the other direction). The second pitfall is assuming READ UNCOMMITTED in PostgreSQL actually gives you dirty reads for a performance win: PostgreSQL accepts the READ UNCOMMITTED keyword but silently treats it exactly like READ COMMITTED (PostgreSQL has no genuinely lock-free dirty-read mode at all), so setting it buys nothing and should not be relied on for a "fast and loose" read path.