Transactions and ACID Properties in SQL Explained

Why Transactions Exist

A transaction groups multiple SQL statements into a single, all-or-nothing unit of work. Without transactions, a multi-step operation — like transferring money between two bank accounts — could fail halfway through, leaving the database in an inconsistent state: money debited from one account but never credited to the other. Transactions are the mechanism that makes that impossible.

The Basic Transaction Pattern

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;   -- both updates become permanent together

-- If anything goes wrong before COMMIT:
ROLLBACK;  -- undoes every change made since BEGIN, as if none of it happened

If the application crashes, a constraint is violated, or the connection drops between the two UPDATE statements and before COMMIT, the database automatically rolls back — the first debit never becomes visible or permanent on its own.

ACID: The Four Guarantees

Atomicity

A transaction either completes entirely or has no effect at all — there's no partial state where only some of its statements took effect. The bank transfer example above is the canonical illustration: both updates happen, or neither does.

Consistency

A transaction moves the database from one valid state to another, never violating any constraint, trigger, or business rule along the way. If a CHECK constraint says balances can't go negative, no transaction is allowed to commit a state where they do — see our constraints guide for how these rules are enforced.

Isolation

Concurrent transactions shouldn't see each other's uncommitted changes — the exact degree of isolation is configurable (see below), but the core promise is that transactions behave, to some degree, as if they ran one at a time even when they're actually running concurrently.

Durability

Once a transaction commits, its changes survive a crash, a power loss, or a server restart. This is typically achieved through a write-ahead log (WAL) — changes are recorded to durable storage before the commit is acknowledged to the application, so a crash immediately after commit can still recover the change on restart.

Isolation Levels: Trading Consistency for Concurrency

Full isolation (as if every transaction ran completely alone, one after another) is expensive — it requires heavy locking that kills concurrency. Real databases offer tunable isolation levels that trade strictness for throughput:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

• Dirty read — reading another transaction's uncommitted changes, which might later be rolled back, leaving you having acted on data that never actually existed.
• Non-repeatable read — re-reading the same row twice within one transaction and getting different values, because another transaction committed a change in between your two reads.
• Phantom read — re-running the same query twice within one transaction and getting a different set of rows, because another transaction inserted or deleted matching rows in between.

Defaults vary by database: PostgreSQL and Oracle default to Read Committed; MySQL's InnoDB defaults to Repeatable Read (and additionally prevents most phantom reads via a technique called next-key locking, going beyond the standard SQL guarantee for that level). Most applications never change the default, and doing so should be a deliberate, informed decision — not a default tweak.

-- Explicitly setting isolation level for a transaction (PostgreSQL / standard SQL syntax)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;

Pessimistic vs. Optimistic Locking

• Pessimistic locking — acquire a lock on a row before modifying it, blocking other transactions from touching it until you're done. Use SELECT ... FOR UPDATE to explicitly lock rows you're about to change.
• Optimistic locking — don't lock anything upfront; instead, check at commit time whether the data changed since you read it (typically via a version column), and retry if it did. Better suited to low-contention scenarios where conflicts are rare.

-- Pessimistic: lock the row for the duration of this transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Optimistic: check a version number hasn't changed since you last read it
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 7;   -- fails to match (0 rows updated) if someone else already changed it
-- application code checks the row count and retries if it's 0

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed — the database detects this cycle and forcibly rolls back one of them as the "deadlock victim." Prevent deadlocks by consistently accessing tables and rows in the same order across every transaction in your codebase, and by keeping transactions short — acquire locks as late as possible, release them as early as possible.

Keeping Transactions Short

A long-running transaction holds its locks (and, on some databases, prevents old row versions from being cleaned up) for as long as it's open — this can quietly degrade concurrency and bloat storage across the whole database, not just the table involved.

• Never hold a transaction open across a slow external call (an API request, a file upload, waiting on user input) — do that work first, then open the transaction only for the actual database writes
• Batch large bulk operations into smaller transactions rather than one enormous one, both for lock duration and for keeping rollback cost manageable if something fails partway

Common Mistakes

• Holding a transaction open during slow, non-database work — blocks other transactions far longer than necessary.
• Not handling deadlock errors with a retry — deadlocks are an expected, normal part of concurrent systems, not a bug; application code should catch the specific error and retry the transaction.
• Assuming Serializable isolation everywhere is "safer" with no downside — it significantly increases lock contention and can hurt throughput; use it deliberately for the specific operations that truly need it.
• Forgetting that a SELECT inside a transaction can still be affected by isolation level — under Read Committed, two identical SELECTs in the same transaction can return different results if another transaction commits in between (a non-repeatable read).

Common Interview Questions

1. Explain the four ACID properties with an example. Use the bank transfer scenario — walk through what would go wrong for each property if it were missing.
2. What's the difference between a dirty read, a non-repeatable read, and a phantom read? Covered in detail above — the key distinction is what kind of change the second transaction makes (uncommitted value, updated row, or inserted/deleted row).
3. What isolation level does your database default to, and what does it protect against? Know your specific database's default (PostgreSQL/Oracle: Read Committed; MySQL InnoDB: Repeatable Read) and what phenomena it does and doesn't prevent.
4. How would you prevent a deadlock in application code that transfers money between two accounts? Always lock/update the lower account ID first, consistently, across every code path — this guarantees every transaction acquires locks in the same global order.

Frequently Asked Questions

Do NoSQL databases have ACID transactions too?

It varies widely — some (like modern MongoDB, for multi-document transactions) support ACID-style guarantees within certain scopes; many NoSQL systems historically traded strict ACID guarantees for horizontal scalability. See our SQL vs. NoSQL guide for more on this tradeoff.

Is Serializable isolation the same as literally running transactions one at a time?

It guarantees the same outcome as if transactions ran one at a time, but the database doesn't necessarily achieve that by actually serializing execution — it uses techniques like detecting conflicting access patterns and aborting/retrying transactions that would violate serializability.

Practice Transaction Design

Understanding transactions and isolation levels is essential for any backend or data engineering role working with concurrent writes. Try related SQL patterns hands-on with our SQL practice questions, or work through realistic multi-step business scenarios in our case studies.