SQL Transactions and ACID Properties Explained

Every time money moves between two bank accounts, two things must happen together: one account is debited and the other is credited. If only one of those steps succeeds, the bank's books no longer balance. SQL transactions exist to prevent exactly this kind of half-finished update. This guide explains BEGIN, COMMIT, and ROLLBACK, walks through a bank-transfer example that goes wrong without transactions, and breaks down the four ACID properties that make transactions reliable.

What is a SQL Transaction?

A transaction groups multiple SQL statements into a single unit of work. The database guarantees that either all statements in the group succeed, or none of them do. You control the boundaries of a transaction with three commands:

• BEGIN (or START TRANSACTION) — marks the start of a transaction
• COMMIT — permanently saves every change made since BEGIN
• ROLLBACK — undoes every change made since BEGIN, as if it never happened

-- MySQL / PostgreSQL
START TRANSACTION;   -- or: BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

The Bank Transfer Example: What Goes Wrong Without Transactions

Imagine transferring $500 from Account 1 to Account 2 using two separate, unprotected statements:

-- No transaction wrapping these two statements
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- App crashes, network drops, or server restarts right here

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

If the application crashes between the two statements, Account 1 has already lost $500 but Account 2 never received it. The $500 has vanished from the system entirely — a bug that is very hard to detect and even harder to explain to an auditor. Wrapping the same two statements in a transaction eliminates this risk:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- If anything failed above, run ROLLBACK instead of COMMIT:
-- ROLLBACK;

COMMIT;

If the crash happens before COMMIT runs, the entire transaction is automatically rolled back when the connection drops. Account 1 keeps its original balance, and no money is lost. A common real-world safeguard is to check business rules before committing:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500
WHERE account_id = 1 AND balance >= 500;

-- Application checks ROW_COUNT() / affected rows here.
-- If 0 rows were updated (insufficient funds), roll back:
-- ROLLBACK;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

The Four ACID Properties

ACID is the set of guarantees that make transactions trustworthy. Every production-grade relational database — MySQL (InnoDB), PostgreSQL, SQL Server, Oracle — implements all four:

1. Atomicity — the transaction is all-or-nothing. The debit and credit in the bank example either both happen or neither happens. There is no partial state.
2. Consistency — a transaction moves the database from one valid state to another valid state, respecting constraints, foreign keys, and triggers. Total money in the system before and after the transfer remains the same.
3. Isolation — concurrent transactions do not see each other's uncommitted changes. If two transfers happen at the same time, each one behaves as if it were running alone, preventing race conditions like double-spending.
4. Durability — once COMMIT succeeds, the change is permanent, even if the server crashes or loses power one millisecond later. The database writes changes to a durable transaction log before confirming the commit.

Isolation Levels and Why They Matter

Isolation is configurable because stricter isolation costs performance. Most databases support four standard levels, from loosest to strictest:

• READ UNCOMMITTED — can see other transactions' uncommitted changes ("dirty reads")
• READ COMMITTED — only sees committed data; PostgreSQL's default
• REPEATABLE READ — same query returns the same rows throughout the transaction; MySQL InnoDB's default
• SERIALIZABLE — behaves as if transactions ran one at a time, strongest but slowest

-- MySQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Using SAVEPOINT for Partial Rollbacks

Within a long transaction, SAVEPOINT lets you roll back part of the work without discarding everything:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

SAVEPOINT before_credit;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Something about the credit step failed validation:
ROLLBACK TO SAVEPOINT before_credit;

-- Retry the credit differently, then:
COMMIT;

Frequently Asked Questions