What is a MySQL Transaction?¶

Imagine you go to a bank to transfer money to a friend: first, your account is debited, then your friend’s account is credited. These two steps must both succeed; otherwise, the money would either disappear or appear out of nowhere. MySQL Transactions are analogous to this scenario: they are a set of SQL operations that either all execute successfully (committed) or all fail (rolled back), with no “half-done” intermediate state.

The Four Core Properties of Transactions (ACID)¶

Transactions are reliable because they satisfy the ACID properties, each addressing a distinct concern:

1. Atomicity¶

“Atomic” means “indivisible.” All operations in a transaction must either complete entirely or none at all.
Example: When transferring money, the “debit” and “credit” operations must both succeed or fail together. If debiting succeeds but crediting fails, the system automatically rolls back, restoring the account balance to its original state (e.g., before the transfer).

2. Consistency¶

Before and after a transaction, data must adhere to business rules. For example, “total amount remains unchanged”: if you transfer 100 yuan from A to B, A’s balance decreases by 100 and B’s increases by 100, so the sum of A+B remains the same as before the transfer.
Key Point: Consistency is the ultimate goal of transactions, guaranteed by atomicity and other properties.

3. Isolation¶

When multiple transactions execute concurrently, they do not interfere with each other. For example, if you and a friend both transfer to the same account, your operations do not affect each other, and neither of you sees “incomplete” intermediate states.
Note: Isolation addresses “concurrency issues,” and different isolation levels affect data visibility.

4. Durability¶

Once a transaction is committed, data is permanently saved, even if the system crashes. For example, after a successful transfer, A and B’s balances remain unchanged even after the database restarts.

Why Are Transactions Needed? Typical Use Cases¶

Transactions are ubiquitous in real-world business to ensure data “accuracy” and “integrity”:

1. Bank Transfers¶

• Scenario: Transfer 1000 yuan from Account A to Account B.
• Problem: If only A is debited without crediting B, A loses money; if only B is credited without debiting A, B gains money unfairly.
• Solution: Wrap UPDATE A and UPDATE B in a transaction. Commit on success, roll back on failure.

2. E-commerce Order Processing¶

• Scenario: When a user places an order, “create order” and “deduct inventory” must both happen.
• Problem: If only the order is created without deducting inventory, overselling occurs (e.g., 10 items sold to 100 users, showing 10 in stock but actual sales are 100).
• Solution: A transaction ensures “create order + deduct inventory” either both succeed or both fail.

3. Payment Systems¶

• Scenario: After a user pays successfully, update order status, deduct user balance, and record transaction logs.
• Problem: If one step fails (e.g., payment succeeds but order status isn’t updated), users might be charged multiple times.
• Solution: A transaction ensures all related operations are “all or nothing.”

How to Use Transactions in MySQL?¶

Only the InnoDB engine supports transactions; engines like MyISAM do not. Here are the basic steps:

1. Explicitly Start a Transaction¶

By default, MySQL may have “autocommit” enabled (each SQL is a standalone transaction). Explicitly start a transaction:

START TRANSACTION;  -- or BEGIN;

2. Execute SQL Operations¶

Run SQL statements that need atomicity, e.g., a transfer:

-- Assume Account A (ID=1) and B (ID=2), transfer 100 yuan
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

3. Commit or Roll Back¶

• Commit (COMMIT): Confirm all operations are successful and make changes permanent.
• Rollback (ROLLBACK): Undo all changes if an error occurs (e.g., insufficient balance).

Example:

START TRANSACTION;
-- Execute operations...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Check for errors (e.g., balance check)
IF operations_succeeded THEN
  COMMIT;  -- Permanently save changes
ELSE
  ROLLBACK; -- Undo all changes
END IF;

Transaction Isolation Levels and Common Issues¶

When multiple transactions run concurrently, issues like dirty reads, non-repeatable reads, and phantom reads may occur. Isolation levels address these:

1. Common Concurrency Issues¶

• Dirty Read: A transaction reads uncommitted data from another transaction (e.g., Transaction A hasn’t committed a debit, but Transaction B sees the deduction).
• Non-Repeatable Read: The same transaction reads the same data multiple times and gets different results (e.g., Transaction A first reads 1000, then Transaction B debits 100, so A reads 900 again).
• Phantom Read: The same transaction queries multiple times and gets different result sets (e.g., Transaction A first sees 10 orders, then Transaction B adds 1, so A sees 11 orders).

2. MySQL Transaction Isolation Levels¶

InnoDB supports 4 isolation levels; the default is REPEATABLE READ. Check the current level with:

SELECT @@tx_isolation;

• READ UNCOMMITTED: Lowest level; prone to dirty reads, non-repeatable reads, and phantom reads.
• READ COMMITTED: Avoids dirty reads but may have non-repeatable/phantom reads (set with SET TRANSACTION ISOLATION LEVEL READ COMMITTED;).
• REPEATABLE READ: MySQL’s default; uses MVCC to prevent non-repeatable/phantom reads (partial phantom reads may occur in certain versions).
• SERIALIZABLE: Highest level; transactions run sequentially, avoiding concurrency issues but with lowest performance.

Important Considerations¶

1. Avoid Long Transactions: Long transactions consume database resources, lock tables, and reduce concurrency. Split operations or shorten transaction time.
2. Beware of Autocommit: By default, MySQL enables autocommit=1 (each SQL is a transaction). Use START TRANSACTION to control scope explicitly.
3. Choose Isolation Levels Wisely: Balance consistency and performance. Use READ COMMITTED for simple queries and REPEATABLE READ for critical operations (e.g., payments).

Summary¶

Transactions are MySQL’s core mechanism for ensuring data consistency, using ACID properties to guarantee “all or nothing” operations. Understanding transactions, their use cases, and isolation levels helps write reliable SQL. In practice, selecting the right isolation level for business needs is critical to avoiding concurrency issues and ensuring system stability.