intermediateDatabase Engineering100% Free

SQL Transactions & ACID Properties

Definition

A SQL transaction is a sequence of one or more SQL statements executed as a single atomic unit, ensuring all changes either fully commit or fully roll back.

Introduction to SQL Transactions & ACID Properties

Transactions are the safety net of relational databases. They ensure that bank transfers don't lose money, e-commerce orders don't lose items, and concurrent users don't corrupt each other's data. ACID guarantees — Atomicity, Consistency, Isolation, Durability — define what makes a transaction reliable.

Syntax

SQL

BEGIN TRANSACTION; -- or BEGIN;

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

COMMIT; -- or ROLLBACK;

Examples

Bank Transfer Transaction

BEGIN;

-- Debit sender
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 'ACC-001';

-- Credit recipient
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 'ACC-002';

-- Both updates succeed or both roll back
COMMIT;

If any statement fails, ROLLBACK reverts all changes. The transfer is atomic — it either fully happens or not at all.

Try in Playground

SAVEPOINT for Partial Rollback

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (42, 150.00);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, qty)
VALUES (LASTVAL(), 99, 2);

-- If item insert fails, roll back only the item part
ROLLBACK TO SAVEPOINT order_created;

COMMIT; -- Order saved, items not

SAVEPOINTs create intermediate checkpoints within a transaction, allowing partial rollbacks without discarding all work.

Try in Playground

Common Mistakes

Long-running transactions — hold locks for too long, blocking other users

Not wrapping related operations in a transaction — leaves data in partial state on failure

Wrong isolation level — READ UNCOMMITTED allows dirty reads; SERIALIZABLE has high contention

Implicit autocommit — in many tools, each statement auto-commits; wrap in BEGIN...COMMIT explicitly

Frequently Asked Questions

What are ACID properties in a database?

ACID stands for: Atomicity (all-or-nothing — a transaction either fully commits or fully rolls back), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions don't interfere with each other), and Durability (committed transactions survive crashes and power failures).

What is the difference between COMMIT and ROLLBACK?

COMMIT permanently saves all changes made in a transaction to the database. ROLLBACK undoes all changes made since the transaction began, restoring the database to its state before the transaction started.

What are database isolation levels?

Isolation levels control how transactions see each other's changes. From weakest to strongest: READ UNCOMMITTED (can see uncommitted data — dirty reads), READ COMMITTED (default in most DBs), REPEATABLE READ, and SERIALIZABLE (highest isolation, lowest concurrency). Higher isolation prevents more anomalies but reduces concurrency.

What is a deadlock in SQL?

A deadlock occurs when two transactions each hold a lock that the other needs, creating a circular wait. Database engines detect deadlocks and automatically kill one transaction (the victim). Prevent deadlocks by always acquiring locks in the same order across all transactions.

Practice This in the SQL Playground

Write real queries, see live results, and master SQL Transactions & ACID Properties hands-on. 100% free.

Open SQL Playground Browse All Lessons