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!
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
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
-- or
START TRANSACTION;  -- Standard SQL, MySQL, PostgreSQL
-- or
BEGIN TRANSACTION;  -- SQL Server

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

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.

LevelDirty ReadNon-Repeatable ReadPhantom Read
READ UNCOMMITTED❌ Possible❌ Possible❌ Possible
READ COMMITTED✓ Prevented❌ Possible❌ Possible
REPEATABLE READ✓ Prevented✓ Prevented❌ Possible
SERIALIZABLE✓ Prevented✓ Prevented✓ Prevented

Read Uncommitted (Lowest Isolation)

Can see uncommitted changes from other transactions (dirty reads).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Transaction 1:          Transaction 2:
BEGIN;                  BEGIN;
UPDATE accounts         
SET balance = 500       
WHERE id = 1;           
                        SELECT balance FROM accounts 
                        WHERE id = 1;
                        Result: 500 (dirty read!)
ROLLBACK;               
                        -- Saw data that was rolled back!

Read Committed (Default in PostgreSQL)

Only see committed changes. No dirty reads.

SET 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.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Guarantees complete isolation
-- May block or abort conflicting transactions

Concurrency Problems

Dirty Read

Reading uncommitted data that might be rolled back.

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! Both wait forever
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
  • Use timeouts: Database detects and breaks deadlocks automatically

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.
Database FundamentalsLesson 9