Transactions

BEGIN / COMMIT / ROLLBACK

BEGIN opens a transaction; COMMIT makes every change inside it permanent and visible to other sessions; ROLLBACK discards every change as if the transaction never ran. Until you commit, your changes live in your session only: invisible to everyone else, undoable in one statement.

Syntax #

sql

BEGIN;                  -- or START TRANSACTION;
  -- one or more DML statements
  UPDATE users SET status = 'archived' WHERE last_seen_at < DATE '2025-01-01';
  DELETE FROM sessions WHERE user_id IN (SELECT id FROM users WHERE status = 'archived');
COMMIT;                 -- make permanent
-- ROLLBACK;            -- ...or undo everything since BEGIN

-- Savepoint: nested rollback boundary.
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT before_credit;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- if the credit fails:
  ROLLBACK TO SAVEPOINT before_credit;
  -- ...handle error, try alternate path...
COMMIT;

Example #

Loading SQL editor...

Isolation levels #

Isolation controls what other concurrent transactions see while yours is in flight, and how they interfere with you. Four standard levels, weakest to strongest:

READ UNCOMMITTED: see other transactions' uncommitted changes ("dirty reads"). Rare in practice; some engines don't even implement it.
READ COMMITTED (default in Postgres, Oracle, SQL Server): only see committed changes. Same query inside one transaction can return different results if another transaction commits in between ("non-repeatable read").
REPEATABLE READ (default in MySQL InnoDB): snapshot at the moment of the first read; same query returns the same rows. New rows from other transactions can still appear ("phantom read") on some engines.
SERIALIZABLE: behaves as if transactions ran one at a time. Highest correctness, lowest concurrency, occasional retries when the engine detects a serialization conflict.

Set per session or per transaction:

sql

-- Per transaction.
BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- ...
COMMIT;

-- Per session (Postgres / SQL Server).
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Dialect notes #

Auto-commit by default: every engine starts each statement in implicit auto-commit mode. BEGIN is what disables it for the next block of statements.
Postgres: BEGIN is the standard. Default isolation READ COMMITTED. Both DDL and DML are transactional.
MySQL InnoDB: START TRANSACTION or BEGIN. Default isolation REPEATABLE READ. DDL implicitly commits: CREATE TABLE inside a transaction will auto-commit any pending DML before it. Don't mix DDL into a DML transaction on MySQL.
SQL Server: BEGIN TRANSACTION (or BEGIN TRAN). Default READ COMMITTED. Full DDL-in-transaction support. Watch for XACT_ABORT settings.
Oracle: BEGIN is implicit, so every statement opens a transaction; COMMIT or ROLLBACK closes it. DDL implicitly commits.
SQLite: BEGIN / COMMIT / ROLLBACK. Single-writer at a time. SAVEPOINT supported.
Warehouses (Snowflake, BigQuery, Redshift): transactions exist but the workload patterns differ — large MERGE and INSERT OVERWRITE operations are usually atomic on their own. BEGIN/COMMIT blocks are rarer than in OLTP databases.

Tip

The "BEGIN / verify / COMMIT" pattern is the single highest-leverage habit when running ad-hoc DML against production. BEGIN; UPDATE ... WHERE ...; SELECT * FROM ... WHERE ...; — eyeball the result. If it's right, COMMIT;. If it's wrong, ROLLBACK; and start over. Costs you 30 seconds per change. Saves you the day a typo would have rewritten 100,000 rows. (Note: some psql / DBeaver settings auto-commit each statement — confirm yours don't, or you'll be in for a surprise.)