BEGIN, COMMIT, ROLLBACK

~10 min · transactions, control

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The three control statements

BEGIN (or START TRANSACTION) opens a transaction. COMMIT finalises it — every change becomes visible to other sessions and is durable on disk. ROLLBACK throws everything away. These are the only three transaction control statements you'll write 99% of the time.

Application-side patterns

Every database driver wraps these. In Python with psycopg: conn.commit() / conn.rollback(). In SQLAlchemy: a session commit() or context manager. In Node with pg: client.query('COMMIT') / client.query('ROLLBACK'). The semantics are identical; the syntax wraps it.

Always handle the failure case

The typical bug: BEGIN, run statements, COMMIT — but if any statement raised an exception, the transaction is left open and idle. The next statement on that connection errors with "current transaction is aborted." Always roll back on exception.

Code

Python with psycopg — explicit transaction·python

import psycopg
with psycopg.connect("postgresql://...") as conn:
    with conn.transaction():
        # Everything inside this block is one transaction.
        conn.execute("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
        conn.execute("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
    # Commits on context exit; rolls back on exception.

Node with pg — manual try/catch/finally·javascript

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

External links

psycopg Transactions

Exercise

Write a small script (in any language) that opens a transaction, runs two updates, and ROLLBACKs. Verify nothing was changed. Then run the same script with COMMIT and verify both updates are visible.