Database Transactions: ACID Properties, Isolation Levels, and Usage in MySQL

Transactions are a core feature that distinguishes databases from file systems, ensuring consistency and allowing recovery to a specific point in time.

1. Transaction Overview

Only the InnoDB storage engine in MySQL supports transactions, as shown by the SHOW ENGINES command.

1.1 Storage Engine Support

InnoDB is the only engine that provides transactional capabilities.

1.2 Basic Concepts

A transaction is a logical unit of work that moves data from one state to another. It follows the principle of treating all operations as a single work unit, committing all changes permanently with COMMIT or discarding them with ROLLBACK .

-- Example: AA transfers 100 to BB
UPDATE account SET money = money - 100 WHERE name = 'AA';
-- Server crash
UPDATE account SET money = money + 100 WHERE name = 'BB';

1.3 ACID Characteristics

Atomicity : All operations succeed or all are rolled back.

Consistency : Data moves between legal states defined by business rules.

Isolation : Transactions do not interfere with each other.

Durability : Once committed, changes survive crashes, guaranteed by redo and rollback logs.

1.4 Transaction States

Transactions progress through states such as active , partially committed , failed , aborted , and committed . A state diagram illustrates these transitions.

2. Using Transactions

2.1 Explicit Transactions

Start a transaction with START TRANSACTION or BEGIN , optionally adding modifiers like READ ONLY , READ WRITE , or WITH CONSISTENT SNAPSHOT . After DML statements, end with COMMIT or ROLLBACK . Savepoints can be created and released:

START TRANSACTION;
-- DML operations
COMMIT;

SAVEPOINT sp1;
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;

2.2 Implicit Transactions

When autocommit is ON (default), each statement is its own transaction. To group statements, disable autocommit with SET autocommit = OFF or explicitly start a transaction.

2.3 Implicit Commit Situations

DDL statements (e.g., CREATE , ALTER , DROP ), user-management commands, lock statements, LOAD DATA , and replication commands automatically commit the current transaction.

3. Transaction Isolation Levels

MySQL supports four isolation levels, each balancing consistency and concurrency:

READ UNCOMMITTED : Allows dirty reads.

READ COMMITTED : Prevents dirty reads.

REPEATABLE READ (default): Prevents dirty and non‑repeatable reads, but phantom reads may occur.

SERIALIZABLE : Prevents all three anomalies at the cost of performance.

Isolation level can be viewed and set globally or per session:

# View current level
SHOW VARIABLES LIKE 'transaction_isolation';
# Set level for the session
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# Set level globally
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Examples demonstrate how different levels affect phenomena such as dirty reads, non‑repeatable reads, and phantom reads.

4. Common Transaction Classifications

Flat Transactions

Flat Transactions with Savepoints

Chained Transactions

Nested Transactions

Distributed Transactions

The article concludes with a reminder that the choice of isolation level should reflect the consistency requirements of the application.