Transactions and ACID

What is a Transaction?

A transaction is a logical unit of work that contains one or more SQL operations. All operations in a transaction are treated as a single unit — either ALL succeed or ALL fail. This ensures data consistency even in the event of system failures.

Real-World Analogy

Think of a bank transfer:

1. Debit $100 from Account A
2. Credit $100 to Account B

If the system crashes after step 1 but before step 2, money would vanish. A transaction ensures both operations complete together or neither does.

Without vs With Transactions

-- WITHOUT transaction (dangerous!)
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- System crashes here!
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
-- Account A lost $100, Account B never received it

-- WITH transaction (safe)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;  -- Both updates are permanent
-- If crash occurs before COMMIT, both updates are rolled back

Transaction Boundaries

BEGIN TRANSACTION
  ├── SQL Statement 1 (INSERT)
  ├── SQL Statement 2 (UPDATE)
  ├── SQL Statement 3 (DELETE)
  └── COMMIT (or ROLLBACK)

Transactions can span multiple statements and even multiple tables. The key is that all changes within the transaction boundary are atomic.

ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability — the four guarantees that transactions provide.

Atomicity

All operations in a transaction succeed, or none do. There is no partial completion.

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (101, 250.00);  -- Step 1
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);  -- Step 2
UPDATE inventory SET stock = stock - 2 WHERE product_id = 5;  -- Step 3
COMMIT;  -- All 3 succeed together
-- If step 2 fails, steps 1 and 3 are undone

Consistency

A transaction brings the database from one valid state to another. All constraints, rules, and cascades must be satisfied.

-- Constraint: foreign key, CHECK, NOT NULL
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (999, -50);
-- FAILS: total must be positive (CHECK constraint)
-- Transaction is rolled back, data remains consistent

Isolation

Concurrent transactions execute as if they were running sequentially. One transaction's intermediate state is not visible to others.

-- Transaction 1: Read account balance
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';  -- $1000
-- ... time passes ...
SELECT balance FROM accounts WHERE id = 'A';  -- Should still see $1000
COMMIT;

-- Transaction 2: Update balance (concurrent)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;

-- With proper isolation, Transaction 1 sees consistent $1000
-- until it commits

Durability

Once committed, changes survive system failures (crash, power loss).

COMMIT
  ├── Write-ahead log (WAL) flushed to disk
  ├── Changes are permanent
  └── Survives crash recovery

The database uses write-ahead logging (WAL) to ensure durability. Changes are first written to a log on disk, then applied to the actual data files.

Transaction Control Statements

SQL provides three main statements to control transactions:

BEGIN TRANSACTION

Starts a new transaction. Subsequent statements are part of this transaction.

-- Standard SQL
BEGIN TRANSACTION;
-- or simply:
BEGIN;

-- SQL Server
BEGIN TRANSACTION;
-- or:
BEGIN TRAN;

-- Now all statements are in the transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
INSERT INTO transactions (account_id, amount, type) VALUES ('A', -100, 'debit');

COMMIT

Permanently saves all changes made in the current transaction.

BEGIN;
INSERT INTO employees (name, dept_id) VALUES ('Alice', 5);
UPDATE departments SET headcount = headcount + 1 WHERE id = 5;
COMMIT;  -- Changes are now permanent

-- After COMMIT, other sessions can see the changes

ROLLBACK

Undoes all changes made in the current transaction.

BEGIN;
DELETE FROM orders WHERE customer_id = 101;
-- Oops! Wrong customer!
ROLLBACK;  -- All deletions are undone
-- orders table is unchanged

Error Handling with Transactions

-- PostgreSQL: Using exception handling
BEGIN;

DO $$
BEGIN
    INSERT INTO orders (customer_id, total) VALUES (101, 250.00);
    INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 5, 2);
    UPDATE inventory SET stock = stock - 2 WHERE product_id = 5;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Error: %', SQLERRM;
    ROLLBACK;
    RETURN;
END $$;

COMMIT;

-- MySQL: Check error after each statement
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (101, 250.00);
IF @@error != 0 THEN ROLLBACK; END IF;
COMMIT;

Transaction States

Active → Partially Committed → Committed
  ↓
Failed → Aborted (Rolled Back)

A transaction is active until COMMIT or ROLLBACK is issued. If an error occurs, it transitions to Failed and must be rolled back.

Savepoints: Partial Rollback

A savepoint creates a named point within a transaction that you can roll back to, without undoing the entire transaction. This is useful for complex operations where you might want to undo some changes but keep others.

Creating and Using Savepoints

BEGIN;

-- First batch of operations
INSERT INTO orders (customer_id, total) VALUES (101, 250.00);
SAVEPOINT after_order_created;

-- Second batch (might fail)
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
UPDATE inventory SET stock = stock - 2 WHERE product_id = 5;

-- If inventory update fails, roll back to savepoint:
ROLLBACK TO SAVEPOINT after_order_created;
-- Order still exists, but order_items and inventory are unchanged

-- Can continue with different approach
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 3, 1);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 3;

COMMIT;  -- All remaining changes are saved

Savepoint Syntax by Database

-- PostgreSQL / MySQL / Standard SQL
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT savepoint_name;  -- Remove savepoint (keep changes)

-- SQL Server
SAVE TRANSACTION savepoint_name;
ROLLBACK TRANSACTION savepoint_name;

-- Oracle
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

Practical Example: Batch Processing

BEGIN;

-- Process 1000 records, skip errors
FOR i IN 1..1000 LOOP
    SAVEPOINT before_record;
    
    BEGIN
        INSERT INTO processed_data (record_id, result)
        SELECT id, complex_calculation(data) FROM raw_data WHERE id = i;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT before_record;
        -- Log error and continue
        INSERT INTO error_log (record_id, error_msg) VALUES (i, SQLERRM);
    END;
END LOOP;

COMMIT;  -- All successful records and error logs are saved

Savepoints give you fine-grained control over transaction rollback without losing all progress.

Auto-commit Mode

Most database clients operate in auto-commit mode by default, where each SQL statement is automatically committed as a separate transaction. You must explicitly start a transaction to group multiple statements.

Auto-commit vs Manual Transactions

-- Auto-commit mode (default in most tools):
INSERT INTO users (name) VALUES ('Alice');  -- Committed immediately
INSERT INTO users (name) VALUES ('Bob');    -- Committed immediately
-- Each statement is its own transaction
-- If Bob's insert fails, Alice's is already committed

-- Manual transaction:
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');
COMMIT;  -- Both are committed together
-- If Bob's insert fails, Alice's is also rolled back

Disabling Auto-commit

-- MySQL
SET autocommit = 0;
-- Now you must explicitly COMMIT or ROLLBACK
INSERT INTO users (name) VALUES ('Alice');
COMMIT;

-- PostgreSQL (psql)
\\set AUTOCOMMIT off
-- Or use BEGIN explicitly

-- JDBC (Java)
connection.setAutoCommit(false);

-- Python (psycopg2)
cur.execute("BEGIN")
# or: conn.autocommit = False

-- SQL Server
-- Auto-commit is always on; use BEGIN TRAN for transactions

When to Use Each Mode

Mode	Use Case
Auto-commit	Single statements, read queries, quick inserts
Manual transaction	Multi-statement operations, data integrity critical

Best Practice: Always Use Explicit Transactions

-- Even for simple operations, use transactions:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

-- Benefits:
-- 1. Atomicity guarantee
-- 2. Can ROLLBACK if needed
-- 3. Clear intent to other developers
-- 4. Consistent behavior across databases

Connection Pool Considerations

When using connection pools, be aware that:

• Connections returned to the pool should not have open transactions
• Always COMMIT or ROLLBACK before returning a connection
• Some pools auto-rollback abandoned connections