Transaction Definition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- Basic transaction start
BEGIN;
 
-- Equivalent forms
START TRANSACTION;
 
-- With explicit isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- With read-only hint
BEGIN TRANSACTION READ ONLY;
 
-- Combined options
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
 
-- Check transaction status
SELECT txid_current();  -- Returns current transaction ID
SELECT pg_is_in_recovery();  -- Check if replica
 
-- Transaction state query
SELECT 
    pid,
    state,
    xact_start,
    query_start,
    NOW() - xact_start AS transaction_age
FROM pg_stat_activity 
WHERE pid = pg_backend_pid();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Basic commit
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
 
-- Equivalent forms
COMMIT WORK;
COMMIT TRANSACTION;
END;  -- PostgreSQL alias for COMMIT
 
-- Commit and start new transaction immediately
COMMIT AND CHAIN;
-- Equivalent to: COMMIT; BEGIN;
-- New transaction inherits same isolation level
 
-- Prepared transaction (two-phase commit)
BEGIN;
    UPDATE distributed_data SET value = 42 WHERE id = 1;
PREPARE TRANSACTION 'tx_for_distributed_commit';
-- Transaction is now in prepared state, can survive crashes
-- Later: COMMIT PREPARED 'tx_for_distributed_commit';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Basic rollback
BEGIN;
    UPDATE accounts SET balance = balance - 1000000 WHERE id = 1;
    -- Oops, that's a million dollars!
ROLLBACK;
-- Phew, nothing happened
 
-- Equivalent forms
ROLLBACK WORK;
ROLLBACK TRANSACTION;
ABORT;  -- PostgreSQL alias for ROLLBACK
 
-- Rollback and start new transaction
ROLLBACK AND CHAIN;
-- Equivalent to: ROLLBACK; BEGIN;
 
-- Rollback to savepoint (partial rollback)
BEGIN;
    UPDATE orders SET status = 'processing' WHERE id = 1;
    SAVEPOINT before_risky;
    DELETE FROM orders WHERE id = 2;  -- Oops, wrong row!
ROLLBACK TO SAVEPOINT before_risky;
    -- orders.id=1 change is preserved
    -- orders.id=2 delete is undone
    UPDATE orders SET status = 'confirmed' WHERE id = 1;
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- PostgreSQL: Error aborts entire transaction
BEGIN;
    INSERT INTO users (id, name) VALUES (1, 'Alice');  -- OK
    INSERT INTO users (id, name) VALUES (1, 'Bob');    -- Error: duplicate key
    
    -- Transaction is now in 'aborted' state
    -- This will fail with: "current transaction is aborted"
    SELECT * FROM users;  
    
ROLLBACK;  -- Only option: rollback
 
-- Workaround: Use savepoints
BEGIN;
    SAVEPOINT before_insert;
    INSERT INTO users (id, name) VALUES (1, 'Bob');  -- Might fail
    -- If it fails, we can recover:
    ROLLBACK TO SAVEPOINT before_insert;
    -- Transaction is still usable
    INSERT INTO users (id, name) VALUES (2, 'Bob');  -- Try with different ID
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- MySQL: DDL causes implicit commit
START TRANSACTION;
    INSERT INTO users (name) VALUES ('Alice');  -- Part of transaction
    
    CREATE TABLE temp_log (id INT);  -- Implicit COMMIT here!
    -- The INSERT is now committed, regardless of what follows
    
    INSERT INTO users (name) VALUES ('Bob');
ROLLBACK;  -- Only rolls back Bob, not Alice!
 
-- PostgreSQL: DDL is transactional
BEGIN;
    INSERT INTO users (name) VALUES ('Alice');
    CREATE TABLE temp_log (id INT);
ROLLBACK;  -- Both INSERT and CREATE are undone!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Two-Phase Commit (2PC) for distributed transactions
-- Requires max_prepared_transactions > 0 in postgresql.conf
 
-- Phase 1: Prepare on Database A
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'transfer_2024_001';
-- Transaction is now 'prepared' - survives crashes
 
-- Meanwhile, on Database B, similar prepare happens...
 
-- Phase 2: Commit both (or rollback both)
-- On Database A:
COMMIT PREPARED 'transfer_2024_001';
-- On Database B:
COMMIT PREPARED 'transfer_2024_001';
 
-- If any prepare fails, rollback all:
ROLLBACK PREPARED 'transfer_2024_001';
 
-- View prepared transactions
SELECT * FROM pg_prepared_xacts;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

// ✅ GOOD: Guaranteed cleanup with try/finally pattern
async function transferWithProperHandling(
    fromId: string,
    toId: string,
    amount: number
) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        
        // Validate first
        const { rows: [sender] } = await client.query(
            'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
            [fromId]
        );
        
        if (sender.balance < amount) {
            throw new InsufficientFundsError(sender.balance, amount);
        }
        
        // Perform transfer
        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );
        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );
        
        await client.query('COMMIT');
        
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;  // Re-throw after cleanup
        
    } finally {
        client.release();  // Always release connection
    }
}
 
// ❌ BAD: No error handling, possible leaked transaction
async function transferDangerous(fromId: string, toId: string, amount: number) {
    await pool.query('BEGIN');
    await pool.query(`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`);
    // If this next line throws, transaction remains open!
    await pool.query(`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${toId}`);
    await pool.query('COMMIT');
}