What are database transactions and why do they matter?

A transaction groups multiple SQL statements into a single atomic unit - either all succeed (COMMIT) or all are undone (ROLLBACK), ensuring data consistency.

SQLPostgreSQL

Overview

A transaction ensures that a set of database operations either all succeed together or all fail together. Without transactions, a crash or error in the middle of a multi-step operation can leave your database in a corrupted, inconsistent state. The classic example: transferring money between bank accounts.

Key Points

•ACID: Atomicity, Consistency, Isolation, Durability - the four guarantees of a transaction.
•`BEGIN` (or `START TRANSACTION`) starts a transaction.
•`COMMIT` saves all changes made since `BEGIN`.
•`ROLLBACK` undoes all changes since `BEGIN`.
•`SAVEPOINT name` creates a partial rollback point within a transaction.
•Most ORMs and database drivers wrap operations in transactions automatically.

Examples

Money transfer - the classic transaction example

sql

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- debit Alice
UPDATE accounts SET balance = balance + 500 WHERE id = 2; -- credit Bob

-- If anything goes wrong, ROLLBACK undoes both operations
-- Both succeed → COMMIT persists them together
COMMIT;

Without a transaction, a crash between the two UPDATEs would debit Alice without crediting Bob.

Using ROLLBACK on error

sql

BEGIN;

UPDATE orders SET status = 'processing' WHERE id = 42;
INSERT INTO audit_log (order_id, event) VALUES (42, 'status_changed');

-- Something failed...
ROLLBACK; -- undo both changes, leave the database as it was

Transaction with SAVEPOINT

sql

BEGIN;
INSERT INTO invoices (amount) VALUES (100);

SAVEPOINT after_invoice;

INSERT INTO line_items (invoice_id, product) VALUES (1, 'Widget');

-- Only undo the line item, keep the invoice
ROLLBACK TO after_invoice;

COMMIT; -- commits the invoice, line_item is rolled back

Transactions in application code (Node.js + pg)

javascript

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

Pro Tip

Keep transactions as short as possible. Long-running transactions hold locks, block other queries, and can cause deadlocks. Do all heavy computation before opening the transaction, then open-write-commit in one quick burst.