Implicit vs Explicit Transactions

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

-- Check if inside a transaction
SELECT pg_current_xact_id_if_assigned();
-- Returns NULL if no transaction, or XID if in transaction
 
-- Alternative: check transaction state
SELECT 
    current_setting('transaction_isolation') as isolation_level,
    current_setting('transaction_read_only') as read_only,
    pg_is_in_recovery() as is_replica;
 
-- In application code, check if autocommit is off
-- (PostgreSQL doesn't have a single autocommit variable)
-- You need to track this at the driver/connection level
 
-- psql meta-command to check autocommit
\echo :AUTOCOMMIT
 
-- Check active transaction details
SELECT 
    pid,
    usename,
    state,
    xact_start,
    NOW() - xact_start as age,
    query
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

-- PostgreSQL doesn't have server-level autocommit
-- It's controlled at the connection/client level
 
-- In psql client:
\set AUTOCOMMIT off
-- Now each statement doesn't auto-commit
 
-- In application connection strings:
-- Most drivers support an 'autocommit' parameter
 
-- Set default transaction isolation (server level)
-- In postgresql.conf:
-- default_transaction_isolation = 'read committed'
 
-- Set for current session:
SET default_transaction_isolation = 'serializable';
 
-- Set for specific transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- operations
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

// ❌ BUG 1: Assuming atomic when autocommit is ON
async function transferFundsBuggy(from: number, to: number, amount: number) {
    const db = getConnection();  // Autocommit is ON by default!
    
    // These are TWO SEPARATE transactions!
    await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
    // If crash/error here, money is gone but not received!
    await db.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
}
 
// ✅ FIX: Explicit transaction
async function transferFundsFixed(from: number, to: number, amount: number) {
    const db = getConnection();
    await db.query('BEGIN');
    try {
        await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
        await db.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
        await db.query('COMMIT');
    } catch (e) {
        await db.query('ROLLBACK');
        throw e;
    }
}
 
// ❌ BUG 2: Oracle developer porting to PostgreSQL
// Oracle code (works because autocommit is OFF by default):
/*
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF error THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
*/
// Same logic in PostgreSQL with autocommit ON:
// First update commits immediately! ROLLBACK does nothing useful.
 
// ❌ BUG 3: Connection pool state pollution
async function getConnectionBuggy(): Promise<Connection> {
    const conn = await pool.getConnection();
    // Previous user might have: SET autocommit = 0;
    // We don't know the connection state!
    return conn;
}
 
// ✅ FIX: Reset connection state
async function getConnectionFixed(): Promise<Connection> {
    const conn = await pool.getConnection();
    await conn.query('SET autocommit = 1');  // Reset to known state
    return conn;
}

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
50
51
52
53
54
55
56
57
58
59
60
61

// Pattern 1: Transaction wrapper function
async function withTransaction<T>(
    db: Database,
    fn: (tx: Transaction) => Promise<T>
): Promise<T> {
    await db.query('BEGIN');
    try {
        const result = await fn(db as Transaction);
        await db.query('COMMIT');
        return result;
    } catch (error) {
        await db.query('ROLLBACK');
        throw error;
    }
}
 
// Usage - atomicity is guaranteed
await withTransaction(db, async (tx) => {
    await tx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, from]);
    await tx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, to]);
});
 
// Pattern 2: Connection pool with state reset
class SafeConnectionPool {
    async getConnection(): Promise<Connection> {
        const conn = await this.pool.getConnection();
        
        // Reset to known state
        await conn.query(`
            SET autocommit = 1;
            SET transaction_isolation = 'read committed';
            RESET ALL;  -- PostgreSQL: reset all session variables
        `);
        
        return conn;
    }
}
 
// Pattern 3: Transaction-aware repository
class OrderRepository {
    constructor(private db: Database) {}
    
    // Every public method that modifies data uses transaction
    async createOrder(order: Order): Promise<Order> {
        return withTransaction(this.db, async (tx) => {
            const created = await tx.query(
                'INSERT INTO orders (customer_id, total) VALUES ($1, $2) RETURNING *',
                [order.customerId, order.total]
            );
            
            for (const item of order.items) {
                await tx.query(
                    'INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)',
                    [created.id, item.productId, item.quantity]
                );
            }
            
            return created;
        });
    }
}