Farhad Rahimi Klie

Posted on Nov 13 • Edited on Dec 21

SQL Transactions — The Complete Guide (Internals, ACID, Isolation, Locks, Logs, and Real-World Code)

#sql #database #transactions #programming

Transactions are the foundation of data integrity in SQL databases.
If you work with banking systems, payments, e-commerce, social networks, or any serious backend, you are already relying on transactions—even if you don’t fully understand them yet.

This article explains everything about SQL transactions, from high-level concepts to internal database mechanisms, with clear SQL code examples.

1. What Is a Transaction?

A transaction is a logical unit of work that groups one or more SQL statements into a single, atomic operation.

Simple definition:

A transaction ensures that either all operations succeed or none of them do.

Example:

BEGIN;

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

COMMIT;

If any statement fails → nothing is applied.

2. Why Transactions Exist

Without transactions, databases would suffer from:

Partial updates
Corrupted data
Inconsistent state
Lost money
Race conditions

Example without transaction (dangerous):

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- crash happens here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Money disappears.

3. ACID Properties (Core Theory)

Every SQL transaction follows ACID.

3.1 Atomicity

All or nothing.

Either:

All statements execute successfully or
Database rolls back everything

Example:

BEGIN;
INSERT INTO orders VALUES (1, 'phone');
INSERT INTO payments VALUES (1, 500);
ROLLBACK;

Nothing is stored.

3.2 Consistency

The database always moves from one valid state to another valid state.

Rules include:

Constraints
Foreign keys
Triggers
Data types

Example:

INSERT INTO users (id, email) VALUES (1, NULL);
-- fails if email is NOT NULL

Transaction is rejected.

3.3 Isolation

Transactions do not see each other’s intermediate states.

Multiple users can work concurrently without corrupting data.

Isolation is controlled by isolation levels (explained later).

3.4 Durability

Once committed, data survives crashes, power failures, and restarts.

Achieved via:

Write-Ahead Logging (WAL)
Redo logs
Disk flush guarantees

4. Transaction Lifecycle (Internals)

Internally, a transaction goes through these phases:

BEGIN
Execution
Validation
Commit or Rollback
Cleanup

Internal flow:

Client → SQL Engine → Transaction Manager
        ↓
      Lock Manager
        ↓
      Buffer Pool
        ↓
      WAL / Redo Log

5. Transaction Commands in SQL

5.1 BEGIN / START TRANSACTION

BEGIN;
-- or
START TRANSACTION;

Starts a new transaction.

5.2 COMMIT

COMMIT;

Writes changes permanently
Flushes logs to disk

5.3 ROLLBACK

ROLLBACK;

Undoes all changes
Releases locks

5.4 SAVEPOINT (Partial Rollback)

BEGIN;

INSERT INTO users VALUES (1, 'Ali');
SAVEPOINT s1;

INSERT INTO users VALUES (2, NULL); -- error

ROLLBACK TO s1;
COMMIT;

User 1 is saved; user 2 is not.

6. Auto-Commit Mode

Most SQL databases use auto-commit by default.

INSERT INTO users VALUES (1, 'Sara');

Each statement = one transaction.

Disable auto-commit:

SET autocommit = 0;

7. Isolation Levels (Very Important)

SQL defines four isolation levels.

7.1 READ UNCOMMITTED

Can see dirty data
Rarely used

Problem: Dirty Read

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

7.2 READ COMMITTED (Default in many DBs)

Sees only committed data
Prevents dirty reads

Problem: Non-Repeatable Read

7.3 REPEATABLE READ

Same query always returns same result
Prevents non-repeatable reads

Problem: Phantom Reads

(MySQL InnoDB solves this with MVCC)

7.4 SERIALIZABLE (Strongest)

Transactions behave as if executed one by one
Slowest but safest

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

8. Concurrency Problems (With Examples)

Dirty Read

Reading uncommitted data.

Non-Repeatable Read

SELECT balance FROM accounts WHERE id = 1;
-- another transaction updates it
SELECT balance FROM accounts WHERE id = 1;

Different results.

Phantom Read

SELECT * FROM orders WHERE amount > 100;
-- another transaction inserts a new row
SELECT * FROM orders WHERE amount > 100;

9. Locks (Internal Mechanism)

Transactions use locks to protect data.

9.1 Types of Locks

Shared (S) – Read
Exclusive (X) – Write
Intent Locks
Row-level locks
Table-level locks

Example:

SELECT * FROM users WHERE id = 1 FOR UPDATE;

Locks the row.

10. MVCC (Multi-Version Concurrency Control)

Modern databases (PostgreSQL, MySQL InnoDB) use MVCC.

How MVCC works:

Each row has versions
Transactions see a snapshot
Readers don’t block writers

Internal fields:

xmin (created by)
xmax (deleted by)

11. Write-Ahead Logging (WAL)

Transactions do not write directly to disk.

Rule:

Log first, data later

Steps:

Write changes to WAL
Commit log to disk
Apply data pages asynchronously

This ensures durability.

12. Rollback Internals

Rollback uses:

Undo logs
Before-images of rows

Rollback is logical, not physical.

13. Deadlocks

A deadlock occurs when two transactions wait on each other.

Example:

T1 locks A → waits for B
T2 locks B → waits for A

Database:

Detects deadlock
Kills one transaction

14. Nested Transactions (Truth)

SQL does not truly support nested transactions.

What exists:

Savepoints (pseudo-nested)

SAVEPOINT s1;
ROLLBACK TO s1;

15. Distributed Transactions (Brief)

Used across multiple databases.

Techniques:

Two-Phase Commit (2PC)
XA Transactions

Very expensive and complex.

16. Transactions in Real Applications

Banking

Transfers
Payments
Ledgers

E-commerce

Orders
Inventory updates
Payments

Social Media

Likes
Follows
Counters

17. Best Practices

Keep transactions short
Avoid user input inside transactions
Always handle rollback
Choose correct isolation level
Never assume auto-commit behavior

18. Full Real-World Example

BEGIN;

INSERT INTO orders (id, user_id, total) VALUES (1, 10, 500);
INSERT INTO order_items VALUES (1, 'Laptop', 500);
UPDATE inventory SET stock = stock - 1 WHERE product = 'Laptop';

COMMIT;