PostgreSQL Transactions

Overview

A transaction is a sequence of one or more SQL operations that are executed as a single unit of work. Transactions ensure data integrity and consistency in database operations.

ACID Properties

Transactions in PostgreSQL follow the ACID principles:

1. Atomicity - All operations succeed or all fail
2. Consistency - Database remains in valid state
3. Isolation - Concurrent transactions don't interfere
4. Durability - Committed changes persist

Transaction Basics

Starting a Transaction

-- Begin a transaction
BEGIN;

-- Or use START TRANSACTION
START TRANSACTION;

Committing a Transaction

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Save all changes

Rolling Back a Transaction

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;  -- Undo all changes

Complete Transaction Example

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

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

-- Transfer money between accounts
BEGIN;

-- Deduct from Alice
UPDATE accounts 
SET balance = balance - 200 
WHERE name = 'Alice';

-- Add to Bob
UPDATE accounts 
SET balance = balance + 200 
WHERE name = 'Bob';

-- Commit the transaction
COMMIT;

-- Verify the transfer
SELECT * FROM accounts;

Transaction Isolation Levels

PostgreSQL supports four isolation levels to control transaction behavior:

1. Read Uncommitted

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Same as Read Committed in PostgreSQL
COMMIT;

2. Read Committed (Default)

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- See only committed data
-- Each statement sees latest committed data
COMMIT;

3. Repeatable Read

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Sees snapshot from first query
-- Prevents non-repeatable reads
COMMIT;

4. Serializable

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Strictest isolation
-- Prevents phantom reads
COMMIT;

Isolation Level Comparison

-- Session 1: Read Committed
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE name = 'Alice';
-- Sees changes from other committed transactions
COMMIT;

-- Session 2: Repeatable Read
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE name = 'Alice';
-- Always sees same data (snapshot)
SELECT * FROM accounts WHERE name = 'Alice';
COMMIT;

Savepoints

Savepoints allow you to partially rollback a transaction.

BEGIN;

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

SAVEPOINT my_savepoint;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Oops, rollback to savepoint
ROLLBACK TO SAVEPOINT my_savepoint;

-- First update is kept, second is undone
COMMIT;

Multiple Savepoints

BEGIN;

INSERT INTO accounts (name, balance) VALUES ('Charlie', 1000);
SAVEPOINT sp1;

UPDATE accounts SET balance = balance + 500 WHERE name = 'Charlie';
SAVEPOINT sp2;

UPDATE accounts SET balance = balance - 200 WHERE name = 'Charlie';
SAVEPOINT sp3;

-- Rollback to sp2
ROLLBACK TO SAVEPOINT sp2;

-- Release savepoint (remove it)
RELEASE SAVEPOINT sp1;

COMMIT;

Transaction Control

Check Transaction Status

-- Check if in transaction
SELECT current_setting('transaction_isolation');

-- Show transaction state
SHOW transaction_isolation;

Set Default Isolation Level

-- For current session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- In postgresql.conf for all sessions
-- default_transaction_isolation = 'repeatable read'

Common Transaction Patterns

Pattern 1: Safe Money Transfer

CREATE OR REPLACE FUNCTION transfer_money(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
) RETURNS BOOLEAN AS $$
BEGIN
    -- Check sufficient balance
    IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    -- Deduct from source
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = from_account;
    
    -- Add to destination
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE id = to_account;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transaction failed: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT transfer_money(1, 2, 100);

Pattern 2: Batch Insert with Validation

BEGIN;

-- Create temporary staging table
CREATE TEMP TABLE staging_data (
    id INTEGER,
    value TEXT,
    is_valid BOOLEAN DEFAULT FALSE
);

-- Load data
INSERT INTO staging_data (id, value) VALUES
    (1, 'valid data'),
    (2, 'invalid'),
    (3, 'valid data');

-- Validate data
UPDATE staging_data 
SET is_valid = TRUE 
WHERE LENGTH(value) > 5;

-- Insert only valid data
INSERT INTO main_table (id, value)
SELECT id, value 
FROM staging_data 
WHERE is_valid = TRUE;

-- Check if all data is valid
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM staging_data WHERE NOT is_valid) THEN
        RAISE EXCEPTION 'Invalid data found';
    END IF;
END $$;

COMMIT;

Pattern 3: Audit Trail

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    changed_by VARCHAR(100)
);

BEGIN;

-- Update with audit
WITH old_values AS (
    SELECT to_jsonb(accounts.*) AS data
    FROM accounts
    WHERE id = 1
)
UPDATE accounts 
SET balance = balance + 500
WHERE id = 1
RETURNING (SELECT data FROM old_values) AS old_data, 
          to_jsonb(accounts.*) AS new_data;

-- Log the change
INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)
VALUES ('accounts', 'UPDATE', 
    (SELECT to_jsonb(accounts.*) FROM accounts WHERE id = 1),
    (SELECT to_jsonb(accounts.*) FROM accounts WHERE id = 1),
    CURRENT_USER);

COMMIT;

Transaction Errors and Handling

Error Handling in Transactions

DO $$
BEGIN
    BEGIN
        -- Start nested block
        UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
        
        -- This will fail if balance goes negative
        IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
            RAISE EXCEPTION 'Negative balance not allowed';
        END IF;
        
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error occurred: %', SQLERRM;
            -- Transaction is rolled back
    END;
END $$;

Retry Logic

CREATE OR REPLACE FUNCTION retry_transaction(max_attempts INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    attempt INTEGER := 0;
BEGIN
    WHILE attempt < max_attempts LOOP
        BEGIN
            -- Attempt transaction
            UPDATE accounts 
            SET balance = balance - 100 
            WHERE id = 1;
            
            -- If successful, return
            RETURN TRUE;
            
        EXCEPTION
            WHEN serialization_failure THEN
                attempt := attempt + 1;
                IF attempt >= max_attempts THEN
                    RAISE EXCEPTION 'Transaction failed after % attempts', max_attempts;
                END IF;
                -- Wait before retry
                PERFORM pg_sleep(0.1);
        END;
    END LOOP;
    
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

Concurrency Issues

Dirty Read

-- PostgreSQL prevents dirty reads
-- Transaction 1
BEGIN;
UPDATE accounts SET balance = 999 WHERE id = 1;
-- Not committed yet

-- Transaction 2 (in another session)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Sees old value (not 999)
COMMIT;

-- Transaction 1
ROLLBACK;  -- Change is undone

Lost Update

-- Solution: Use FOR UPDATE
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock the row
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Phantom Read

-- Use SERIALIZABLE to prevent
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM accounts WHERE balance > 500;
-- Other transactions cannot insert rows that would change this count
COMMIT;

Best Practices

1. Keep transactions short

   • Minimize time holding locks
   • Reduce chance of conflicts

2. Use appropriate isolation level

   • READ COMMITTED for most cases
   • REPEATABLE READ for consistency
   • SERIALIZABLE for strict requirements

3. Handle errors properly

   • Always use exception handling
   • Implement retry logic for serialization failures

4. Use savepoints for complex logic

   • Partial rollback without losing entire transaction
   • Better error recovery

5. Avoid long-running transactions

   • Can cause blocking
   • Increases deadlock risk
   • Affects vacuum performance

Transaction Performance Tips

-- 1. Batch operations
BEGIN;
INSERT INTO logs (message) VALUES ('msg1'), ('msg2'), ('msg3');
COMMIT;

-- 2. Use COPY for bulk loads
BEGIN;
COPY data FROM '/path/to/file.csv' CSV;
COMMIT;

-- 3. Disable autocommit for batch operations
-- In psql: \set AUTOCOMMIT off

-- 4. Use prepared statements
PREPARE insert_account AS
    INSERT INTO accounts (name, balance) VALUES ($1, $2);

BEGIN;
EXECUTE insert_account('User1', 100);
EXECUTE insert_account('User2', 200);
COMMIT;

DEALLOCATE insert_account;

Monitoring Transactions

-- View active transactions
SELECT 
    pid,
    usename,
    state,
    query,
    xact_start,
    query_start,
    state_change
FROM pg_stat_activity
WHERE state = 'active';

-- Check for long-running transactions
SELECT 
    pid,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

-- Kill a transaction
SELECT pg_terminate_backend(pid);

Summary

PostgreSQL transactions provide:

• ACID guarantees - Data integrity and consistency
• Isolation levels - Control concurrent access
• Savepoints - Partial rollback capability
• Error handling - Robust failure recovery
• Concurrency control - Prevent data conflicts

Key Commands:

• BEGIN / START TRANSACTION - Start transaction
• COMMIT - Save changes
• ROLLBACK - Undo changes
• SAVEPOINT - Create checkpoint
• ROLLBACK TO SAVEPOINT - Partial rollback

Understanding transactions is essential for maintaining data integrity and building reliable database applications.