ACID, BASE & Transactions in SQL for Data Engineers

acid sql is the four-letter contract — Atomicity, Consistency, Isolation, Durability — that every relational database honours the moment you wrap statements in BEGIN … COMMIT. Knowing the contract is table stakes. Knowing how each letter is implemented in production SQL — WAL and fsync for Durability, CHECK / FOREIGN KEY / UNIQUE constraints for Consistency, SET TRANSACTION ISOLATION LEVEL for Isolation, ROLLBACK for Atomicity — and how it trades against base properties and the cap theorem when the workload goes global, is the senior data-engineering interview signal panelists actually score on.

This is the deep-dive companion every data engineer eventually needs: a tour through acid transactions with real BEGIN / COMMIT / ROLLBACK blocks in PostgreSQL and MySQL, a climb up the isolation levels ladder from Read Uncommitted through Read Committed, Repeatable Read, and Serializable with the anomalies each rung blocks (dirty read, non-repeatable read, phantom read), a clean derivation of base properties (Basically Available, Soft state, Eventual consistency) from the cap theorem including why most distributed stores live on the AP edge, and a five-dimension ACID vs BASE decision matrix to pick a model per workload rather than per aesthetic. Each section ships SQL or pseudo-SQL you can run today, a step-by-step trace, an output table, and a why this works concept breakdown — the exact shape interview rounds reward.

When you want hands-on reps immediately after reading, browse SQL practice library →, drill database problems →, sharpen aggregation reconciliation patterns →, rehearse joins under isolation →, reinforce data-validation drills →, or widen coverage on the full Python practice library →.

---

On this page

• Why ACID + BASE matter for data engineers
• ACID anatomy — Atomicity, Consistency, Isolation, Durability with SQL examples
• Isolation levels ladder — Read Uncommitted to Serializable, and the anomalies each blocks
• BASE anatomy — Basically Available, Soft state, Eventual consistency (and CAP)
• ACID vs BASE decision matrix — pick by workload, not by aesthetics
• Choosing the right transaction model (cheat sheet)
• Frequently asked questions
• Practice on PipeCode

---

1. Why ACID + BASE matter for data engineers

acid sql and base properties — the two contracts every pipeline implicitly chooses

The one-sentence invariant: every read or write your pipeline issues either lives inside an ACID transaction (and pays for strict guarantees with latency and contention) or sits on a BASE store (and pays for availability with stale reads) — there is no third option, only knobs in between. Data engineers who internalise that one sentence stop arguing about "which is better" and start asking "which is right for this query?" — and that question is the senior signal interviewers score on.

What data engineers actually use ACID for, day-to-day.

• Order checkout flows — debit balance, insert order, decrement inventory, emit event; if any step fails, all must roll back. That is Atomicity in one sentence.
• Money movement — debit account A by $100, credit account B by $100; the books must never reflect a partial transfer. That is Consistency plus Atomicity.
• Snapshot reporting — a 30-second SELECT SUM(amount) … GROUP BY day against a live OLTP table must not see half-applied transfers. That is Isolation.
• Post-restart recovery — if the warehouse instance reboots mid-load, every committed row must still be there when it comes back. That is Durability.
• Schema migrations — wrap the ALTER TABLE, the backfill UPDATE, and the DROP COLUMN in one transaction; either the schema is fully migrated, or the old schema is fully intact.

What data engineers actually use BASE for, day-to-day.

• Activity feeds — a tweet, like, or share that takes 200 ms to be globally visible is fine; a request that fails because one region is partitioned is not.
• IoT telemetry — millions of sensors writing every second; the system must keep accepting writes even if some replicas lag.
• Recommendation caches — a slightly stale "you may like" list beats a 500 error every time.
• Globally distributed reads — read-your-own-write semantics in one region, eventual consistency cross-region; a tunable knob, not a binary.
• Cross-shard analytics ingest — a multi-region Kafka topic into a multi-region warehouse; the consumer never expects all rows to arrive in source order.

Why the choice is structural, not stylistic.

• Latency vs strictness — ACID writes pay for fsync + replica quorum on every commit; BASE writes return as soon as one replica acks.
• Cost of stale reads — for billing, the cost is regulatory or financial; for a feed, it is invisible to the user.
• Geography — speed-of-light forces eventual consistency across continents; you can have C (Consistency) and A (Availability) under a network partition only in one region.
• Workload shape — multi-row, multi-table updates need transactions; single-row, idempotent upserts thrive without them.
• Operational blast radius — an ACID database that goes read-only under partition is safe; a BASE store that keeps serving stale rows is available. Both are correct — for different products.

Worked example — map a single business decision onto ACID + BASE

Detailed explanation. Real interviews probe whether you can apply the contract to a concrete domain. Below is a canonical product spec — "users can transfer money between their own wallets and then immediately see the new balance on their phone" — and how it splits cleanly across an ACID core and a BASE periphery.

Question. A wallet product supports peer-to-peer transfers. The product manager wants (a) transfers to be all-or-nothing and never double-spend, (b) the sender's home screen to show the new balance within 2 seconds, and (c) the global "money moved today" leaderboard to update within 30 seconds. Which parts are ACID and which are BASE?

Input. A wallets table (PostgreSQL, single-region) for balances, a Redis cache for home-screen balance reads, and a globally distributed Kafka topic + ClickHouse for the leaderboard.

Code.

-- ACID core: the actual money movement, in one transaction.
BEGIN;
UPDATE wallets SET balance = balance - 100 WHERE user_id = 'A' AND balance >= 100;
-- 1 row updated, else FAIL and ROLLBACK
UPDATE wallets SET balance = balance + 100 WHERE user_id = 'B';
INSERT INTO ledger (from_id, to_id, amount, ts)
VALUES ('A', 'B', 100, NOW());
COMMIT;

-- BASE periphery: cache invalidation + global emission.
-- 1. Best-effort cache invalidation (eventual consistency is fine)
DEL wallet:A:balance
DEL wallet:B:balance
-- 2. Emit to Kafka, consumed by ClickHouse for the leaderboard
PRODUCE topic=transfers payload={'from':'A','to':'B','amount':100,'ts':...}

Step-by-step explanation.

1. The BEGIN … COMMIT block is the ACID core: balances must never diverge from the ledger, even under crashes or concurrent transfers.
2. The UPDATE … WHERE balance >= 100 check inside the transaction enforces a balance invariant; if the predicate fails, the row count is 0 and the application issues a ROLLBACK.
3. The Redis cache invalidation is BASE: if it fails or arrives 1 second late, the app re-reads from Postgres and corrects itself; nothing is lost.
4. The Kafka emit is BASE: the leaderboard tolerates 30-second lag; consumers can be in any region.
5. The product gets the best of both — strict correctness where it matters (money), elastic latency where it doesn't (cache, leaderboard).

Output (after a successful $100 transfer).

user_id	balance
A	400
B	600

from_id	to_id	amount	ts
A	B	100	2026-05-29 10:00:01

Rule of thumb: one product almost never picks a single model. Senior engineers split features into an ACID core and a BASE periphery; junior engineers force everything into one bucket and pay either with latency or with anomalies.

acid sql mental model in three minutes

The transaction state machine. A SQL transaction is a tiny state machine — BEGIN → (statements) → COMMIT | ROLLBACK — and every guarantee follows from how that machine is implemented.

• BEGIN — opens a new transaction; from this point, your statements see a consistent snapshot of the database depending on the active isolation level.
• Statement N — every INSERT, UPDATE, DELETE is recorded in the write-ahead log (WAL) and held in private undo space until commit.
• COMMIT — flushes the WAL to disk (fsync), releases locks, and replicates to standbys.
• ROLLBACK — discards the private changes; the database is byte-identical to where it was at BEGIN.
• Implicit autocommit — outside an explicit BEGIN, every statement is its own one-statement transaction; great for ad-hoc queries, dangerous for multi-statement business logic.

The four ACID letters as one paragraph. Atomicity says the whole transaction commits or nothing does. Consistency says committed state always satisfies every declared invariant — NOT NULL, UNIQUE, CHECK, FOREIGN KEY, plus any application-level rules enforced through the same constraints. Isolation says concurrent transactions appear to execute as if serialised. Durability says once COMMIT returns, the data survives crashes and reboots. Drop any of those and you no longer have an ACID database — you have a probabilistic store, which is exactly what BASE describes.

SQL
Topic — database
Transaction & database drills

Practice →

SQL
Topic — sql
SQL practice library

Practice →

Solution Using a hybrid ACID-core + BASE-periphery design pattern

Code.

-- One canonical mapping table — every feature is either ACID, BASE, or hybrid.
CREATE TABLE transaction_model_map AS
SELECT * FROM (VALUES
    ('wallet_debit_credit',     'ACID', 'multi-row write',          'postgres single region', 'strict'),
    ('order_checkout',          'ACID', 'multi-table write + event','postgres + outbox',      'strict'),
    ('balance_cache_read',      'BASE', 'single-row read',          'redis',                  'eventual'),
    ('home_feed_read',          'BASE', 'paginated read',           'redis + scylladb',       'eventual'),
    ('leaderboard_aggregate',   'BASE', 'global aggregate',         'kafka -> clickhouse',    'eventual_30s'),
    ('schema_migration',        'ACID', 'DDL + backfill',           'postgres txn',           'strict'),
    ('iot_telemetry_ingest',    'BASE', 'append-only writes',       'kafka -> druid',         'eventual'),
    ('finance_close_recon',     'ACID', 'multi-table aggregate',    'snowflake snapshot iso', 'strict')
) AS t(feature, model, write_shape, store, consistency);

Step-by-step trace.

feature	model	write_shape	store	consistency
wallet_debit_credit	ACID	multi-row write	postgres single region	strict
order_checkout	ACID	multi-table write + event	postgres + outbox	strict
balance_cache_read	BASE	single-row read	redis	eventual
home_feed_read	BASE	paginated read	redis + scylladb	eventual
leaderboard_aggregate	BASE	global aggregate	kafka -> clickhouse	eventual_30s
schema_migration	ACID	DDL + backfill	postgres txn	strict
iot_telemetry_ingest	BASE	append-only writes	kafka -> druid	eventual
finance_close_recon	ACID	multi-table aggregate	snowflake snapshot iso	strict

1. Row 1 — wallet debit/credit is ACID because the invariant "no money disappears" cannot be eventual.
2. Row 2 — order checkout is ACID plus an outbox table for downstream events; the outbox is itself an ACID row.
3. Rows 3-4 — balance and feed reads are BASE because users tolerate <1 second of staleness more than they tolerate errors.
4. Row 5 — leaderboards are BASE with a clearly stated 30-second target; nobody refreshes the page faster than that.
5. Row 6 — schema migrations are ACID because half-migrated schemas break every downstream model.
6. Row 7 — IoT ingest is BASE because partition tolerance and write-availability matter more than ordering.
7. Row 8 — finance close uses ACID snapshot isolation to read a consistent point-in-time.

Output.

feature	model	consistency
wallet_debit_credit	ACID	strict
order_checkout	ACID	strict
balance_cache_read	BASE	eventual
home_feed_read	BASE	eventual
leaderboard_aggregate	BASE	eventual_30s
schema_migration	ACID	strict
iot_telemetry_ingest	BASE	eventual
finance_close_recon	ACID	strict

Why this works — concept by concept:

• Feature-by-feature mapping — turns the abstract ACID-vs-BASE debate into an auditable artefact; every feature is owned by exactly one model.
• Write-shape column — captures the structural reason for the choice; multi-row writes belong in ACID, append-only writes thrive in BASE.
• Store column — pins the model to a concrete store; this is what makes the design reviewable rather than aspirational.
• Consistency column — codifies the SLA (strict, eventual, eventual_30s) so on-call knows what to alert on.
• Cost — O(1) to read the table; the actual transactional cost lives in pg_stat_activity and Kafka consumer lag, not here.

---

2. ACID anatomy — Atomicity, Consistency, Isolation, Durability with SQL examples

acid transactions — four guarantees that turn a database into a contract

acid transactions are the contract that distinguishes a database from a file: every write either lands as part of an all-or-nothing unit (Atomicity), respects every declared invariant (Consistency), behaves as if no other transaction is running (Isolation), and survives any subsequent failure (Durability). Drop one, and you lose the contract.

Atomicity — BEGIN … COMMIT / ROLLBACK as one unit

Detailed explanation. Atomicity is the all-or-nothing guarantee. Either every statement inside the transaction commits, or the database is byte-identical to the state it was in at BEGIN. Under the hood, every write is held in undo space (PostgreSQL: the row's old version in the heap; MySQL InnoDB: the rollback segment) until commit. On ROLLBACK, the undo log is replayed in reverse and the writes vanish.

Question. Show a money-transfer transaction that debits A by 100 and credits B by 100, and demonstrate the ROLLBACK path when A has insufficient funds.

Input.

user_id	balance
A	50
B	500

Code.

BEGIN;

UPDATE wallets SET balance = balance - 100
WHERE user_id = 'A' AND balance >= 100;
-- row count check
SELECT CASE
    WHEN (SELECT changes() FROM (SELECT 1)) = 0
    THEN RAISE(ABORT, 'insufficient_funds')
END;

UPDATE wallets SET balance = balance + 100 WHERE user_id = 'B';

COMMIT;
-- If RAISE fired, the transaction was aborted -> implicit ROLLBACK.

Step-by-step explanation.

1. BEGIN opens a new transaction; writes from this point are private.
2. The first UPDATE filters on balance >= 100; A has 50, so 0 rows are affected.
3. The CASE guard inspects the affected-row count and raises an abort because A is below the threshold.
4. The abort triggers an implicit ROLLBACK; the second UPDATE is never applied.
5. B's balance is unchanged; A's balance is unchanged; the transaction is byte-identical to before BEGIN.

Output (after the aborted transfer).

user_id	balance
A	50
B	500

Rule of thumb: every multi-statement business operation must be wrapped in BEGIN … COMMIT; an unwrapped sequence is two autocommitted statements with a window in between where a crash leaves the books inconsistent.

Common beginner mistakes.

• Forgetting that autocommit is on by default in psql / mysql; each statement is its own transaction unless you explicitly BEGIN.
• Issuing ROLLBACK outside a transaction; some drivers warn, others silently no-op.
• Mixing DDL (ALTER TABLE) and DML in MySQL — most DDL statements implicitly COMMIT the current transaction in MySQL; PostgreSQL DDL is transactional and safer.
• Relying on the application to "undo" a half-applied transaction; the database can do it perfectly with ROLLBACK, your code cannot.

Consistency — declared invariants, enforced on every commit

Detailed explanation. Consistency is the commit-time invariant guarantee. The database refuses to commit any transaction that would leave the data violating a declared constraint — NOT NULL, UNIQUE, CHECK, FOREIGN KEY, exclusion constraints, plus user-defined constraints via triggers. The contract is every committed state is a valid state; the path between two valid states can pass through invalid intermediates inside the transaction, but the moment you say COMMIT, every constraint is verified.

Question. Demonstrate a CHECK constraint that prevents a negative balance from ever being committed, and show what happens when a buggy transaction tries to overdraw.

Input.

user_id	balance
A	50

ALTER TABLE wallets
ADD CONSTRAINT wallets_balance_nonneg CHECK (balance >= 0);

Code.

BEGIN;
UPDATE wallets SET balance = balance - 100 WHERE user_id = 'A';
-- balance is now -50 inside the transaction
COMMIT;
-- ERROR: new row for relation "wallets" violates check constraint
--        "wallets_balance_nonneg"
-- The transaction aborts; A still has 50.

Step-by-step explanation.

1. The CHECK constraint is declared, not enforced by application code; the database is the source of truth.
2. The UPDATE runs and the in-transaction row shows -50.
3. COMMIT evaluates every deferred constraint; balance >= 0 fails.
4. The transaction aborts; the database rolls back automatically.
5. A's balance is still 50; downstream readers never see the invalid -50.

Output (after the aborted commit).

user_id	balance
A	50

Rule of thumb: prefer CHECK / FK / UNIQUE constraints declared on the schema over checks in application code; the database enforces them under every code path, including direct SQL from a DBA.

Common beginner mistakes.

• Enforcing invariants only in the application layer; an ad-hoc DBA UPDATE will bypass them silently.
• Using BEFORE INSERT triggers as a substitute for CHECK; constraints are cheaper, declarative, and easier to read.
• Forgetting DEFERRABLE INITIALLY DEFERRED for FK constraints in two-phase loaders; without it, you can't insert mutually referencing rows.

Isolation — concurrent transactions appear serialised

Detailed explanation. Isolation is the appears-serial guarantee. Concurrent transactions can run in parallel for throughput, but the database must hide the in-flight state of one transaction from the others — to a degree controlled by the isolation level. The four standard levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) trade concurrency for correctness; section 3 covers them in depth. The point here: Isolation is the only ACID letter you tune; the other three are binary.

Question. Two concurrent transactions both read A's balance, then debit by 100. Show why a naive flow can double-debit, and how SELECT … FOR UPDATE fixes it.

Input.

user_id	balance
A	200

Code.

-- Transaction T1                  -- Transaction T2
BEGIN;                              BEGIN;
SELECT balance FROM wallets
  WHERE user_id = 'A'
  FOR UPDATE;
-- row locked; T2 waits           SELECT balance FROM wallets
                                    WHERE user_id = 'A' FOR UPDATE;
                                  -- BLOCKED, waiting on T1
UPDATE wallets SET balance = 100
  WHERE user_id = 'A';
COMMIT;
                                  -- now T2 wakes, sees balance = 100
                                  UPDATE wallets SET balance = 0
                                    WHERE user_id = 'A';
                                  COMMIT;

Step-by-step explanation.

1. T1 issues SELECT … FOR UPDATE and acquires a row lock on A.
2. T2 issues SELECT … FOR UPDATE and blocks because the row is locked.
3. T1 sees balance = 200, sets it to 100, commits — releasing the lock.
4. T2 wakes, re-reads the row, sees the fresh value 100, sets it to 0, commits.
5. Final balance is 0, not -100; the lock prevented the lost-update anomaly.

Output (after both transactions commit).

user_id	balance
A	0

Rule of thumb: the moment you write read-then-write logic on the same row, reach for SELECT … FOR UPDATE or raise the isolation level to Repeatable Read (snapshot in PostgreSQL) or Serializable.

Common beginner mistakes.

• Assuming READ COMMITTED is enough for read-modify-write; it isn't — that's exactly the lost-update window.
• Using SELECT … FOR UPDATE without a transaction; the lock is released the instant the implicit autocommit fires.
• Locking too much by reading whole tables instead of single rows; isolation upgrades work best with targeted locks.

Durability — committed rows survive crashes

Detailed explanation. Durability is the committed-state-survives guarantee. The instant COMMIT returns to the application, the database has persisted the write to a place that survives a process crash, an OS crash, and an instance reboot. The standard implementation is the write-ahead log (WAL in PostgreSQL, redo log in MySQL InnoDB, transaction log in SQL Server) plus fsync of the log file to disk before COMMIT returns. Replication and backups widen the survival domain — but the base contract is one local fsync.

Question. Sketch the write path of a single UPDATE from the moment the app issues COMMIT to the moment the row is durable on disk, and explain what synchronous_commit = on actually buys you.

Input. A single-row UPDATE wallets SET balance = 100 WHERE user_id = 'A'; issued in synchronous_commit = on mode on PostgreSQL with one synchronous standby.

Code.

-- Application
BEGIN;
UPDATE wallets SET balance = 100 WHERE user_id = 'A';
COMMIT;
-- COMMIT returns here, only after:
--   1) WAL record is fsync'd to local disk
--   2) Synchronous standby acks the WAL record

Step-by-step explanation.

1. UPDATE modifies the in-memory page and appends a WAL record to the WAL buffer.
2. COMMIT writes the WAL buffer to the local WAL file and calls fsync.
3. fsync returns only after the OS confirms the bytes are on stable storage.
4. With synchronous_commit = on plus a synchronous standby, the primary also waits for the standby to ack the WAL record.
5. Only then does COMMIT return to the application; the row is durable on at least two machines.

Output (after a crash + restart).

user_id	balance
A	100

Rule of thumb: the difference between synchronous_commit = on and off is the difference between never losing a committed row and losing the last few milliseconds of commits on a crash. Finance picks on, analytics picks off; never silently default.

Common beginner mistakes.

• Confusing Durability with backup; the WAL gives durability for committed rows, backup gives recoverability for whole databases.
• Disabling fsync for "speed" without understanding what's being traded — you've left ACID for BASE without saying so.
• Storing the WAL on the same physical disk as the data files; a single-disk failure can lose both.

Solution Using a BEGIN … COMMIT block that exercises all four letters

Code.

-- One canonical transfer that exercises A, C, I, D in a single block.
BEGIN;
-- I: SELECT FOR UPDATE locks the sender row -> Isolation
SELECT balance FROM wallets
WHERE user_id = 'A' FOR UPDATE;

-- A: both UPDATEs commit together or not at all -> Atomicity
UPDATE wallets SET balance = balance - 100
WHERE user_id = 'A' AND balance >= 100;

UPDATE wallets SET balance = balance + 100
WHERE user_id = 'B';

INSERT INTO ledger (from_id, to_id, amount, ts)
VALUES ('A', 'B', 100, NOW());

-- C: CHECK (balance >= 0) + FK (user_id) verified at commit -> Consistency
COMMIT;
-- D: WAL fsync + replica ack on commit -> Durability

Step-by-step trace.

step	action	acid letter	observable effect
1	BEGIN	—	new private snapshot
2	SELECT … FOR UPDATE	I	row A locked
3	UPDATE … balance - 100 WHERE balance >= 100	A + C	A debited, balance stays >= 0
4	UPDATE … balance + 100	A	B credited
5	INSERT ledger …	A	audit row written
6	COMMIT	C + D	constraints verified, WAL fsynced, replica acked

1. BEGIN starts the transaction; nothing is visible to other connections yet.
2. SELECT … FOR UPDATE takes a row lock on A; concurrent transfers from A queue behind us.
3. The debit UPDATE enforces the balance >= 100 predicate as part of the WHERE clause; combined with the CHECK (balance >= 0) constraint, it guards the invariant from two angles.
4. The credit UPDATE and INSERT INTO ledger ride the same transaction.
5. COMMIT validates constraints, flushes the WAL, waits for the synchronous replica, then returns; the lock on A is released.

Output.

user_id	balance
A	400
B	600

from_id	to_id	amount	ts
A	B	100	2026-05-29 10:01:00

Why this works — concept by concept:

• Atomicity — both UPDATEs and the INSERT INTO ledger ride one BEGIN … COMMIT; a crash anywhere leaves the books byte-identical to the pre-BEGIN state.
• Consistency — the CHECK (balance >= 0) constraint plus the WHERE balance >= 100 predicate prevent any committed state where a wallet is negative.
• Isolation — SELECT … FOR UPDATE serialises concurrent transfers from the same sender; the lost-update anomaly cannot occur.
• Durability — synchronous_commit = on plus a synchronous standby means the transfer survives both local crash and primary failure.
• Cost — one fsync + one network round-trip to the standby per commit; ~1-2 ms on modern hardware, the dominant cost in OLTP latency budgets.

SQL
Topic — database
ACID transaction drills

Practice →

SQL
Topic — sql
SQL transaction practice

Practice →

---

3. Isolation levels ladder — Read Uncommitted to Serializable, and the anomalies each blocks

isolation levels — four rungs, three anomalies, one ladder

isolation levels are the only ACID guarantee you tune at runtime. The ANSI SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — each blocking a strictly larger set of anomalies at the cost of strictly less concurrency. Modern engines also add Snapshot Isolation (via MVCC) slotted around Repeatable Read, which is what most data engineers actually run in production.

The three classic anomalies.

• dirty read — your transaction reads a row that another transaction has written but not yet committed; if the writer rolls back, you've read a value that never existed.
• non-repeatable read — you read the same row twice in one transaction and get two different committed values, because another transaction committed in between.
• phantom read — you run the same WHERE predicate twice and the second run returns extra rows, because another transaction INSERTed matching rows in between.

The ladder, rung by rung.

level	dirty read	non-repeatable read	phantom read	typical default
Read Uncommitted	possible	possible	possible	rarely chosen
Read Committed	blocked	possible	possible	PostgreSQL, SQL Server, Oracle
Repeatable Read	blocked	blocked	possible (some engines block)	MySQL InnoDB, MariaDB
Serializable	blocked	blocked	blocked	strict / interactive money flows

Setting the level in SQL.

-- Postgres / MySQL / SQL Server — per-transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... statements ...
COMMIT;

-- Postgres also supports session-level default:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Per-connection in MySQL:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• Per-transaction wins — the SET must come before BEGIN and binds the next transaction only; the rest of the session reverts to default.
• Engine defaults differ — PostgreSQL defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read; never assume.
• Snapshot Isolation — PostgreSQL's Repeatable Read is actually Snapshot Isolation under the hood, which blocks phantom reads in practice; the standard says it's allowed.
• Real-world picking guide — most OLTP runs at Read Committed; raise to Serializable only when a known anomaly is unacceptable (e.g. finance closes, idempotent ledger writes).

Read Uncommitted — the rung nobody picks intentionally

Detailed explanation. Read Uncommitted allows your transaction to see uncommitted writes from other transactions — the dirty-read anomaly. It is the lowest rung and the highest concurrency, but the cost is reading values that never existed if the writer rolls back. Most engines either don't implement it at all (PostgreSQL silently upgrades it to Read Committed) or expose it for backwards compatibility.

Question. Show a dirty read with two concurrent transactions where T1 reads an uncommitted value that T2 later rolls back.

Input.

user_id	balance
A	100

Code.

-- T2: writer
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE wallets SET balance = 500 WHERE user_id = 'A';
-- (does NOT commit yet)

-- T1: reader
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM wallets WHERE user_id = 'A';
-- returns 500  <-- DIRTY READ
COMMIT;

-- T2 decides to abort
ROLLBACK;
-- A.balance is back to 100; T1 saw a value that never existed.

Step-by-step explanation.

1. T2 starts and updates A to 500 inside a transaction; the update is private.
2. T1 starts in Read Uncommitted and reads A; with this level, it sees T2's uncommitted 500.
3. T1 commits, having based its logic on 500.
4. T2 hits an error and ROLLBACKs; A reverts to 100.
5. T1's downstream decisions are based on a value the database now denies ever existed.

Output (after both transactions resolve).

user_id	balance
A	100

Rule of thumb: never set Read Uncommitted intentionally. The performance win is microscopic; the correctness cost is unbounded.

Common beginner mistakes.

• Picking Read Uncommitted to "read fast" on a reporting query; reach for a read replica or snapshot isolation instead.
• Believing PostgreSQL gives you dirty reads at this level — it doesn't; it silently runs at Read Committed.

Read Committed — the default and the lost-update trap

Detailed explanation. Read Committed is the most common default. It blocks dirty reads — every SELECT sees only committed data — but each statement gets a fresh snapshot, so reading the same row twice in one transaction can return two different values (the non-repeatable read anomaly). The classic trap at this level is the lost update: read-modify-write on the same row from two concurrent transactions can overwrite each other.

Question. Show a lost-update at Read Committed and how SELECT … FOR UPDATE fixes it.

Input.

user_id	balance
A	200

Code.

-- T1                                T2
BEGIN;                              BEGIN;
SELECT balance FROM wallets         SELECT balance FROM wallets
  WHERE user_id = 'A';                WHERE user_id = 'A';
-- returns 200                      -- returns 200

-- both compute new = 200 - 100 = 100
UPDATE wallets SET balance = 100    UPDATE wallets SET balance = 100
  WHERE user_id = 'A';                WHERE user_id = 'A';
COMMIT;                             COMMIT;
-- Final balance = 100, but TWO transfers happened: should be 0.

Step-by-step explanation.

1. Both T1 and T2 read A.balance = 200 in their own snapshots.
2. Both compute new = 200 - 100 = 100 client-side.
3. Both UPDATE A to 100; the second UPDATE overwrites the first.
4. Both COMMIT; the ledger records two debits but the wallet shows only one.
5. The fix is SELECT … FOR UPDATE or raising the isolation to Repeatable Read / Serializable.

Output (after both transactions commit).

user_id	balance
A	100

Rule of thumb: Read Committed is fine for read-only or single-statement writes (UPDATE … WHERE balance >= 100 is atomic per row). For multi-step read-modify-write, add FOR UPDATE or raise the level.

Common beginner mistakes.

• Assuming Read Committed "stops anomalies" because the docs say it blocks dirty reads; it doesn't block non-repeatable reads, phantoms, or lost updates.
• Skipping FOR UPDATE because the read "seems quick"; concurrency is exactly when the bug happens.

Repeatable Read — snapshot isolation in practice

Detailed explanation. Repeatable Read guarantees that every read inside the transaction sees the same committed snapshot taken at the moment the transaction started. PostgreSQL and Oracle implement this as MVCC snapshot isolation — each transaction sees a frozen view; writes by other committed transactions are invisible. MySQL InnoDB's Repeatable Read adds gap locks that also block most phantom reads. The cost: write-write conflicts surface as serialization failures, and your app must retry.

Question. Show a transaction that reads, computes, and writes safely under Repeatable Read with explicit retry on a serialization failure.

Input.

user_id	balance
A	200

Code.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM wallets WHERE user_id = 'A';
-- returns 200 from the snapshot; ANOTHER tx commits 100 in the meantime
SELECT balance FROM wallets WHERE user_id = 'A';
-- still returns 200 (snapshot is frozen)
UPDATE wallets SET balance = 100 WHERE user_id = 'A';
COMMIT;
-- ERROR: could not serialize access due to concurrent update
-- application catches the SQLSTATE 40001 and RETRIES the whole txn.

Step-by-step explanation.

1. The transaction takes a snapshot at BEGIN; both reads see 200 even if another transaction commits a different value.
2. The UPDATE discovers a conflicting committed write since the snapshot was taken.
3. PostgreSQL raises a serialization failure (SQLSTATE 40001); the transaction aborts.
4. The application catches the error and retries the whole transaction from BEGIN.
5. On retry, the snapshot is fresh; the lost-update anomaly is impossible.

Output (after a successful retry).

user_id	balance
A	0

Rule of thumb: whenever you set Repeatable Read or higher, the application must retry on SQLSTATE 40001. Production frameworks (SQLAlchemy, Django, ActiveRecord) ship retry decorators for exactly this.

Common beginner mistakes.

• Setting Repeatable Read and not catching serialization errors; the app crashes instead of retrying.
• Confusing PostgreSQL's Repeatable Read (snapshot isolation) with MySQL's Repeatable Read (gap locks); behaviour around phantom reads differs.

Serializable — the top rung and its cost

Detailed explanation. Serializable is the highest standard level: the execution must be equivalent to some serial order of the concurrent transactions. PostgreSQL implements it via Serializable Snapshot Isolation (SSI), which monitors read-write dependencies between concurrent transactions and aborts one if a serialization conflict is detected. The cost: more serialization failures and lower throughput. The reward: the strongest correctness guarantee SQL provides, with no anomalies of any kind.

Question. Two concurrent transactions both check a balance and insert a ledger row; show how Serializable detects a read-write dependency cycle and aborts one.

Input.

user_id	balance
A	100

Code.

-- T1                                T2
SET TRANSACTION ISOLATION LEVEL     SET TRANSACTION ISOLATION LEVEL
  SERIALIZABLE;                       SERIALIZABLE;
BEGIN;                              BEGIN;
SELECT balance FROM wallets         SELECT balance FROM wallets
  WHERE user_id = 'A';                WHERE user_id = 'A';
-- 100                              -- 100
INSERT INTO ledger (user_id, amt)   INSERT INTO ledger (user_id, amt)
VALUES ('A', -100);                 VALUES ('A', -100);
UPDATE wallets SET balance = 0      UPDATE wallets SET balance = 0
  WHERE user_id = 'A';                WHERE user_id = 'A';
COMMIT;                             COMMIT;
-- ERROR: could not serialize access
-- one of the two aborts; the other commits.

Step-by-step explanation.

1. Both T1 and T2 read A.balance = 100 under their own snapshots.
2. Both insert a ledger row and update A.balance to 0.
3. PostgreSQL's SSI detects that the two transactions have a read-write dependency cycle (each read the value the other wrote).
4. One transaction is allowed to commit; the other is aborted with SQLSTATE 40001.
5. The application retries the aborted transaction; on retry it sees the post-commit balance and either skips the debit or fails cleanly.

Output (after one commit + one retry-fail).

user_id	balance
A	0

user_id	amt
A	-100

Rule of thumb: pick Serializable for money flows where double-spend is unacceptable and you can afford a small retry rate. For high-throughput non-financial workloads, Read Committed plus explicit FOR UPDATE or idempotent upserts is usually a better fit.

Common beginner mistakes.

• Setting Serializable globally and being surprised by the retry rate under load.
• Forgetting to wrap the transaction in a retry loop; the very feature that makes Serializable correct also makes it noisy without retries.

Solution Using SERIALIZABLE + a retry loop for a money transfer

Code.

import psycopg2
from psycopg2 import errors

def transfer(conn, from_id: str, to_id: str, amount: float, attempts: int = 3) -> bool:
    for attempt in range(attempts):
        try:
            with conn:  # auto-commit / rollback
                cur = conn.cursor()
                cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                cur.execute(
                    "SELECT balance FROM wallets WHERE user_id = %s",
                    (from_id,),
                )
                bal = cur.fetchone()[0]
                if bal < amount:
                    raise ValueError("insufficient_funds")
                cur.execute(
                    "UPDATE wallets SET balance = balance - %s WHERE user_id = %s",
                    (amount, from_id),
                )
                cur.execute(
                    "UPDATE wallets SET balance = balance + %s WHERE user_id = %s",
                    (amount, to_id),
                )
                cur.execute(
                    "INSERT INTO ledger (from_id, to_id, amount) VALUES (%s, %s, %s)",
                    (from_id, to_id, amount),
                )
            return True  # committed
        except errors.SerializationFailure:
            continue  # retry the whole txn
    return False  # gave up

Step-by-step trace.

attempt	event	outcome
1	BEGIN SERIALIZABLE	snapshot taken
1	read A.balance	200
1	update A, B, insert ledger	private writes
1	COMMIT	SerializationFailure raised due to concurrent transfer
2	BEGIN SERIALIZABLE	fresh snapshot
2	read A.balance	100 (concurrent commit visible)
2	update A, B, insert ledger	private writes
2	COMMIT	success

1. Attempt 1 starts under Serializable; PostgreSQL takes a fresh snapshot.
2. The transfer logic runs against the snapshot and prepares the writes.
3. On COMMIT, SSI detects a dependency cycle with a concurrent transfer; the txn is aborted.
4. The except clause catches SerializationFailure and retries the whole block.
5. Attempt 2 sees the committed state from the concurrent transfer; the transfer succeeds.

Output (after attempt 2 commits).

user_id	balance
A	0
B	700

from_id	to_id	amount
A	B	100

Why this works — concept by concept:

• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE — the strongest standard guarantee; equivalent to some serial order of the concurrent transactions.
• Serializable Snapshot Isolation — PostgreSQL's implementation tracks read-write dependencies; aborts the loser of any cycle.
• Retry loop — turns a SerializationFailure from a crash into a transient event; without it, Serializable is unusable under load.
• Per-transaction SET — keeps the rest of the session at the default level (typically Read Committed); avoids global throughput collapse.
• Cost — typically <1% retry rate for short transactions on warm workloads; pay it on money flows, skip it on analytics reads.

SQL
Topic — database
Isolation-level drills

Practice →

SQL
Topic — joins
Joins under concurrency

Practice →

---

4. BASE anatomy — Basically Available, Soft state, Eventual consistency (and CAP)

base properties — born from the CAP theorem

base properties are the design counter-weight to ACID: Basically Available (the system always answers, even degraded), Soft state (replica state may drift between writes), Eventual consistency (replicas converge once writes stop). The trio falls naturally out of the cap theorem — Eric Brewer's 2000 conjecture, formalised in 2002 by Gilbert and Lynch — which says a distributed store can pick at most two of Consistency, Availability, and Partition tolerance under a network partition. Since partitions are inevitable on a global network, real systems pick CP (ACID-shaped) or AP (BASE-shaped).

The three letters, one paragraph each.

• Basically Available — the system always responds to every request; under a partition or replica failure, responses may be degraded (a stale read, a 503 with cached fallback) but never absent. Compare with strict ACID, which would refuse to serve under quorum loss.
• Soft state — replica state is not required to be identical between writes; replicas may diverge for a window. This is a deliberate design choice: it lets each replica accept writes locally without waiting for a global lock.
• Eventual consistency — given enough time without new writes, every replica converges to the same value. The convergence window is the design knob: milliseconds (single-region with anti-entropy) up to seconds (cross-region with async replication).

cap theorem in one minute.

• C (Consistency) — every read sees the most recent committed write; equivalent to linearizability for single-key reads.
• A (Availability) — every request receives a non-error response within a bounded time.
• P (Partition tolerance) — the system continues to operate despite arbitrary message loss between nodes.
• The rule — under a partition, you must choose either consistency or availability; you cannot have both. Outside a partition, you can have all three; the theorem is about the partitioned regime.
• CP examples — PostgreSQL with synchronous replication, ZooKeeper, Spanner (with TrueTime); under partition, minority side returns errors.
• AP examples — Cassandra, DynamoDB, Riak; under partition, all sides keep accepting writes; conflicts resolve later via last-write-wins or CRDTs.

PACELC — the practical extension.

• Under Partition, choose A or C (the CAP part).
• Else (no partition), Latency or Consistency — even with no partition, strong consistency costs round-trips; eventual consistency is faster.
• PA/EL — Cassandra, DynamoDB; avail under partition, latency-optimised normally.
• PC/EC — Spanner, FaunaDB; consistent under partition, consistent normally.
• PA/EC — MongoDB (default); available under partition, consistent normally.
• The practical interview answer: "I think in PACELC, not CAP, because I trade latency for consistency every day even with no partition in sight."

Basically Available — degraded responses beat errors

Detailed explanation. Basically Available is the always answer guarantee. Even when a node is down, a region is partitioned, or replicas are out of sync, the system returns something: a stale read, a fallback list, an older version of the cached page. The contract is no errors due to coordination; the implementation is local writes plus async replication plus tunable read quorums.

Question. A globally distributed user_profile_cache runs on three regions. Region B is partitioned from A and C. How does a BASE store still answer reads in region B?

Input. Cassandra cluster with replication_factor = 3 (one per region), read consistency LOCAL_ONE for warm reads, QUORUM for cold reads.

Code.

-- Local read in region B (still works, returns possibly-stale data)
SELECT * FROM user_profiles
  WHERE user_id = 'u_123'
  USING CONSISTENCY LOCAL_ONE;

-- Cross-region quorum read (FAILS while B is partitioned)
SELECT * FROM user_profiles
  WHERE user_id = 'u_123'
  USING CONSISTENCY QUORUM;
-- error: cannot achieve quorum

Step-by-step explanation.

1. Under LOCAL_ONE, the read targets only the local replica in region B.
2. Even with the cross-region link down, B has a local replica with a (possibly stale) profile.
3. The read succeeds in single-digit milliseconds with the stale data.
4. The same query under QUORUM requires 2 of 3 replicas; with B partitioned from A and C, the cross-region acks can't return.
5. The system trades freshness for availability — the BASE choice.

Output (under partition, LOCAL_ONE).

user_id	name	last_seen
u_123	Asha	2026-05-29 09:55:00 (stale by 5 min)

Rule of thumb: tune consistency per query. LOCAL_ONE for hot-path reads, QUORUM for writes that must not be lost, ALL for the few correctness-critical reads.

Common beginner mistakes.

• Using ONE consistency everywhere "for speed"; you may read your own writes one in three times.
• Using ALL consistency everywhere "for safety"; you lose the availability you adopted Cassandra to get.

Soft state — replicas drift between writes

Detailed explanation. Soft state says the cluster's state at rest is allowed to drift between writes. There is no global lock that forces every replica to be byte-identical at every microsecond; each replica records the writes it has seen and gossips them outward. The system catches up via anti-entropy (background read-repair, Merkle tree exchanges, hinted handoff) without blocking the user-facing path.

Question. A Cassandra cluster has three replicas of a key. A write goes to replica A under CONSISTENCY ONE. Show why the other two replicas may temporarily diverge and how anti-entropy reconciles them.

Input. Three replicas of key = 'k1', all initially holding value = 'v0'.

Code.

-- Client write to one replica.
INSERT INTO kv (k, v) VALUES ('k1', 'v1') USING CONSISTENCY ONE;
-- replica A: v1, replicas B and C: still v0
-- (soft state: cluster is briefly inconsistent)

-- Background anti-entropy (hinted handoff + read-repair) eventually carries
-- v1 to B and C; meanwhile, a LOCAL_ONE read to B returns 'v0'.

-- A QUORUM read repairs on the fly:
SELECT v FROM kv WHERE k = 'k1' USING CONSISTENCY QUORUM;
-- coordinator reads from any 2; sees (A=v1, B=v0); returns v1
-- and writes v1 back to B in the background.

Step-by-step explanation.

1. The write under ONE returns as soon as A acks.
2. B and C still hold v0; the cluster is in soft-state divergence.
3. A LOCAL_ONE read to B returns v0 — the stale value.
4. A QUORUM read forces the coordinator to read from 2 replicas, detects the divergence, returns the latest value, and triggers a background read-repair.
5. After read-repair (or after gossip / hinted-handoff fires), all three replicas converge to v1.

Output (after anti-entropy).

replica	k	v
A	k1	v1
B	k1	v1
C	k1	v1

Rule of thumb: embrace soft state as a feature, not a bug — it is what gives BASE stores their write availability. Tune the convergence window with CONSISTENCY and read_repair_chance.

Common beginner mistakes.

• Expecting INSERT … VALUES (…) to be globally durable like in PostgreSQL; in Cassandra it depends on the requested consistency.
• Disabling read-repair "for speed"; without it, stale replicas can serve old data indefinitely.

Eventual consistency — replicas converge once writes stop

Detailed explanation. Eventual consistency is the convergence guarantee: given a period without new writes to a key, every replica eventually returns the same value. "Eventually" is the entire design knob — milliseconds with anti-entropy on a single-region cluster, seconds with async cross-region replication, longer for offline mobile clients. Modern systems offer tunable consistency (per-query knobs like read-your-writes, monotonic reads, bounded staleness) so you can climb back toward stronger guarantees per workload.

Question. Demonstrate a read-your-writes read against DynamoDB where the client wants to be sure it reads the value it just wrote.

Input. A DynamoDB table user_profiles with last_login written 50 ms ago.

Code.

import boto3
dyn = boto3.client('dynamodb')

# 1. The write
dyn.put_item(TableName='user_profiles', Item={
    'user_id': {'S': 'u_123'},
    'last_login': {'S': '2026-05-29T10:00:00Z'}
})

# 2. Default eventually-consistent read (may return stale)
r1 = dyn.get_item(
    TableName='user_profiles',
    Key={'user_id': {'S': 'u_123'}},
    ConsistentRead=False,
)
# r1 may NOT contain the just-written last_login

# 3. Strongly-consistent read (read-your-writes)
r2 = dyn.get_item(
    TableName='user_profiles',
    Key={'user_id': {'S': 'u_123'}},
    ConsistentRead=True,
)
# r2 is guaranteed to contain the just-written last_login

Step-by-step explanation.

1. The PutItem writes to a coordinator and returns; one or more replicas may not yet have the value.
2. ConsistentRead=False is the default; it may read from a replica that hasn't received the write yet.
3. ConsistentRead=True forces a read from the leader / strongly-consistent replica; the client pays 2x the RCU cost but reads-its-own-write.
4. The application picks per query: hot paths use False, money paths use True.
5. The same pattern shows up in Cassandra (QUORUM), MongoDB (readConcern: "majority"), Cosmos DB (consistency levels).

Output (after both reads).

read	ConsistentRead	last_login
r1	false	2026-05-29T09:55:00Z (stale)
r2	true	2026-05-29T10:00:00Z (fresh)

Rule of thumb: eventual consistency is a budget, not a default. Set the convergence target per workload (100 ms for in-region, 1 s for cross-region, 30 s for analytics) and let the platform pick the cheapest mechanism that meets it.

Common beginner mistakes.

• Assuming "eventual" means "within a second" everywhere; cross-region replication can take seconds under load.
• Mixing strongly-consistent and eventually-consistent reads on the same query path; users see flicker as the read source changes.

Solution Using a tunable-consistency design per workload

Code.

def read_balance(user_id: str, fresh: bool = False) -> float:
    """Read a wallet balance from DynamoDB.

    fresh=True  -> ConsistentRead=True   (use after own write)
    fresh=False -> ConsistentRead=False  (use for hot-path display)
    """
    r = dyn.get_item(
        TableName='wallets',
        Key={'user_id': {'S': user_id}},
        ConsistentRead=fresh,
    )
    return float(r['Item']['balance']['N'])

def transfer(from_id: str, to_id: str, amount: float) -> None:
    # money path -> strongly consistent reads
    src = read_balance(from_id, fresh=True)
    if src < amount:
        raise ValueError("insufficient_funds")
    # atomic conditional write to prevent double-spend
    dyn.transact_write_items(TransactItems=[
        {'Update': {
            'TableName': 'wallets',
            'Key': {'user_id': {'S': from_id}},
            'UpdateExpression': 'SET balance = balance - :a',
            'ConditionExpression': 'balance >= :a',
            'ExpressionAttributeValues': {':a': {'N': str(amount)}},
        }},
        {'Update': {
            'TableName': 'wallets',
            'Key': {'user_id': {'S': to_id}},
            'UpdateExpression': 'SET balance = balance + :a',
            'ExpressionAttributeValues': {':a': {'N': str(amount)}},
        }},
    ])

def display_balance_for_home(user_id: str) -> float:
    # display path -> eventually consistent (cheap, fast)
    return read_balance(user_id, fresh=False)

Step-by-step trace.

call	path	ConsistentRead	cost	freshness
transfer -> read_balance(fresh=True)	money	true	2x RCU	latest
transfer -> transact_write_items	money	n/a	strongly consistent	atomic
display_balance_for_home -> read_balance(fresh=False)	display	false	1x RCU	eventual

1. transfer calls read_balance(fresh=True) to get the leader-read balance; required for the precondition check.
2. The transact_write_items is a DynamoDB transactional write across two items; ACID-shaped inside a BASE store.
3. display_balance_for_home calls read_balance(fresh=False) for the hot-path read; pays 1x RCU.
4. The application code chooses per workload; the store provides the knob.

Output.

call	balance read	next action
transfer.fresh	500.00	proceed with debit
transfer.write	n/a	atomic update
display_balance_for_home	400.00 or 500.00	render to UI

Why this works — concept by concept:

• Tunable consistency per call — the knob is at the API call site, not the cluster default; this is the modern BASE pattern.
• Transactional writes on a BASE store — DynamoDB Transactions, Cassandra LWT, MongoDB transactions; ACID-shaped writes on top of BASE replication.
• ConditionExpression — the precondition balance >= :a enforces the invariant at write time; equivalent to a CHECK constraint in SQL.
• Hot vs cold split — display paths read cheaply and tolerate staleness; money paths pay for freshness.
• Cost — strongly-consistent reads are 2x the cost of eventual reads on most stores; transactional writes are 2-3x; budget accordingly.

SQL
Topic — data-validation
Consistency validation drills

Practice →

SQL
Topic — database
Database / replication drills

Practice →

---

5. ACID vs BASE decision matrix — pick by workload, not by aesthetics

acid vs base — five dimensions, one decision per workload

acid vs base is never one decision for the whole system. It is a per-workload, sometimes per-query, decision. The matrix that follows captures the five dimensions that matter — read pattern, write pattern, geography, cost of staleness, and best-fit workload — and lays each against the canonical ACID and BASE answer. Memorise the matrix; senior interview answers cite the exact dimension that flipped the decision.

The matrix.

dimension	ACID (strict guarantees)	BASE (eventually correct)
Read pattern	strong consistency required	tolerates stale reads
Write pattern	multi-row, multi-table txns	single-row, idempotent upserts
Geography	single region preferred	global replication friendly
Cost of staleness	high — money, regulations	low — likes, feeds, recs
Best-fit workload	banking, billing, inventory	social, IoT, analytics ingest

Stack-by-stack answer.

• Postgres / MySQL / SQL Server / Oracle → ACID by default; pick Serializable for money flows, Repeatable Read for snapshot reads, Read Committed for everything else.
• Cassandra / ScyllaDB / DynamoDB / Riak → BASE by default; reach for LWT / transact_write_items / transactions for the few items that need ACID-shaped writes.
• MongoDB → BASE-leaning, but multi-document ACID transactions since 4.0; use them for state machines, otherwise stick with idempotent upserts.
• Spanner / CockroachDB / TiDB / YugabyteDB → globally distributed CP; ACID across regions at the cost of higher write latency.
• Cosmos DB → fully tunable; pick from Strong, Bounded staleness, Session, Consistent prefix, Eventual per request.
• Kafka + a sink (Snowflake, BigQuery, ClickHouse) → BASE at ingest, ACID inside the warehouse; the warehouse is the system of record for analytics.

The decision tree, in five questions.

1. Can the user tolerate a stale read for this query? → No → ACID; Yes → BASE candidate.
2. Is this write multi-row or multi-table? → Yes → ACID; No → BASE candidate.
3. Is the workload global / multi-region? → Yes → BASE or CP-distributed; No → single-region ACID.
4. Is the cost of being wrong measured in dollars or regulations? → Yes → ACID with Serializable; No → BASE.
5. Is this a state machine or an append-only stream? → State machine → ACID; stream → BASE.

Pattern — wallets are ACID, activity feeds are BASE

Detailed explanation. A single product almost always splits into ACID and BASE features. The pattern below shows the canonical split in a fintech app: the wallet (ACID) and the activity feed (BASE).

Question. A fintech app has (a) wallet balances and money movements, (b) a transaction history list shown on the user's phone. Where does each belong?

Input. PostgreSQL for wallets + ledger; Redis + ScyllaDB for the feed cache.

Code.

-- ACID: wallet + ledger in one transaction (Postgres)
BEGIN;
UPDATE wallets SET balance = balance - 100
WHERE user_id = 'A' AND balance >= 100;
UPDATE wallets SET balance = balance + 100 WHERE user_id = 'B';
INSERT INTO ledger (from_id, to_id, amount, ts)
VALUES ('A', 'B', 100, NOW());
COMMIT;

-- BASE: activity feed write (ScyllaDB, eventually consistent)
INSERT INTO activity_feed (user_id, txn_id, type, amount, ts)
VALUES ('A', 't_001', 'transfer_out', 100, NOW())
USING CONSISTENCY LOCAL_ONE;

Step-by-step explanation.

1. The Postgres block enforces the money-movement invariants (Atomicity, Consistency, Isolation, Durability).
2. After the Postgres COMMIT, an out-of-band consumer (CDC, Debezium, or an outbox poller) emits a feed write.
3. The feed write lands in ScyllaDB under LOCAL_ONE; it returns in single-digit ms.
4. The feed may take 100-300 ms to fully replicate across regions; users in remote regions see a tiny lag.
5. The split is correct: the truth lives in Postgres (ACID); the display lives in ScyllaDB (BASE).

Output (after both writes).

user_id	balance	source
A	400	postgres (truth)
B	600	postgres (truth)

user_id	txn_id	type	amount
A	t_001	transfer_out	100

Rule of thumb: the system of record is always ACID; the read model / cache / feed is usually BASE. The CDC (or outbox) is the bridge.

Common beginner mistakes.

• Storing the wallet balance in the cache as the source of truth; the cache will diverge, and reconciliation is brutal.
• Skipping the outbox table and double-writing from the app to both Postgres and ScyllaDB; one of the two writes will fail and you'll lose events.

Pattern — order checkout uses ACID + an outbox to bridge to BASE

Detailed explanation. The outbox pattern is the canonical way to ride a BASE downstream from an ACID upstream. The trick: the event is written to an outbox table inside the same Postgres transaction as the business write; an external worker polls the outbox and publishes to Kafka.

Question. Show an order-checkout transaction that places the order, decrements inventory, and atomically enqueues a OrderPlaced event to Kafka via the outbox pattern.

Input. Postgres tables orders, inventory, outbox; a Kafka topic orders.events.

Code.

BEGIN;

INSERT INTO orders (order_id, user_id, total, status)
VALUES ('o_1', 'u_1', 99.50, 'pending');

UPDATE inventory SET qty = qty - 1
WHERE sku = 'sku_42' AND qty > 0;

INSERT INTO outbox (event_id, topic, payload, created_at)
VALUES (
    gen_random_uuid(),
    'orders.events',
    '{"order_id":"o_1","user_id":"u_1","total":99.50}',
    NOW()
);

COMMIT;
-- A separate worker SELECTs from outbox, publishes to Kafka,
-- then UPDATEs / DELETEs the row.

Step-by-step explanation.

1. The transaction either commits all three writes — orders, inventory, outbox — or none.
2. The outbox row is the durable signal that the event must be published.
3. A separate worker process polls the outbox table, publishes each row to Kafka, then marks it as published.
4. If the worker crashes mid-publish, the row stays unpublished and is retried; the worker is at-least-once, the consumer must be idempotent.
5. The combined system is transactionally consistent upstream + eventually consistent downstream — the cleanest ACID→BASE bridge.

Output (after the COMMIT).

order_id	user_id	total	status
o_1	u_1	99.50	pending

sku	qty
sku_42	99

event_id	topic	payload
uuid-…	orders.events	{…}

Rule of thumb: whenever a transaction needs to emit an event downstream, use the outbox. Direct produce(...) calls inside a transaction are a classic dual-write bug.

Common beginner mistakes.

• Producing to Kafka from inside the transaction; the produce call cannot be rolled back if the transaction aborts.
• Skipping the unique constraint on event_id; the worker's at-least-once delivery will produce duplicates that the consumer must deduplicate.

Solution Using a per-workload ACID-vs-BASE decision table

Code.

-- A decision table you can hand to a new engineer in any architecture review.
CREATE TABLE workload_decision AS
SELECT * FROM (VALUES
    ('wallet_transfer',         'ACID', 'postgres serializable',  'SELECT FOR UPDATE + CHECK + retry'),
    ('order_checkout',          'ACID', 'postgres + outbox',      'multi-table txn + outbox bridge'),
    ('payment_settlement',      'ACID', 'postgres serializable',  'idempotency key + retry'),
    ('home_feed_render',        'BASE', 'redis -> scylladb',      'LOCAL_ONE, write-through cache'),
    ('global_leaderboard',      'BASE', 'kafka -> clickhouse',    'append-only, async aggregate'),
    ('iot_telemetry_ingest',    'BASE', 'kafka -> druid',         'partition by device, idempotent upserts'),
    ('audit_log',               'ACID', 'postgres append-only',   'no DELETE, FK to source'),
    ('search_index_update',     'BASE', 'cdc -> opensearch',      'eventual, reindex on schema change'),
    ('reporting_snapshot',      'ACID', 'snowflake snapshot iso', 'snapshot read at run start'),
    ('mobile_offline_sync',     'BASE', 'crdt or last-write-wins','conflict-free merge')
) AS t(workload, model, store, key_pattern);

Step-by-step trace.

workload	model	store	key_pattern
wallet_transfer	ACID	postgres serializable	SELECT FOR UPDATE + CHECK + retry
order_checkout	ACID	postgres + outbox	multi-table txn + outbox bridge
payment_settlement	ACID	postgres serializable	idempotency key + retry
home_feed_render	BASE	redis -> scylladb	LOCAL_ONE, write-through cache
global_leaderboard	BASE	kafka -> clickhouse	append-only, async aggregate
iot_telemetry_ingest	BASE	kafka -> druid	partition by device, idempotent upserts
audit_log	ACID	postgres append-only	no DELETE, FK to source
search_index_update	BASE	cdc -> opensearch	eventual, reindex on schema change
reporting_snapshot	ACID	snowflake snapshot iso	snapshot read at run start
mobile_offline_sync	BASE	crdt or last-write-wins	conflict-free merge

1. ACID rows all have multi-row or multi-table writes and high cost of staleness; the trade-off picks itself.
2. BASE rows all have single-row or append-only writes and low cost of staleness.
3. The key_pattern column is the implementation shortcut — what shape the code takes given the model choice.
4. The reporting snapshot is interesting: ACID isolation (snapshot read) on top of an eventually consistent ingest.
5. Mobile offline sync is interesting: BASE by necessity (offline = partitioned) plus CRDTs to make the conflict resolution deterministic.

Output.

workload	model	store
wallet_transfer	ACID	postgres serializable
order_checkout	ACID	postgres + outbox
home_feed_render	BASE	redis -> scylladb
global_leaderboard	BASE	kafka -> clickhouse
iot_telemetry_ingest	BASE	kafka -> druid

Why this works — concept by concept:

• Per-workload decision — turns the abstract debate into a table reviewers can argue about line by line; promotes from opinion to data.
• Model + store + key pattern — three columns capture the entire design: what guarantee, which engine, what code shape.
• Implicit cost column — every model has an implied cost (latency for ACID, staleness for BASE); the key-pattern column reflects which cost the team accepted.
• Hybrid first-class — order_checkout is ACID + outbox bridge; this is the modern pattern and the senior interview answer.
• Cost — O(1) to read the table at design time; the real costs (txn throughput, replica lag) show up in monitoring and are reviewed quarterly.

SQL
Topic — database
ACID vs BASE design drills

Practice →

SQL
Topic — aggregation
Aggregation under consistency

Practice →

---

Choosing the right transaction model (cheat sheet)

A one-screen cheat sheet for acid sql and base properties — pick by the failure mode you cannot tolerate.

You want to …	Model	Canonical primitive	Engine default
Move money between accounts	ACID Serializable	BEGIN … COMMIT + SELECT FOR UPDATE + retry on 40001	Postgres / SQL Server
Decrement inventory on checkout	ACID Read Committed + row predicate	UPDATE … WHERE qty > 0	Postgres / MySQL
Run a 30-second reporting query against live OLTP	ACID Repeatable Read (snapshot)	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	Postgres / Snowflake
Block dirty reads (the easy win)	ACID Read Committed	engine default	Postgres / SQL Server
Block non-repeatable reads	ACID Repeatable Read / Snapshot Iso	snapshot taken at BEGIN	Postgres / MySQL
Block phantom reads	ACID Serializable	SSI + dependency tracking	Postgres
Bridge ACID upstream to BASE downstream	Hybrid	outbox table + CDC worker	Postgres + Kafka
Render a hot-path home feed	BASE eventual	CONSISTENCY LOCAL_ONE	Cassandra / ScyllaDB / Redis
Read your own write on a cache	BASE → tunable strong	ConsistentRead=True / readConcern: majority	DynamoDB / MongoDB
Accept writes during a partition	BASE	local quorum + async replication	Cassandra / Dynamo
Ingest IoT telemetry	BASE append-only	Kafka producer with idempotent semantics	Kafka + Druid
Run a global leaderboard	BASE eventual	Kafka stream + windowed aggregate	Kafka + ClickHouse
Reconcile finance close at month-end	ACID snapshot	snapshot read at job start	Snowflake / BigQuery
Globally distributed strong consistency	CP-distributed	Spanner / CockroachDB / TiDB	per-engine
Per-request tunable consistency	tunable	Strong / Bounded staleness / Session / Eventual	Cosmos DB

---

Frequently asked questions

What does ACID stand for in SQL, in one sentence each?

Atomicity — every statement inside BEGIN … COMMIT either commits as a unit or rolls back as a unit; there is no "halfway". Consistency — every committed state satisfies every declared invariant (NOT NULL, UNIQUE, CHECK, FOREIGN KEY, plus user-defined rules enforced through constraints or triggers). Isolation — concurrent transactions appear to execute as if some serial order produced the same result; the level is tunable via SET TRANSACTION ISOLATION LEVEL. Durability — once COMMIT returns, the write survives crashes, reboots, and (with synchronous replication) primary failure. Drop any one and you no longer have an ACID database — you have a probabilistic store, which is exactly the BASE design space.

How are ACID guarantees actually implemented under the hood?

Atomicity is implemented via undo logs (Postgres MVCC row versions, MySQL InnoDB rollback segments) plus two-phase commit when distributed. Consistency is implemented as constraint validation at commit time — the engine evaluates every CHECK, FK, UNIQUE and exclusion constraint before the WAL record is finalised. Isolation is implemented via locking (row, range, table) plus MVCC (each transaction reads a consistent snapshot of committed data); the level dictates which combination. Durability is implemented via the write-ahead log (WAL in Postgres, redo log in InnoDB, transaction log in SQL Server) — every commit forces an fsync of the WAL before returning, and synchronous replicas extend the durability domain to a second machine. Knowing these four mechanisms by name is the difference between a junior and a senior database answer in an interview.

What are the four SQL isolation levels and what does each block?

The ANSI SQL standard defines four levels, climbing from least to most strict. Read Uncommitted allows dirty reads, non-repeatable reads, and phantom reads — nobody picks this intentionally; Postgres silently runs it as Read Committed. Read Committed blocks dirty reads but allows non-repeatable and phantom reads — it is the default in Postgres, SQL Server, and Oracle; safe for most reads, dangerous for multi-step read-modify-write. Repeatable Read blocks dirty and non-repeatable reads; in MySQL InnoDB and Postgres (where it is implemented as Snapshot Isolation), it also blocks phantoms in practice. Serializable blocks all three — equivalent to some serial execution order of the concurrent transactions — at the cost of more serialization failures that the app must retry. Pick Serializable for money flows where double-spend is unacceptable; everywhere else, Read Committed with explicit SELECT … FOR UPDATE on the critical row is usually the right call.

What is the CAP theorem and how does it relate to BASE?

The CAP theorem says a distributed data store can pick at most two of Consistency (every read sees the most recent write), Availability (every request gets a non-error response), and Partition tolerance (the system continues despite network drops). Since real distributed networks always have partitions eventually, the practical choice under partition is between CP (refuse to serve on the minority side, like Spanner or synchronous Postgres) and AP (keep serving stale data, like Cassandra or DynamoDB). BASE — Basically Available, Soft state, Eventual consistency — is the design philosophy that flows from picking AP: prioritise availability, accept temporary divergence, converge eventually. The PACELC extension reminds you that even without a partition, you trade latency vs consistency; that knob is real every microsecond, not just during network failures.

When should I pick ACID vs BASE for a new system?

Pick ACID when the cost of being wrong is measured in dollars, regulations, or user trust: money movement, inventory decrements, order state machines, audit logs, schema migrations, finance reconciliation. Pick BASE when the cost of being slightly stale is measured only in user friction: activity feeds, recommendations, leaderboards, IoT telemetry ingest, search indexes, cross-region read replicas. Most real systems do both — an ACID core (Postgres / MySQL / SQL Server) for the system of record plus a BASE periphery (Redis, Cassandra, ScyllaDB, Kafka + ClickHouse) for the read paths and downstream consumers. The outbox pattern is the canonical bridge: write the business row and a downstream event in one ACID transaction, then ride a worker to publish the event to a BASE store. Senior architects never argue "ACID vs BASE for the whole system" — they decide per workload, often per query.

What's the difference between Serializable and Snapshot Isolation?

Snapshot Isolation (Postgres Repeatable Read, MySQL InnoDB Repeatable Read, Oracle Serializable, SQL Server Snapshot) gives every transaction a frozen snapshot of committed data taken at BEGIN; concurrent writes are invisible. It blocks dirty reads, non-repeatable reads, and most phantom reads, but it allows the write-skew anomaly: two transactions can read each other's data, write disjoint rows, and produce a state no serial order could. Serializable (Postgres Serializable Snapshot Isolation, SQL Server Serializable with key-range locks) adds a final check that the schedule is equivalent to some serial order; in Postgres SSI, that means tracking read-write dependencies and aborting a transaction whose commit would produce an anomaly. The trade-off: Snapshot Isolation has higher throughput and rarely aborts; Serializable is the only level that fully prevents write-skew but has a higher serialization-failure rate that the app must retry. For money flows: Serializable. For most analytics: Snapshot Isolation is the sweet spot.

---

Practice on PipeCode

PipeCode ships 450+ data-engineering interview problems — including SQL + Python drills keyed to the same acid sql, acid transactions, isolation levels, and base properties mental model this guide teaches (transactions and rollback, snapshot reads, lost-update prevention, serializable retries, idempotent BASE upserts, CAP / PACELC reasoning, and the ACID-core + BASE-periphery bridge via the outbox pattern). Whether you're prepping for a senior data-engineering interview the night before or building the transactional core of a production wallet over 12 months, the practice library mirrors the same five-section mental model — plus the Postgres, MySQL, Cassandra, DynamoDB, Kafka, and Snowflake tooling you'll wire into your own systems.