Master MySQL Transactions: ACID, Locks, and Practical Examples

What Is a Database Transaction and Why It Matters?

A database transaction is a unit of work that groups multiple data operations—Create, Read, Update, or Delete—so they are executed and written as a single whole. During a transaction the data may be inconsistent, but once all operations are committed the database returns to a consistent state. In simple terms, all SQL statements in a transaction either succeed together or fail together, ensuring consistency and providing a way to recover from failures or mistakes.

MySQL Transaction Support Overview

MySQL implements transactions through specific statements: START TRANSACTION / BEGIN: Begin a transaction. COMMIT: Make all changes permanent. ROLLBACK: Undo all changes made in the transaction. SET autocommit = 0 (or OFF) to disable automatic commits; SET autocommit = 1 (or ON) to enable them.

ACID Properties of Transactions

Atomicity

All changes in a transaction are treated as a single indivisible unit—either all happen or none happen.

Consistency

The database must always satisfy its integrity constraints (e.g., primary keys, foreign keys). For example, a column defined to hold only integers will reject non‑integer values, preserving data validity.

Isolation

Isolation determines how visible a transaction’s changes are to other concurrent transactions. MySQL supports four isolation levels:

READ UNCOMMITTED : Allows reading uncommitted changes from other transactions.

READ COMMITTED : Only sees data that other transactions have committed.

REPEATABLE READ : Guarantees that repeated reads within the same transaction return the same data, even if other transactions commit changes.

SERIALIZABLE : The highest level; prevents other transactions from modifying or inserting rows that the current transaction reads.

MySQL’s default isolation level is READ COMMITTED, but it can be changed with SET TRANSACTION ISOLATION LEVEL ….

Durability

Once a transaction is committed, its changes survive crashes or power loss. MySQL ensures durability using Write‑Ahead Logging (WAL), which writes transaction logs to disk before modifying the actual data.

Locks and Concurrency Control in MySQL

MySQL uses several lock types to prevent race conditions:

Shared Locks : Multiple transactions can read the same data, but none can modify it.

Exclusive Locks : Prevent any other transaction from reading or writing the locked data.

Intent Locks : Indicate a transaction’s intention to read or write a portion of data.

Row‑level Locks : Lock only the specific rows a transaction accesses, not the whole table.

For high concurrency, MySQL employs Multi‑Version Concurrency Control (MVCC). Each transaction works with a snapshot of the data, writing changes to a new version while other transactions continue to read the original version. Keeping transactions short helps avoid long‑held locks.

How to Create and Use Transactions in MySQL

Start a transaction with START TRANSACTION, execute your statements, then either COMMIT to persist changes or ROLLBACK to discard them. Example:

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('师兄奇谈', '[email protected]');
UPDATE accounts SET balance = SUM(balance) WHERE name = '师兄奇谈';
COMMIT;

If an error occurs, you can roll back:

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('师兄奇谈', '[email protected]');
UPDATE accounts SET balance = SUM(balance) WHERE name = '师兄奇谈';
ROLLBACK;

Using the InnoDB Storage Engine for Transactions

InnoDB supports full ACID transactions. Ensure a table uses InnoDB: SHOW TABLE STATUS FROM your_database_name; Set InnoDB as the default engine in my.cnf or with:

SET storage_engine = InnoDB;

Handling Errors and Exceptions in Transactions

MySQL provides SIGNAL and RESIGNAL for custom error messages, and you can use a TRY‑CATCH‑style handler to roll back on exceptions:

START TRANSACTION;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
UPDATE accounts SET balance = 5000 WHERE user_id = 1;
UPDATE accounts SET balance = 1000 WHERE user_id = 2;
IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
END IF;
COMMIT;

Using Savepoints in MySQL Transactions

Savepoints let you roll back part of a transaction without aborting the whole thing:

START TRANSACTION;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = 5000 WHERE user_id = 1;
UPDATE accounts SET balance = 1000 WHERE user_id = 2;
IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
    ROLLBACK TO SAVEPOINT my_savepoint;
END IF;
COMMIT;

Conclusion

This article introduced the concept of database transactions, explained the ACID properties, covered MySQL’s transaction commands, isolation levels, lock mechanisms, MVCC, and demonstrated practical examples for starting, committing, rolling back, handling errors, and using savepoints. Future articles will dive deeper into each topic.