A transaction is a group of reads and writes that the database treats as one unit of work: either all of it becomes visible and permanent, or none of it does. ACID is the vocabulary for what that promise contains. Interviewers probe it because almost every backend bug involving money, inventory, or "the row exists but its child rows do not" is really a transaction-boundary bug, and because candidates often recite the four letters without being able to say what mechanism delivers each one. For a Backend or AI engineer this matters daily: an ingestion pipeline that writes a document row and its embedding rows, or an order service that debits stock and records a payment, needs a defensible answer to "what state is left behind if the process dies halfway?"
Move 30 from Alice to Bob:
step 1: UPDATE accounts SET balance = balance - 30 WHERE id = 1; (debit)
step 2: UPDATE accounts SET balance = balance + 30 WHERE id = 2; (credit)
Without a transaction, each statement commits on its own (autocommit):
t0 alice=100 bob=50
t1 step 1 commits alice=70 bob=50 <- 30 has vanished from the system
t2 *** process crashes ***
t3 step 2 never runs alice=70 bob=50 <- permanent, no error anywhere
With a transaction:
BEGIN;
debit;
credit;
COMMIT; -- both changes become visible and durable together
-- or, on any failure:
ROLLBACK; -- both changes disappear as if they never happenedThe transfer is two writes but one business fact. There is no instant at which a crash, a constraint failure, or another user's query should be able to observe or leave behind "money removed but not yet added". A transaction gives the application a way to declare which writes belong to one fact; the database then owns the responsibility of making that declaration hold across errors, crashes and concurrent readers. That is the answer to "why are transactions necessary": partial failure is the normal case in a system with a network, a disk and a process that can be killed, and the database is the only component positioned to make a multi-step change all-or-nothing.
Letter Promise Mechanism (typical relational engines)
------ ----------------------------------- ---------------------------------------------
A all-or-nothing undo information: on ROLLBACK or crash recovery,
uncommitted changes are reversed (InnoDB undo
log; PostgreSQL keeps old row versions and simply
marks the aborting transaction as aborted)
C moves between valid states constraints (PK, FK, UNIQUE, CHECK, NOT NULL)
enforced by the engine + invariants that only
the application knows and must code correctly
I concurrent txns don't corrupt locks and/or MVCC (multi-version concurrency
each other's view control); strength depends on isolation level
D committed = survives a crash write-ahead log (WAL / redo log) flushed to disk
BEFORE COMMIT returns; data pages can be written
lazily because the log can replay themAtomicity and durability share one mechanism family: the write-ahead log. The rule is "log first, data later". Before COMMIT is acknowledged, the log record describing the change is forced to stable storage. If the machine dies right after, recovery replays the log (redo) so committed work is not lost, and uses undo information to reverse transactions that had started but not committed, so half-finished work is not kept. Durability is only as real as the flush: settings such as PostgreSQL's synchronous_commit or InnoDB's innodb_flush_log_at_trx_commit trade a small crash-loss window for lower commit latency, which is a deliberate weakening of D rather than a bug.
Isolation is delivered by a different mechanism: locks (block conflicting access until the holder finishes) and/or MVCC (readers see a consistent snapshot of older row versions instead of waiting). Consistency is the odd one out and is covered next, because it is the letter interviewers use to catch people out.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0) -- database-enforced rule
);
Atomicity says: "the debit and the credit happen together or not at all"
Consistency says: "after the transaction, every declared rule still holds"
Case 1: engine-enforced consistency
debit 500 from a 70 balance -> CHECK (balance >= 0) fails -> statement rejected
atomicity then makes sure the already-applied credit is undone (ROLLBACK)
Case 2: application-level consistency
Rule: "total money across all accounts is unchanged by a transfer"
Bug: code debits 30 but credits 3 -- no constraint can see this
The database happily commits it atomically, durably, in isolation.
ACID's C is not violated by the engine; the application broke the invariant.Atomicity is about the transaction as a unit: partial execution is never visible. Consistency is about the data: a committed transaction must leave the database satisfying its rules. The engine can enforce only the rules it has been told about (constraints, foreign keys, triggers); rules like "the ledger must balance" or "an order's total equals the sum of its lines" are application invariants, and the transaction machinery will preserve a wrong result just as faithfully as a right one. So it is fair to say that C is a joint responsibility: the database guarantees it will not commit something that violates declared constraints, and the application guarantees its transactions, run alone, would take a valid state to another valid state. Note also that this C is unrelated to the C in CAP, which is about linearizability under a network partition.
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- keep this
SAVEPOINT after_fee; -- bookmark inside the txn
UPDATE accounts SET balance = balance - 20 WHERE id = 1; -- suspect step
ROLLBACK TO after_fee; -- undo ONLY the suspect step
-- txn is still open; the first UPDATE is still pending
COMMIT; -- commits the first UPDATE
Autocommit (the default in most clients): every statement is its own transaction.
UPDATE ...; -- commits immediately
UPDATE ...; -- separate transaction, separately committed
Long transaction hazards:
* locks held longer -> other sessions queue up, deadlock chance rises
* PostgreSQL: old row versions cannot be vacuumed while a snapshot might need them
* InnoDB: undo history grows; a huge rollback can take as long as the work itself
* a crash or failover rolls back everything done so farA savepoint is a named marker inside an open transaction that lets you undo a suffix of the work without abandoning the prefix. It is useful for "try this optional step, fall back if it fails" and for batch loaders that want to skip a bad row without losing the previous thousand. Engine behaviour on errors differs and is worth knowing: in PostgreSQL an error inside a transaction puts it in an aborted state where only ROLLBACK or ROLLBACK TO SAVEPOINT is accepted, whereas SQLite and MySQL generally undo just the failing statement and let the transaction continue, so a savepoint there is a deliberate choice rather than an error-recovery necessity.
Choosing boundaries is a design decision. Put in one transaction the writes that must never be observed apart (debit + credit, order + order lines, document + its chunks). Keep out of it anything slow or outside the database: an HTTP call to a payment provider or an LLM inside BEGIN … COMMIT means locks and a connection are held for the duration of somebody else's latency, and if the transaction later rolls back the external side effect cannot be rolled back with it. The usual approach is: do the external call outside, then a short transaction to record the outcome, and use idempotency keys or a saga/outbox when several systems must agree.
COMMIT makes them durable and visible, ROLLBACK discards them.BEGIN, each statement is its own transaction, so a two-step transfer is not atomic.ROLLBACK TO name) while keeping earlier work; after a plain ROLLBACK savepoints are gone too.synchronous_commit = off) can lose the most recent commits on a crash while remaining internally consistent.BEGIN (or the driver starts a transaction implicitly); the engine assigns a transaction id and, depending on the isolation level, a snapshot.COMMIT the engine appends a commit record and flushes the log to stable storage before returning success to the client; only then is the change guaranteed durable.ROLLBACK, or on crash recovery of an uncommitted transaction, the engine uses undo information to reverse the changes and releases the locks; committed transactions missing from the data files are re-applied from the log.-- Runs as-is in: sqlite3 :memory:
.headers on
.mode column
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);
-- 1) Successful transfer of 30
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT * FROM accounts;
-- id owner balance
-- -- ----- -------
-- 1 alice 70
-- 2 bob 80
-- 2) Failing transfer of 500: the credit succeeds, the debit violates the CHECK
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Runtime error: CHECK constraint failed: balance >= 0
SELECT * FROM accounts; -- inside the open txn, the credit is still applied:
-- 1 alice 70
-- 2 bob 580
ROLLBACK;
SELECT * FROM accounts; -- atomicity: the credit is undone too
-- 1 alice 70
-- 2 bob 80
-- 3) Savepoint: partial rollback
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT after_fee;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1; -- 40
ROLLBACK TO after_fee;
SELECT balance FROM accounts WHERE id = 1; -- 60 (only the second UPDATE was undone)
COMMIT;
SELECT * FROM accounts;
-- 1 alice 60
-- 2 bob 80
# Python (sqlite3): explicit transaction + a "crash" with an open transaction.
# Output below is real output from running this script.
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "bank.db")
def conn():
return sqlite3.connect(path, isolation_level=None) # autocommit: we send BEGIN/COMMIT ourselves
c = conn()
c.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
c.execute("INSERT INTO accounts VALUES (1, 100), (2, 50)")
def transfer(c, src, dst, amount):
c.execute("BEGIN")
try:
c.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
c.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
c.execute("COMMIT")
return "committed"
except sqlite3.IntegrityError as e:
c.execute("ROLLBACK")
return f"rolled back ({e})"
print(transfer(c, 1, 2, 30)) # committed
print(list(c.execute("SELECT id, balance FROM accounts"))) # [(1, 70), (2, 80)]
print(transfer(c, 1, 2, 500)) # rolled back (CHECK constraint failed: balance >= 0)
print(list(c.execute("SELECT id, balance FROM accounts"))) # [(1, 70), (2, 80)]
c.execute("BEGIN")
c.execute("UPDATE accounts SET balance = balance - 10 WHERE id = 1")
other = conn()
print("other conn sees (uncommitted change invisible):", list(other.execute("SELECT balance FROM accounts WHERE id = 1")))
# other conn sees (uncommitted change invisible): [(70,)]
c.close() # "crash" with the transaction still open
print("after crash, another connection sees:", list(conn().execute("SELECT balance FROM accounts WHERE id = 1")))
# after crash, another connection sees: [(70,)]
-- PostgreSQL (not run here; behaviour described, no output invented)
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- CHECK violation -> ERROR
SELECT 1; -- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK; -- the only way out (or ROLLBACK TO a savepoint taken before the error)
Q: Explain ACID.
A: Atomicity: a transaction's changes all take effect or none do. Consistency: a committed transaction leaves the data satisfying the declared constraints (and, with the application's help, its business invariants). Isolation: concurrent transactions do not see each other's intermediate state beyond what the chosen isolation level permits. Durability: once COMMIT returns, the change survives a crash, because the log was flushed first. The trade-off to mention is that stronger isolation and stricter flushing both cost throughput or latency, so engines expose knobs for them.
Q: Atomicity vs consistency?
A: Atomicity is about execution: partial work is never left behind, typically via undo and the WAL. Consistency is about the resulting data satisfying rules: constraints the engine enforces, plus invariants only the application knows. A transfer that debits 30 and credits 3 is perfectly atomic and durable but violates the "money is conserved" invariant, which shows the two are independent; atomicity is a tool the database provides, consistency is a joint responsibility.
Q: What is a transaction?
A: A sequence of operations the database treats as one indivisible logical unit, bounded by BEGIN and COMMIT/ROLLBACK, with ACID guarantees. It lets the application declare which writes form a single business fact so partial failure cannot leave a half-applied state. The cost is that it holds locks and resources until it ends, which is why boundaries should be as small as correctness allows.
Q: COMMIT vs ROLLBACK?
A: COMMIT makes the transaction's changes permanent (the log is flushed to stable storage first) and visible to others per their isolation level, then releases locks. ROLLBACK discards all changes since BEGIN using undo information and releases locks. A rollback of a very large transaction is not free: in some engines undoing the work can take about as long as doing it.
Q: What is a savepoint?
A: A named point inside a transaction to which you can roll back with ROLLBACK TO name, undoing only later work while the earlier work stays pending until the final COMMIT or ROLLBACK. It supports "try this step, fall back if it fails" logic and bulk loads that skip bad rows. It does not commit anything, and it is not a substitute for choosing good transaction boundaries.
Q: Why are transactions necessary?
A: Because real operations span multiple statements, and processes, networks and disks fail between any two of them. Without transactions the application would have to build its own recovery and its own protection against concurrent readers and writers, and it could not do so reliably. Transactions push that responsibility into the engine, at the price of lock or version overhead and the need to keep them short.
Follow-up: Does the database guarantee my business rules if I use transactions?
A: Only the ones you have encoded as constraints, and only for the isolation level you chose. A rule like "stock never goes negative" is safe as a CHECK or an atomic conditional update, while "at most 3 doctors on call" spanning several rows can still be broken by concurrent transactions under snapshot isolation (write skew). Transactions make each unit atomic and durable; they do not make arbitrary application logic correct.
Follow-up: How does the database survive a crash between COMMIT and writing the data pages?
A: Because the commit record and the change description are in the write-ahead log, which was flushed before COMMIT returned. On restart, recovery replays the log to bring data pages up to date (redo) and reverses transactions that never committed (undo). That is why data pages can be written lazily and why the log write is the durability point.
Follow-up: Would you wrap a payment-provider HTTP call inside a database transaction?
A: Generally no. The transaction would hold locks and a pooled connection for the remote call's latency, and if the transaction rolls back after the provider has charged the card, the charge cannot be undone by the database. The safer pattern is a short transaction that records intent (with an idempotency key), the external call outside it, and a second short transaction to record the outcome, or an outbox/saga when multiple services must agree.
Transaction boundaries are one of the first things to fix in a system design: the aggregate that must be atomically consistent (an order and its lines, a ledger entry pair, a document and its chunk rows) should live in one database transaction, which usually argues for keeping those tables in the same database and, if you shard, choosing a shard key that keeps them together. Once a business process crosses services or databases, ACID no longer spans it, and you move to sagas, an outbox table written in the same local transaction as the business row, idempotent consumers and compensating actions. For an AI ingestion pipeline the same thinking applies: write the document row, its chunks and the "embedding pending" flag in one short transaction, then let workers do the slow embedding calls outside any transaction and record results in further short ones, so a crash leaves a state you can resume rather than a half-indexed document.
Transactions buy correctness under failure and concurrency; the cost is coordination. The log flush on commit adds latency (group commit and relaxed durability settings reduce it, at the price of possibly losing the last commits on a crash), locks or retained row versions constrain concurrency, and long transactions amplify all of it, including deadlocks, table bloat and slow recovery. Making a transaction bigger is the wrong choice when it drags in slow external work or a large batch that could safely be split into resumable chunks; making it smaller is the wrong choice when it splits writes that must be observed together. Autocommit is fine for single-statement changes and read-only queries, and dangerous for any multi-statement business operation, and even inside a transaction the default isolation level may still allow anomalies that you must handle with locks, atomic updates or a stricter level.