MySQL: A Detailed Explanation of MySQL Transactions and…
Last updated: 2026-08-26
Transactions ensure the atomicity of data operations—they either succeed entirely or fail entirely.
This lesson explains the principles and usage of transactions.
graph TB
A[START TRANSACTION] --> B[ExecuteSQLOperation]
B --> C{Was it successful?？}
C -->|All successful| D[COMMIT]
C -->|Partial failure| E[ROLLBACK]
B --> F[SAVEPOINT Save Point]
F --> G[ROLLBACK TO sp]
G --> B
D --> H[Data Persistence]
E --> I[Data Restored to Before Transaction]
1. What You'll Learn
- The ACID Properties of Transactions
- START TRANSACTION / COMMIT / ROLLBACK
- SAVEPOINT Save Point
- Auto-commit (AUTOCOMMIT)
- Transaction isolation levels
2. A True Story About a Bank Transfer
(1) Pain Point: Transfer Fails Midway
A transfers 100 yuan to B:
SQL
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Success
-- At this point, the system crashes
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Not executed
Result: A was short 100, and B didn't receive anything—the money vanished into thin air.
(2) Solutions to Transactions
SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Submit only if both are successful
3. ACID Properties
| Feature | Description | Implementation |
|---|---|---|
| Atomicity | A transaction is an indivisible unit of work | undo log |
| Consistency | Data remains consistent before and after a transaction | Application layer + constraints |
| Isolation | Concurrent transactions do not interfere with one another | Locks + MVCC |
| Durability | Data is permanently stored after submission | redo log |
4. Transaction Operations
▶ Example: Basic Transactions
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
-- Start Transaction
START TRANSACTION;
-- Perform an operation
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Commit Transaction
COMMIT;
-- Or roll back the transaction
ROLLBACK;
Output:
TEXT
📖 Display only
Output displayed
▶ Example: SAVEPOINT
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 1 row affected
Query OK, 0 rows affected
Query OK, 1 row affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
START TRANSACTION;
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 1);
SAVEPOINT sp2;
-- Roll back to sp1（Retain the first item INSERT）
ROLLBACK TO sp1;
-- Submit（Keep only the first one INSERT）
COMMIT;
Output:
TEXT
📖 Display only
Output displayed
5. Auto-Submit
▶ Example: AUTOCOMMIT
SQL
-- View Auto-Submit Status
SELECT @@autocommit;
-- Turn off Auto-Submit
SET autocommit = 0;
-- Enable Auto-Submit
SET autocommit = 1;
Output:
TEXT
📖 Display only
Output displayed
| autocommit | Description |
|---|---|
| 1 (default) | Auto-commit each statement |
| 0 | Requires a manual COMMIT |
6. Transaction Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ | ✅ | ✅ | Highest |
| READ COMMITTED | ❌ | ✅ | ✅ | High |
| REPEATABLE READ (Default) | ❌ | ❌ | ✅ | Medium |
| SERIALIZABLE | ❌ | ❌ | ❌ | Lowest |
▶ Example: Setting the isolation level
SQL
-- View the current isolation level
SELECT @@transaction_isolation;
-- Set the isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Output:
TEXT
📖 Display only
Output displayed
7. Demonstration of Concurrency Issues
(1) Dirty Read
SQL
-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE id = 1;
-- Not committed
-- Transaction B (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1; -- Read 1000 (Dirty Data)
-- Transaction A
ROLLBACK; -- Rollback
-- Transaction B: data read is now invalid
(2) Non-repeatable reads
SQL
-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Read 500
-- Transaction B
UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;
-- Transaction A
SELECT balance FROM accounts WHERE id = 1; -- Read 1000 (Inconsistent)
COMMIT;
❓ FAQ
Q What is the default isolation level in MySQL?
A REPEATABLE READ, implemented using MVCC + gap locks.
Q What happens if a transaction isn't committed?
A The lock remains held, and other transactions may time out while waiting.
Q Can DDL be rolled back?
A No, DDL is automatically committed and cannot be rolled back.
Q What does "implicit commit" mean?
A When DDL statements (CREATE/ALTER/DROP) are executed, the current transaction is automatically committed; this is known as an implicit commit.
Q What are the risks associated with large transactions?
A Long lock durations, undo log bloat, and increased master-slave latency. It is recommended to break them down into smaller transactions.
📖 Summary
- Transactions ensure the atomicity of operations; they either succeed entirely or fail entirely.
- ACID: Atomicity, Consistency, Isolation, Durability
- COMMIT: Commit, ROLLBACK: Rollback, SAVEPOINT: Savepoint
- Isolation Levels control concurrency issues: dirty reads, non-repeatable reads, and phantom reads
- MySQL default is REPEATABLE READ
📝 Exercises
-
Basic Problem (Difficulty: ⭐): Write a transaction that transfers 100 from Account A to Account B.
-
Advanced Problem (Difficulty ⭐⭐): Use SAVEPOINT to implement a partial rollback.
-
Challenge Question (Difficulty: ⭐⭐⭐): Demonstrate the non-repeatable read problem and solve it using the REPEATABLE READ isolation level.
