Transactions Deep Dive

A transaction is a group of SQL operations that are treated as a single unit of work. Either ALL of them succeed, or NONE of them do. There’s no in-between.

Think of it like a bank transfer. If we’re moving $500 from account A to account B, we need two operations: subtract from A and add to B. If the system crashes after subtracting but before adding, the money just vanishes. Transactions prevent this by wrapping both operations together.

The Basics: BEGIN, COMMIT, ROLLBACK

-- Start a transaction
BEGIN;

-- Deduct from sender
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Add to receiver
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Everything looks good? Make it permanent
COMMIT;

If something goes wrong at any point, we can undo everything:

BEGIN;

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

-- Oops, the receiver account doesn't exist!
-- Undo everything
ROLLBACK;
-- The sender's balance is restored to what it was before

Auto-Commit Mode

By default, most databases run in auto-commit mode. This means every single SQL statement is automatically wrapped in its own transaction and committed immediately.

-- These two statements are independent transactions
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- ^ auto-committed immediately

UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- ^ auto-committed immediately

-- If the second one fails, the first is already committed!
-- That's why we need explicit transactions for multi-step operations.

Savepoints

Sometimes we want to undo part of a transaction without rolling back the whole thing. That’s what savepoints are for.

Think of it like saving a game. If we mess up, we can reload from the last save point instead of starting over.

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100.00);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 2);
-- Oops, product 42 is out of stock

ROLLBACK TO before_items;
-- The order is still there, but the item insert is undone

-- Try a different product
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 43, 2);

COMMIT;  -- commits the order + the second item

Common Mistakes

1. Forgetting to COMMIT

If we start a transaction and forget to commit, the locks are held until we either commit, rollback, or the connection times out. Other queries waiting for those rows will be blocked.

BEGIN;
UPDATE products SET price = 29.99 WHERE id = 1;
-- Developer walks away for lunch...
-- This row is now locked for everyone else!

-- Always commit or rollback when done
COMMIT;

2. Long-Running Transactions

Keeping a transaction open for a long time is a recipe for trouble:

• Locks are held the entire time, blocking other users
• In PostgreSQL, dead tuples can’t be cleaned up by VACUUM
• Memory usage increases because the database keeps undo information

-- Bad: doing slow processing inside a transaction
BEGIN;
SELECT * FROM orders WHERE status = 'pending';
-- ... application processes orders for 5 minutes ...
UPDATE orders SET status = 'processed' WHERE id IN (...);
COMMIT;

-- Better: read first, process outside the transaction, then update
SELECT * FROM orders WHERE status = 'pending';
-- ... application processes orders ...
BEGIN;
UPDATE orders SET status = 'processed' WHERE id IN (...);
COMMIT;  -- transaction lasts milliseconds, not minutes

3. Nested Transactions

Most databases don’t support true nested transactions. In PostgreSQL, if we call BEGIN inside an existing transaction, it just ignores it (with a warning). Use savepoints instead:

BEGIN;

-- This does NOT create a nested transaction
-- BEGIN;  -- WARNING: there is already a transaction in progress

-- Use savepoints for nested behavior
SAVEPOINT sp1;
-- do stuff
RELEASE SAVEPOINT sp1;  -- like a mini-commit

COMMIT;

Transaction in Application Code

Here’s how transactions typically look in application code:

-- PostgreSQL with a connection pool (pseudocode)
-- const client = await pool.connect();
-- try {
--     await client.query('BEGIN');
--     await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [500, 1]);
--     await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [500, 2]);
--     await client.query('COMMIT');
-- } catch (e) {
--     await client.query('ROLLBACK');
--     throw e;
-- } finally {
--     client.release();
-- }

In simple language, transactions are our safety net for multi-step operations. Wrap related operations in BEGIN/COMMIT, use savepoints for partial rollbacks, and keep transactions as short as possible. A forgotten COMMIT can bring an entire application to its knees.