MySQL transactions package multiple DML statements into a reliable execution unit. They primarily solve concurrent update conflicts, data inconsistency, and rollback handling after failures. This article focuses on ACID, commit behavior, and isolation levels. Keywords: MySQL transactions, ACID, serialization.
Technical Specification Snapshot
| Parameter | Description |
|---|---|
| Topic | MySQL transaction mechanisms |
| Language | SQL / Bash |
| Storage Engine | InnoDB |
| Transaction Protocol Semantics | ACID |
| Typical Capabilities | Commit, Rollback, Savepoint, Isolation Level |
| Source Platform Info | CSDN article, 520 views |
| Related Tags | mysql, database |
| Core Dependencies | MySQL Server, InnoDB engine |
Transactions are the foundation of MySQL data reliability
A transaction is essentially a group of related DML operations. Either all operations succeed, or all of them fail. The goal is not merely to make SQL run, but to make business outcomes trustworthy.
Typical scenarios include ticket booking, fund transfers, and inventory deduction. If multiple clients modify the same row at the same time without transaction controls, issues such as overselling, dirty reads, and lost updates can occur.
A minimal transaction model illustrates the problem clearly
START TRANSACTION; -- Start the transaction
UPDATE account SET balance = balance - 100 WHERE id = 1; -- Deduct funds
UPDATE account SET balance = balance + 100 WHERE id = 2; -- Credit funds
COMMIT; -- Commit only after both steps succeedThis code treats a transfer as an indivisible unit, preventing a situation where funds are deducted without being credited.
The four ACID properties define transaction boundaries and guarantees
Atomicity means a transaction is indivisible, and any intermediate failure must trigger a full rollback. Consistency means the data must satisfy constraints and integrity rules before and after the transaction. Isolation means concurrent transactions should interfere with each other as little as possible. Durability means committed results must persist over time.
Many production issues are not caused by incorrect SQL, but by an unclear understanding of ACID boundaries. For example, splitting a multi-step business workflow into multiple independent autocommit statements is effectively giving up atomicity.
Use a command to confirm whether the engine supports transactions
SHOW ENGINES; -- View the storage engines supported by the current MySQL instanceThis command helps verify whether InnoDB is the default engine and whether the Transactions column is set to YES.
AI Visual Insight: This screenshot shows the result set of SHOW ENGINES in MySQL. Key fields include Engine, Support, Transactions, XA, and Savepoints. It directly reveals whether InnoDB is the default engine and whether transaction support, distributed transaction semantics, and savepoint capabilities are available. It is the first piece of concrete evidence for determining whether the database provides complete transactional semantics.
InnoDB is the primary carrier of MySQL transaction capabilities
Not all MySQL storage engines support transactions. InnoDB supports transactions, row-level locks, savepoints, and more mature concurrency control. MyISAM does not support transactions, so it cannot provide rollback or isolation guarantees.
This is why, in production environments, tables that store money, orders, or inventory usually prioritize InnoDB over legacy engines that optimize mainly for simple read performance.
You must clearly distinguish autocommit from explicit transactions
SHOW VARIABLES LIKE 'autocommit'; -- Check the autocommit status
SET AUTOCOMMIT = 0; -- Disable autocommit
SET AUTOCOMMIT = 1; -- Enable autocommitThese commands help confirm whether the current session uses a one-statement-one-commit execution model.
An explicit begin binds multiple statements into a single commit unit
Even when autocommit=ON, once you execute BEGIN or START TRANSACTION, subsequent statements enter the current transaction context. You must persist them with COMMIT or undo them with ROLLBACK.
This means autocommit is the default behavior, while explicit transactions are a higher-priority session-level control. Developers should not confuse BEGIN with autocommit behavior.
Savepoints support partial rollback inside a transaction
BEGIN; -- Start the transaction
SAVEPOINT s1; -- Create a savepoint
INSERT INTO account VALUES (1, '张三', 100.00); -- Insert the first record
SAVEPOINT s2; -- Create a second savepoint
INSERT INTO account VALUES (2, '李四', 10000.00); -- Insert the second record
ROLLBACK TO s2; -- Roll back to s2 and undo the second insert
COMMIT; -- Commit the remaining changesSavepoints let you undo specific steps in a long-running transaction instead of discarding all completed work.
Whether an unexpected disconnect loses data depends on whether the transaction has been committed
If a transaction has not yet executed COMMIT, MySQL rolls back uncommitted changes when the client exits unexpectedly. If it has already been committed, the result is durable and is not affected by a client crash.
This directly explains why seeing data does not necessarily mean the data has been durably committed. Under READ UNCOMMITTED, other sessions may even observe temporary results that have not yet been committed.
AI Visual Insight: This screenshot corresponds to the query result for the autocommit variable. It visually shows whether the current session is operating in autocommit mode. Its technical value lies in helping identify the boundary between “a single SQL statement has been persisted” and “the session is still inside an uncommitted transaction,” which is critical when troubleshooting rollback behavior and session-level commit anomalies.
Isolation levels determine how visible concurrent transactions are to one another
Transaction isolation is not a case of higher always being better. It is a tradeoff between consistency and throughput. The four common MySQL isolation levels are, in order: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
These levels mainly address three classic problems: dirty reads, non-repeatable reads, and phantom reads. Stronger isolation enforces stricter visibility rules, but usually increases lock waits and concurrency costs.
Set and inspect global or session isolation levels
SELECT @@global.transaction_isolation; -- View the global isolation level
SELECT @@session.transaction_isolation; -- View the current session isolation level
SET GLOBAL transaction_isolation = 'REPEATABLE-READ'; -- Set the global default level
SET SESSION transaction_isolation = 'READ-COMMITTED'; -- Set the current session levelThe global setting affects new connections, while the session setting affects only the current connection and takes precedence.
The four isolation levels provide different consistency guarantees
Read Uncommitted allows a transaction to read data that another transaction has not yet committed, which leads to dirty reads. It is used almost exclusively for teaching and experimentation.
Read Committed allows reads of committed data only, which prevents dirty reads. However, the same query can return different results within the same transaction, which leads to non-repeatable reads.
Repeatable Read is the default level in MySQL. It ensures that repeated reads of the same row within a transaction return the same result, and it combines MVCC with Next-Key Locks to suppress most phantom read issues.
Serializable uses stricter locking semantics to make concurrent transactions behave almost like serial execution. It offers the strongest correctness guarantees, but the worst throughput, and it is more likely to cause waits and timeouts.
Use a short script to quickly understand isolation-level differences
-- Session A
BEGIN; -- Start the transaction
UPDATE account SET balance = 333.00 WHERE id = 1; -- Modify data but do not commit yet
-- Session B
SELECT * FROM account WHERE id = 1; -- Different isolation levels produce different visible resultsThis concurrent script is the smallest practical experiment template for observing dirty reads, committed visibility, and repeatable-read snapshot behavior.
AI Visual Insight: This diagram shows the lock contention flow under the Serializable isolation level through interactions among Client A, Client B, and the DBMS. It highlights how reads acquire locks, how updates enter a wait queue, and how locks are released after the transaction completes. It is a key visual model for understanding that Serializable does not mean code runs sequentially by itself; instead, locks enforce ordered access.
Repeatable Read is MySQL’s default because it balances correctness and performance
In most OLTP systems, Repeatable Read is a practical compromise between performance and consistency. It provides a stable read view within the transaction through consistent reads while avoiding the broad blocking that Serializable often introduces.
However, the default isolation level does not mean unconditional safety. If your workload includes hotspot updates, range queries, or gap lock contention, you still need to address those risks with index design, short transaction duration, and idempotent business logic.
A practical checklist for implementing transactions in application code
-- 1. Keep transactions as short as possible
-- 2. Lock critical resources first, then perform updates
-- 3. Roll back immediately on error
-- 4. Commit as soon as the work succeeds
BEGIN;
-- Place core business SQL here
COMMIT;The core goal of this checklist is to reduce lock hold time and lower the probability of deadlocks, timeouts, and concurrency jitter.
FAQ
Q1: Why did I run an INSERT, but the data disappeared after I disconnected?
Because the INSERT may still have been inside an uncommitted transaction. If you executed BEGIN first, the change would not be persisted automatically even when autocommit=ON, and an unexpected disconnect would trigger a rollback.
Q2: Why is MySQL’s default isolation level not Read Committed?
Because InnoDB chooses Repeatable Read by default so that consistent-read scenarios get a more stable query view, while MVCC and locking mechanisms reduce concurrency anomalies.
Q3: Is Serializable the safest choice for production environments?
From a consistency perspective, yes. From a performance perspective, usually no. Serializable significantly increases the risk of lock waits and timeouts, so it is worth considering only in extreme strong-consistency scenarios.
Core Summary: This article systematically reconstructs the core concepts of MySQL transactions, covering ACID, commit and rollback, autocommit behavior, savepoints, the four isolation levels, and typical concurrency issues, helping developers build a complete understanding of transactions from semantics to practical implementation.