SQL Transactions

Overview

A transaction groups one or more statements into a single unit of work that either fully completes or fully fails. Transactions provide the ACID guarantees—atomicity, consistency, isolation, and durability—so partial updates never corrupt your data. You start one with BEGIN, finalize it with COMMIT, or undo it with ROLLBACK.

Syntax / Usage

Wrap statements between BEGIN and COMMIT. If anything goes wrong, ROLLBACK reverts all changes made since BEGIN. Savepoints allow partial rollbacks within a transaction.

BEGIN;

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

COMMIT;   -- or ROLLBACK to undo both updates

-- Savepoint for partial rollback
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, sku) VALUES (999, 'X');
ROLLBACK TO after_order;  -- keeps the order, drops the item
COMMIT;

Examples

A money transfer that must be all-or-nothing:

BEGIN;
UPDATE accounts SET balance = balance - 250 WHERE id = 1;
UPDATE accounts SET balance = balance + 250 WHERE id = 2;
-- If either row is missing or a check fails, issue ROLLBACK
COMMIT;

Set an isolation level to prevent non-repeatable reads within a transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- other sessions cannot change these rows before COMMIT
COMMIT;

Common Mistakes

• Leaving a transaction open, holding locks and blocking other sessions
• Forgetting to COMMIT, so changes silently roll back on disconnect
• Assuming autocommit is off—many clients commit each statement by default
• Choosing too strict an isolation level and causing deadlocks or contention
• Doing slow work (network calls, user input) while a transaction holds locks

See Also

crud sql-views select