Master MySQL Transactions: ACID, Isolation Levels, and Practical Commands

Environment

MySQL 8.0.30

1. Transaction Basics

1.1 Basic Concept

A transaction is an atomic unit of work that can be committed or rolled back, consisting of one or more operations.

Transaction processing principle: all operations must be executed as a single work unit; on success, all changes are committed, otherwise the whole transaction is rolled back.

1.2 ACID Properties

1.2.1 Atomicity

Transactions are indivisible; they either fully commit or fully roll back.

1.2.2 Consistency

Transactions move data from one valid state to another, preserving business rules (e.g., a transfer must debit one account and credit another).

1.2.3 Isolation

Concurrent transactions do not interfere with each other; each transaction sees a consistent view of the data.

1.2.4 Durability

Once committed, changes survive server crashes and can be recovered on restart.

1.3 MySQL Transaction Support

Only the InnoDB storage engine supports transactions.

2. Using Transactions

2.1 Explicit Transactions

Start a transaction with START TRANSACTION or BEGIN , then execute DML statements, and finish with COMMIT or ROLLBACK .

<code>START TRANSACTION;</code>

<code>BEGIN;</code>

<code>COMMIT;</code>

<code>ROLLBACK;</code>

Modifiers:

READ ONLY – transaction can only read data.

READ WRITE – transaction can read and write data.

WITH CONSISTENT SNAPSHOT – obtains a consistent snapshot immediately (only REPEATABLE READ respects this).

2.2 Implicit Transactions

Controlled by the autocommit system variable (default ON). Disable with:

<code>SET AUTOCOMMIT=OFF;</code>

2.3 Transaction Examples

<code>CREATE DATABASE pack;</code>
<code>USE pack;</code>
<code>CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(32));</code>

Commit example:

<code>BEGIN;
INSERT INTO test VALUES (1, 'zs');
INSERT INTO test VALUES (2, 'ls');
COMMIT;
SELECT * FROM test;</code>

Rollback example:

<code>BEGIN;
INSERT INTO test VALUES (3, 'ww');
INSERT INTO test VALUES (4, 'zl');
ROLLBACK;
SELECT * FROM test;</code>

2.4 Savepoints

MySQL supports SAVEPOINT , ROLLBACK TO SAVEPOINT , and RELEASE SAVEPOINT to roll back to a specific point.

<code>BEGIN;
INSERT INTO test VALUES (3, 'zl');
SAVEPOINT p1;
INSERT INTO test VALUES (4, 'ww');
ROLLBACK TO p1;
COMMIT;
SELECT * FROM test;</code>

3. Transaction Isolation Levels

3.1 Setup

<code>CREATE TABLE account (id INT PRIMARY KEY, name VARCHAR(32), balance INT);
INSERT INTO account VALUES (1, 'zs', 1000);
SELECT * FROM account;</code>

3.2 Concurrency Issues

Dirty Write, Dirty Read, Non‑repeatable Read, and Phantom Read are problems that arise when multiple transactions modify the same data concurrently.

3.3 Isolation Levels

MySQL provides four isolation levels:

READ UNCOMMITTED : Allows dirty reads; cannot prevent any concurrency anomalies.

READ COMMITTED : Prevents dirty reads but allows non‑repeatable reads and phantom reads.

REPEATABLE READ : Prevents dirty and non‑repeatable reads; phantom reads may still occur. This is MySQL's default.

SERIALIZABLE : Executes transactions sequentially, preventing all three anomalies.

3.4 Demonstrating Isolation Levels

Set isolation level:

<code>SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;</code>

Or:

<code>SET SESSION TRANSACTION_ISOLATION='REPEATABLE-READ';</code>

Check current level:

<code>SELECT @@TRANSACTION_ISOLATION;</code>

Examples for each level (commands and screenshots omitted for brevity).

Performance impact varies with isolation level (see diagrams).

End of tutorial.