Transactions

Chapter 35: Transactions Overview

ACID Properties and Transaction Control

---

35.1 Understanding Transactions

A transaction is a sequence of database operations that are treated as a single unit of work.

                    ACID Properties
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      ACID Properties                                 │
    │                                                                      │
    │   Atomicity:                                                         │
    │   ──────────                                                         │
    │   All operations succeed or all fail together                       │
    │   - No partial updates                                              │
    │   - "All or nothing"                                                 │
    │                                                                      │
    │   Consistency:                                                       │
    │   ───────────                                                        │
    │   Database moves from one valid state to another                    │
    │   - Constraints maintained                                           │
    │   - Cascades properly applied                                       │
    │                                                                      │
    │   Isolation:                                                        │
    │   ──────────                                                         │
    │   Concurrent transactions don't interfere                            │
    │   - Each sees consistent view                                        │
    │   - Depends on isolation level                                       │
    │                                                                      │
    │   Durability:                                                        │
    │   ───────────                                                        │
    │   Committed data is永久 stored                                       │
    │   - Survives system crash                                           │
    │   - WAL ensures durability                                           │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

35.2 Basic Transaction Commands

-- Explicit transaction
BEGIN;

INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 500);

COMMIT;  -- Make changes permanent

-- Or rollback
BEGIN;
INSERT INTO accounts (account_id, balance) VALUES (3, 100);
ROLLBACK;  -- Discard changes

-- Using SAVEPOINTs
BEGIN;
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
-- Problem occurred, go back to savepoint
ROLLBACK TO SAVEPOINT sp1;

-- Release savepoint (not needed after rollback)
COMMIT;

---

35.3 Transaction Modes

-- Read-only transaction
BEGIN TRANSACTION READ ONLY;
SELECT * FROM users;  -- Can read
INSERT INTO users VALUES ('test', 'test@test.com');  -- Will fail
COMMIT;

-- Read-write transaction (default)
BEGIN TRANSACTION READ WRITE;

-- Deferrable (waits for lock)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
DEFERRABLE;

-- PostgreSQL extensions
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Do work
COMMIT;

---

35.4 Implicit Transactions (Auto-commit)

-- Default autocommit behavior
-- Each statement is its own transaction

-- Turn off autocommit (psql specific)
\set AUTOCOMMIT off

-- Now need explicit BEGIN/COMMIT
BEGIN;
INSERT INTO test (name) VALUES ('test1');
INSERT INTO test (name) VALUES ('test2');
COMMIT;

-- Or set in session
SET default_transaction_deferrable = ON;

---

35.5 Transaction in Functions

-- Function with explicit transaction control
CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC
) RETURNS BOOLEAN AS $$
BEGIN
    -- Check if transaction already started
    IF current_setting('transaction_deferred') = 'on' THEN
        -- Already in a transaction
    END IF;

    -- Deduct from source
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    -- Add to destination
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- Call with transaction
BEGIN;
SELECT transfer_funds(1, 2, 100);
COMMIT;

---

Summary

Command	Description
BEGIN	Start transaction
COMMIT	Save changes
ROLLBACK	Discard changes
SAVEPOINT	Create restore point
RELEASE SAVEPOINT	Remove savepoint

---

Next: Chapter 36: Isolation Levels