SQL Transactions – COMMIT, ROLLBACK, ACID

What Is a Transaction?

Consider a bank transfer: you debit account A and credit account B. If the debit succeeds but the credit fails (due to a crash), money disappears. A transaction wraps both operations so they either both complete or neither does:

SQL

BEGIN;   -- or START TRANSACTION in MySQL

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

COMMIT;  -- make both changes permanent

If anything goes wrong between BEGIN and COMMIT, a ROLLBACK undoes everything:

SQL

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- An error occurs here (e.g., account 2 not found)
ROLLBACK;  -- balance of account 1 is restored, no money lost

ACID Properties

ACID is the set of guarantees that a transactional database provides:

Property	Meaning	Example
Atomicity	All or nothing — every operation in the transaction succeeds or all are rolled back.	Both debit and credit happen, or neither does.
Consistency	The database moves from one valid state to another — constraints are never violated.	Total money in all accounts stays the same after a transfer.
Isolation	Concurrent transactions do not see each other's uncommitted changes.	Another session reading mid-transfer sees either the old or new balances, not a partial state.
Durability	Once committed, data survives crashes — written to persistent storage.	A committed transfer is not lost if the server loses power immediately after.

BEGIN, COMMIT, ROLLBACK

SQL

-- Full transaction workflow with error handling
BEGIN;

-- Multiple DML statements grouped together
INSERT INTO orders (customer_id, total)        VALUES (42, 299.99);
INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 7);
UPDATE inventory SET stock = stock - 1         WHERE product_id = 7;

-- Check the stock is still valid before committing
-- (In application code you would check rows affected here)
COMMIT;

-- To undo everything:
ROLLBACK;

ℹ️

Syntax variation by database

PostgreSQL/SQLite: BEGIN or BEGIN TRANSACTION.
MySQL: START TRANSACTION (BEGIN also works).
SQL Server: BEGIN TRANSACTION.
Oracle: transactions start automatically with the first DML — no explicit BEGIN needed; just COMMIT or ROLLBACK.

SAVEPOINTs

SAVEPOINT lets you create a checkpoint within a transaction. You can roll back to a savepoint without abandoning the entire transaction:

SQL

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (42, 100);
SAVEPOINT after_order;        -- checkpoint created

INSERT INTO order_items (order_id, product_id) VALUES (1, 99);
-- Suppose product 99 is out of stock — roll back only the items insert
ROLLBACK TO SAVEPOINT after_order;
-- The order insert is still alive; only the items insert was undone

INSERT INTO order_items (order_id, product_id) VALUES (1, 15);
COMMIT;  -- commits the order + the fallback item insert

Autocommit

Most databases run in autocommit mode by default: each SQL statement is automatically committed when it succeeds. To use explicit transactions, you must either disable autocommit or use BEGIN explicitly:

SQL

-- MySQL: disable autocommit for the session
SET autocommit = 0;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;   -- required now that autocommit is off

-- MySQL: or just use START TRANSACTION (disables autocommit temporarily)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- PostgreSQL: BEGIN explicitly starts a transaction (autocommit applies to statements outside BEGIN/COMMIT)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Transaction Isolation Levels

Isolation levels control what uncommitted data one transaction can see from another concurrent transaction:

Level	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Not possible	Possible	Possible
REPEATABLE READ	Not possible	Not possible	Possible (MySQL: No)
SERIALIZABLE	Not possible	Not possible	Not possible

SQL

-- Set isolation level (PostgreSQL / MySQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ... your statements
COMMIT;

💡

Default isolation levels

PostgreSQL: READ COMMITTED. MySQL (InnoDB): REPEATABLE READ. SQL Server: READ COMMITTED. Most applications work correctly with READ COMMITTED; SERIALIZABLE provides the strongest guarantees but reduces throughput due to increased locking.

Summary

📋 Summary

A transaction groups SQL statements so they all succeed or all fail together.
ACID: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (survives crash).
BEGIN → statements → COMMIT commits the transaction; ROLLBACK undoes it.
SAVEPOINT creates a partial rollback point inside a transaction.
Most databases run in autocommit mode by default — each statement commits automatically.
Isolation levels trade consistency for concurrency performance; READ COMMITTED is the typical default.

FAQ

What happens if a database crashes inside a transaction? +

The database's write-ahead log (WAL) records all changes before they are applied to disk. On restart, the database replays committed transactions from the WAL and rolls back any transactions that had not reached COMMIT. This ensures Durability (committed data is not lost) and Atomicity (incomplete transactions are cleaned up automatically).

Can DDL statements (CREATE TABLE, ALTER TABLE) be inside a transaction? +

It depends on the database. PostgreSQL supports transactional DDL — you can roll back a CREATE TABLE or ALTER TABLE. MySQL does not support transactional DDL; a DDL statement implicitly commits any open transaction before executing. SQL Server supports most DDL inside transactions. This matters for database migration scripts.

What is a deadlock and how do I prevent it? +

A deadlock occurs when two transactions each hold a lock the other needs: Transaction A holds lock on row 1 and wants row 2; Transaction B holds row 2 and wants row 1. The database detects this and kills one transaction (the victim). Prevention: always acquire locks in the same order across transactions; keep transactions short; use the appropriate isolation level; consider optimistic locking for high-concurrency workloads.