PostgreSQL Transactions

ACID Properties

-- Complete or rollback

-- Maintain constraints

-- No interference

-- Permanent storage

🔹 Basic Transaction Control

Transactions start with BEGIN and end with either COMMIT to save changes or ROLLBACK to undo them. This control ensures that related operations either all succeed or all fail together.

-- Create sample tables
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    balance DECIMAL(10,2)
);

INSERT INTO accounts (name, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 500.00);

-- Successful transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
    UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

-- Check results
SELECT * FROM accounts;

id | name  | balance
---+-------+---------
 1 | Alice | 900.00
 2 | Bob   | 600.00

🔹 Rolling Back Transactions

ROLLBACK undoes all changes made within a transaction, returning the database to its state before BEGIN. This is crucial for handling errors and maintaining data integrity when operations fail.

-- Transaction with rollback
BEGIN;
    UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
    UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';
    -- Oops, made a mistake!
ROLLBACK;

-- Check results (no changes)
SELECT * FROM accounts;

id | name  | balance
---+-------+---------
 1 | Alice | 900.00
 2 | Bob   | 600.00

-- Balances unchanged because of ROLLBACK

🔹 Savepoints

Savepoints allow partial rollbacks within a transaction. You can set multiple savepoints and roll back to any of them without losing all transaction work, providing fine-grained control over changes.

-- Transaction with savepoints
BEGIN;
    UPDATE accounts SET balance = balance - 50 WHERE name = 'Alice';
    SAVEPOINT sp1;
    
    UPDATE accounts SET balance = balance + 50 WHERE name = 'Bob';
    SAVEPOINT sp2;
    
    UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
    -- Rollback to sp2 (undo last update only)
    ROLLBACK TO SAVEPOINT sp2;
    
COMMIT;

-- Check results
SELECT * FROM accounts;

id | name  | balance
---+-------+---------
 1 | Alice | 850.00  -- First update kept
 2 | Bob   | 650.00  -- Second update kept

-- Third update was rolled back

🔹 Transaction Isolation Levels

Isolation levels control how transactions interact with each other. PostgreSQL supports four levels that balance between data consistency and concurrent access performance, preventing various concurrency issues.

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    -- Your queries here
COMMIT;

-- Available isolation levels:
-- READ UNCOMMITTED (treated as READ COMMITTED in PostgreSQL)
-- READ COMMITTED (default)
-- REPEATABLE READ
-- SERIALIZABLE

-- Example: Repeatable Read
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT * FROM accounts WHERE name = 'Alice';
    -- Other transactions can't modify this row until we commit
    UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';
COMMIT;

READ COMMITTED    - See committed changes from other transactions
REPEATABLE READ   - Consistent snapshot throughout transaction
SERIALIZABLE      - Full isolation, as if transactions run serially

🔹 Handling Deadlocks

Deadlocks occur when transactions wait for each other's locks. PostgreSQL automatically detects deadlocks and aborts one transaction. Your application should catch these errors and retry the transaction.

-- Deadlock scenario (for illustration)
-- Transaction 1:
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Wait...
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2 (running simultaneously):
BEGIN;
    UPDATE accounts SET balance = balance - 50 WHERE id = 2;
    -- Wait...
    UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- PostgreSQL will detect deadlock and abort one transaction
-- Error: deadlock detected

1. Access tables in same order
2. Keep transactions short
3. Use appropriate isolation levels
4. Implement retry logic in application

🔹 Transaction Best Practices

Following transaction best practices ensures optimal database performance and data integrity. Keep transactions focused, handle errors properly, and choose appropriate isolation levels for your use case.

-- Good transaction pattern
BEGIN;
    -- Check if operation is valid
    SELECT balance INTO current_balance 
    FROM accounts 
    WHERE name = 'Alice';
    
    IF current_balance >= 100 THEN
        UPDATE accounts 
        SET balance = balance - 100 
        WHERE name = 'Alice';
        
        UPDATE accounts 
        SET balance = balance + 100 
        WHERE name = 'Bob';
        
        COMMIT;
    ELSE
        ROLLBACK;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
END;

🔹 Transaction Guidelines

Effective transaction management is crucial for database reliability and performance. These guidelines help you design transactions that maintain data integrity while maximizing concurrent access and system throughput.