Transactions & ACID

Ensure data integrity and consistency.

Why Transactions Matter

A transaction is a sequence of database operations that are treated as a single unit. Either all operations succeed, or none do, there's no half-finished state. This is critical for maintaining data integrity. Imagine transferring money between bank accounts: you can't deduct from one account and fail to add to the other. Transactions guarantee that related operations complete together or fail together.

The Problem: Inconsistent Data

Without transactions, operations can fail partway through, leaving data inconsistent.

Money Transfer Without Transaction

-- Step 1: Deduct from Alice
UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1;
-- ✓ Success: Alice now has $900

-- Step 2: Add to Bob
UPDATE accounts SET balance = balance + 100 
WHERE account_id = 2;
-- ✗ ERROR: Server crash!

Expected Output:

Result:
Alice: $1000 → $900  (money deducted)
Bob:   $500  → $500  (money never added)
Total: $1500 → $1400 ❌ Money disappeared!

Disaster: $100 vanished from the system! This is why we need transactions.

Using Transactions

Wrap related operations in BEGIN and COMMIT to make them atomic.

Money Transfer With Transaction

BEGIN;  -- Start transaction

-- Step 1: Deduct from Alice
UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1;

-- Step 2: Add to Bob
UPDATE accounts SET balance = balance + 100 
WHERE account_id = 2;

COMMIT;  -- Make changes permanent

Expected Output:

If both succeed:
Alice: $1000 → $900
Bob:   $500  → $600
Total: $1500 → $1500 ✓ Balanced!

If either fails:
Alice: $1000 (unchanged)
Bob:   $500  (unchanged)
Total: $1500 ✓ Rolled back, no data lost

Rolling Back on Error

BEGIN;

UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1;

UPDATE accounts SET balance = balance + 100 
WHERE account_id = 999;  -- Oops! Account doesn't exist

ROLLBACK;  -- Undo everything

Both operations cancelled, data remains consistent

The ACID Properties

ACID is an acronym for four properties that guarantee reliable transactions.

AAtomicity: All or Nothing

A transaction is indivisible. Either all operations complete successfully, or none do.

BEGIN;
INSERT INTO orders (...);      -- ✓
INSERT INTO order_items (...); -- ✓
UPDATE inventory (...);        -- ✗ FAILS
ROLLBACK;

Result: All 3 operations cancelled (atomic)

CConsistency: Valid State to Valid State

Database moves from one valid state to another. All constraints, triggers, and rules are enforced.

BEGIN;
UPDATE accounts SET balance = -50  -- Violates CHECK constraint
WHERE account_id = 1;
COMMIT;

Result: Transaction rejected, constraint enforced

IIsolation: Transactions Don't Interfere

Concurrent transactions don't see each other's uncommitted changes. Each transaction appears to run alone.

Transaction 1:              Transaction 2:
BEGIN;                      BEGIN;
UPDATE accounts             SELECT balance FROM accounts
SET balance = 900           WHERE account_id = 1;
WHERE account_id = 1;       
                            Result: 1000 (not 900)
                            -- Can't see uncommitted change
COMMIT;                     COMMIT;

DDurability: Changes Persist

Once committed, changes are permanent. They survive crashes, power failures, and restarts.

BEGIN;
UPDATE accounts SET balance = 900;
COMMIT;  -- ✓ Written to disk

-- Power failure occurs here!

-- After restart:
SELECT balance FROM accounts;
Result: 900 (change persisted)

Transaction Commands

BEGIN / START TRANSACTION

Start a new transaction.

BEGIN;              -- PostgreSQL, MySQL
START TRANSACTION;  -- Standard SQL; works in PostgreSQL, MySQL
BEGIN TRANSACTION;  -- SQL Server; also valid in PostgreSQL

-- Set the isolation level on the same statement:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

COMMIT

Make all changes permanent.

COMMIT;  -- Save all changes

ROLLBACK

Undo all changes in the transaction.

ROLLBACK;  -- Cancel all changes

SAVEPOINT

Create a checkpoint within a transaction to rollback to.

BEGIN;

UPDATE accounts SET balance = 900 WHERE account_id = 1;
SAVEPOINT after_first_update;

UPDATE accounts SET balance = 600 WHERE account_id = 2;
SAVEPOINT after_second_update;

UPDATE accounts SET balance = -100 WHERE account_id = 3;
-- Oops! Invalid operation

ROLLBACK TO after_second_update;  -- Undo only the last change
COMMIT;  -- Save first two updates

Expected Output:

BEGIN
UPDATE 1
SAVEPOINT
UPDATE 1
SAVEPOINT
ERROR:  new row for relation "accounts" violates check constraint
        "accounts_balance_check"
DETAIL:  Failing row contains (3, -100).

-- try any statement now, before rolling back:
ERROR:  current transaction is aborted, commands ignored until end of
        transaction block

ROLLBACK          <- this is ROLLBACK TO after_second_update
COMMIT

┌────────────┬─────────┐
│ account_id │ balance │
├────────────┼─────────┤
│          1 │     900 │
│          2 │     600 │
│          3 │     750 │
└────────────┴─────────┘

This is the part worth remembering. In PostgreSQL a failed statement
poisons the WHOLE transaction: every later command is refused until you
end it. Without a savepoint your only option is ROLLBACK, losing all
three updates. ROLLBACK TO rewinds just past the error and makes the
transaction usable again, so accounts 1 and 2 keep their new balances
and account 3 is untouched at 750.

Partial rollback while keeping some changes

Isolation Levels: Trading Safety for Speed

Perfect isolation is slow. Databases offer different isolation levels to balance consistency with performance.

The SQL standard defines each level by which anomalies it is allowed to permit. An engine is free to prevent more than the minimum, and PostgreSQL does. Read this table as two separate things: what the standard permits, and what you actually get.

Level	Dirty Read	Non-Repeatable Read	Phantom Read	What PostgreSQL actually does
READ UNCOMMITTED	❌ Allowed	❌ Allowed	❌ Allowed	Accepted as a synonym for READ COMMITTED. Dirty reads never occur
READ COMMITTED	✓ Prevented	❌ Allowed	❌ Allowed	As described. This is the default
REPEATABLE READ	✓ Prevented	✓ Prevented	❌ Allowed	Implemented as snapshot isolation, so phantoms are also prevented. Can fail with a serialization error you must retry
SERIALIZABLE	✓ Prevented	✓ Prevented	✓ Prevented	Adds predicate tracking (SSI) to catch write-skew, which snapshot isolation alone misses

The practical consequence: above READ COMMITTED, PostgreSQL can abort your transaction with ERROR: could not serialize access due to concurrent update. That is not a bug, it is how the level keeps its promise. Any code using REPEATABLE READ or SERIALIZABLE needs a retry loop.

Read Uncommitted (Lowest Isolation)

In the standard, this level may expose uncommitted changes from other transactions. PostgreSQL does not implement it as such, as the run below shows.

-- The isolation level belongs ON the BEGIN, or in a SET issued
-- after it. Run SET TRANSACTION outside a transaction block and
-- PostgreSQL warns and ignores you:
--   WARNING:  SET TRANSACTION can only be used in transaction blocks

Transaction 1:                     Transaction 2:
BEGIN;                             BEGIN TRANSACTION ISOLATION LEVEL
UPDATE accounts                          READ UNCOMMITTED;
SET balance = 500
WHERE id = 1;
-- not committed yet
                                   SELECT balance FROM accounts
                                   WHERE id = 1;
ROLLBACK;

Expected Output:

 transaction_isolation
-----------------------
 read uncommitted

 what_session_b_sees
---------------------
                1000

In the ANSI standard this level permits a dirty read, and Transaction 2
would have seen the uncommitted 500. PostgreSQL returns 1000.

PostgreSQL accepts the READ UNCOMMITTED keyword for compatibility and
then silently gives you READ COMMITTED. Its MVCC design never exposes
uncommitted row versions to another transaction, so a dirty read is not
merely discouraged here, it is unimplementable. Oracle behaves the same
way. If you want to see a real dirty read you need MySQL, SQL Server,
or another engine that offers it.

Read Committed (Default in PostgreSQL)

Only see committed changes. No dirty reads.

-- This is the default, so you rarely write it out.
-- BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

Transaction 1:          Transaction 2:
BEGIN;                  BEGIN;
UPDATE accounts         
SET balance = 500       
WHERE id = 1;           
                        SELECT balance FROM accounts 
                        WHERE id = 1;
                        Result: 1000 (sees old value)
COMMIT;                 
                        SELECT balance FROM accounts 
                        WHERE id = 1;
                        Result: 500 (sees new value after commit)

Serializable (Highest Isolation)

Transactions appear to run one at a time. Safest but slowest.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--  ... your statements ...
COMMIT;

-- Transactions behave as if they ran one after another. PostgreSQL
-- does not achieve this by blocking; it lets them run concurrently
-- and aborts one if the interleaving could not have happened
-- serially. So COMMIT itself can fail:
--   ERROR:  could not serialize access due to read/write dependencies
--           among transactions
--   HINT:  The transaction might succeed if retried.
-- Always wrap SERIALIZABLE work in a retry loop.

Concurrency Problems

Dirty Read

Reading uncommitted data that might be rolled back. Worth knowing as a concept, but as shown above you cannot produce one in PostgreSQL at any isolation level.

Transaction A updates balance to $500 (not committed)
Transaction B reads balance: $500
Transaction A rolls back
Transaction B used invalid data!

Non-Repeatable Read

Same query returns different results within one transaction.

Transaction A reads balance: $1000
Transaction B updates balance to $500 and commits
Transaction A reads balance again: $500
Same query, different result!

Phantom Read

New rows appear in query results within one transaction.

Transaction A: SELECT COUNT(*) FROM orders → 10 rows
Transaction B: INSERT INTO orders (...) and commits
Transaction A: SELECT COUNT(*) FROM orders → 11 rows
Row appeared out of nowhere!

Locking Strategies

Databases use locks to prevent conflicts between concurrent transactions.

Pessimistic Locking

Lock data when you read it, preventing others from modifying.

BEGIN;
SELECT * FROM accounts 
WHERE account_id = 1 
FOR UPDATE;  -- Lock this row

-- Other transactions wait until this commits
UPDATE accounts SET balance = balance - 100
WHERE account_id = 1;

COMMIT;  -- Lock released

Optimistic Locking

Don't lock, but check if data changed before updating.

-- Add version column
ALTER TABLE accounts ADD COLUMN version INTEGER DEFAULT 1;

-- Read data and version
SELECT balance, version FROM accounts WHERE account_id = 1;
-- Result: balance=1000, version=1

-- Update only if version hasn't changed
UPDATE accounts 
SET balance = 900, version = version + 1
WHERE account_id = 1 AND version = 1;

-- If 0 rows updated, someone else modified it
-- Retry or abort

Good for low-conflict scenarios

Deadlocks: When Transactions Block Each Other

A deadlock occurs when two transactions wait for each other's locks.

Transaction 1:              Transaction 2:
BEGIN;                      BEGIN;
UPDATE accounts             UPDATE accounts
SET balance = 900           SET balance = 1100
WHERE id = 1;               WHERE id = 2;
-- Locks row 1              -- Locks row 2

UPDATE accounts             UPDATE accounts
SET balance = 600           SET balance = 800
WHERE id = 2;               WHERE id = 1;
-- Waits for row 2 lock    -- Waits for row 1 lock

❌ DEADLOCK! Neither can proceed

Expected Output:

Transaction 1 completes normally:
  UPDATE 1
  COMMIT

Transaction 2 is chosen as the victim and killed:

  ERROR:  deadlock detected
  DETAIL:  Process 6328 waits for ShareLock on transaction 5145;
           blocked by process 6321.
           Process 6321 waits for ShareLock on transaction 5146;
           blocked by process 6328.
  HINT:  See server log for query details.
  CONTEXT:  while updating tuple (0,1) in relation "dl"
  ROLLBACK

"Both wait forever" is the textbook description, not what happens.
PostgreSQL notices the cycle after deadlock_timeout (1 second by
default), aborts one transaction, and lets the other finish. The
survivor never knows anything happened. The victim gets SQLSTATE
40P01, which is exactly the error your retry logic should catch.

Deadlock: The Circular Wait

Figure 1: Each transaction holds one row and waits for the other's. Neither can proceed, so the database detects the cycle and aborts one.

Deadlock Prevention

Lock in same order: Always lock rows in the same order (e.g., by ID)
Keep transactions short: Less time holding locks = fewer deadlocks
Let the database break them, and retry: PostgreSQL runs deadlock detection after deadlock_timeout (1s by default) and aborts one transaction with ERROR: deadlock detected. Your job is to catch that and retry, not to prevent it by hand
Bound the waiting separately: lock_timeout andstatement_timeout cap how long a statement blocks. These are for ordinary lock contention, and are a different mechanism from deadlock detection

Transaction Best Practices

✅ Keep Transactions Short

Long transactions hold locks longer, blocking other transactions. Do only what's necessary, then commit.

✅ Always Use Transactions for Related Changes

Any sequence of operations that must succeed together should be in a transaction. Bank transfers, order + inventory updates, user creation + role assignment.

✅ Handle Errors Properly

Always ROLLBACK on errors. Use try-catch blocks in application code to ensure transactions are cleaned up.

✅ Use Appropriate Isolation Level

READ COMMITTED is fine for most applications. Use SERIALIZABLE only when absolutely necessary.

✅ Avoid User Interaction in Transactions

Never wait for user input while a transaction is open. This holds locks and blocks other users.

Key Takeaways

Transactions ensure all-or-nothing - guarantees that either all operations succeed or none are applied to the database.
ACID properties: The golden standard for reliability (Atomicity, Consistency, Isolation, Durability).
BEGIN starts, COMMIT saves, ROLLBACK cancels - the fundamental syntax for managing manual transactions.
Isolation levels trade safety for speed - higher levels prevent more anomalies but can slow down concurrent access.
Locks prevent conflicts - Row-level locks ensure two users don't modify the exact same data at the exact same time.
Deadlocks occur when - two transactions are stuck waiting for each other to release locks. Modern databases detect and resolve these automatically.
Data Integrity is the Goal - mastery of transactions is what separates a casual user from a professional database developer.

Back to "Indexes & Performance"

Database FundamentalsLesson 9

Next: "Advanced Queries"