Understanding MySQL Database Transactions: ACID Properties, Control Statements, and Isolation Levels

Database transactions are a sequence of operations that must be executed completely or not at all, providing the atomic unit of work that ensures data integrity.

The four ACID properties—Atomicity, Consistency, Isolation, and Durability—are illustrated with a classic fund transfer example where account A deducts 1,000 units and account B receives the same amount.

In MySQL, only the InnoDB storage engine (default in MySQL 5.7) supports transactions; other engines do not. The server runs in AUTOCOMMIT mode by default, treating each individual statement as a transaction unless AUTOCOMMIT is disabled:

SHOW VARIABLES LIKE 'AUTOCOMMIT';

To disable autocommit, set the variable to 0:

SET AUTOCOMMIT=0;

When autocommit is off, you must explicitly start, commit, or roll back transactions.

MySQL transaction control statements include:

START TRANSACTION or BEGIN – start a new transaction.

COMMIT – make all changes permanent.

ROLLBACK – undo all changes.

SAVEPOINT name – create a named savepoint.

ROLLBACK TO SAVEPOINT name – revert to a specific savepoint.

RELEASE SAVEPOINT name – delete a savepoint.

SET TRANSACTION – define transaction characteristics such as isolation level.

Example of using a savepoint:

CREATE TABLE t_employee (id int NOT NULL AUTO_INCREMENT, name varchar(255), PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_employee (name) VALUES ('Tom'), ('John'), ('Hans');

START TRANSACTION;

UPDATE t_employee SET name='full stack' WHERE id=1;

SAVEPOINT s1;

UPDATE t_employee SET name='full stack new' WHERE id=1;

ROLLBACK TO s1;

SELECT * FROM t_employee WHERE id=1;

MySQL supports four isolation levels defined by the SQL‑92 standard:

Read Uncommitted – allows dirty reads.

Read Committed – prevents dirty reads but allows non‑repeatable reads.

Repeatable Read – prevents dirty and non‑repeatable reads; uses MVCC to avoid phantom rows.

Serializable – forces transactions to execute sequentially, eliminating all concurrency anomalies but with low performance.

Each level is demonstrated with two concurrent sessions (A and B) operating on a checking table. The examples show how the visibility of updates changes under different isolation settings, highlighting phenomena such as dirty reads, non‑repeatable reads, phantom reads, and the performance impact of serializable isolation.

In summary, mastering MySQL transactions—including ACID concepts, control statements, savepoints, and isolation levels—is essential for reliable application development and interview preparation.