MySQL Transaction Basics: ACID, Isolation Levels, and Practical Pitfall‑Avoiding Code

1. What is a database transaction?

Transaction groups SQL statements that succeed or fail as a unit. Example: transfer 100 from A to B requires debit A and credit B; without a transaction, partial success leads to lost money; with a transaction, both succeed or both roll back, guaranteeing consistency.

2. ACID properties

Atomicity

All statements are indivisible; any error or crash triggers a full rollback.

Consistency

Data remains valid before and after; total balance stays unchanged.

Isolation

Concurrent transactions do not interfere; prevents reading uncommitted changes. Identified as the most error‑prone property.

Durability

Committed changes are persisted to disk and survive crashes.

3. Why isolation levels matter

≈90 % of online data anomalies (incorrect balances, duplicate rows, dirty reads) stem from improper isolation level usage. Typical incidents include concurrent transfers causing mismatched balances, order‑status errors, inventory overselling, dirty reads, phantom reads, and long‑running transactions causing deadlocks or timeouts.

4. MySQL InnoDB isolation levels and recommended scenarios

Read Uncommitted

Never use in production – allows dirty reads.

Read Committed (RC)

Suitable for most internet services, high‑concurrency queries, and non‑financial workloads. Prevents dirty reads, allows non‑repeatable reads; high performance.

Repeatable Read (RR)

Default in MySQL; ideal for payment, transfer, order, inventory, and core financial reconciliation. Prevents dirty and non‑repeatable reads; phantom reads may still occur; high safety.

Serializable

Only for rare cases requiring absolute consistency and low frequency. Not suitable for high concurrency because it forces serial execution, causing lock waits and timeouts.

5. Hands‑on reproduction of concurrency problems and solutions

Step 1: Create test table

CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `user_name` varchar(20) NOT NULL COMMENT 'username',
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'account balance',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='transaction test account table';

INSERT INTO account(user_name,balance) VALUES ('Zhang San',1000.00),('Li Si',1000.00);

Step 2: View / modify global isolation level

SHOW VARIABLES LIKE 'transaction_isolation';
SET GLOBAL transaction_isolation = 'READ-COMMITTED';   -- reproduce dirty / non‑repeatable reads
SET GLOBAL transaction_isolation = 'REPEATABLE-READ'; -- default, solves most issues

Step 3: Dirty read

Definition: a transaction reads data modified by another uncommitted transaction. Condition: isolation level below Read Committed. Solution: raise isolation to Read Committed or higher.

Step 4: Non‑repeatable read

Definition: same query within one transaction returns different results because another transaction committed changes. Condition: Read Committed level. Solution: upgrade to Repeatable Read, which provides a consistent snapshot.

Step 5: Phantom read

Definition: a range query returns a different row count after another transaction inserts or deletes rows. RR cannot fully prevent phantom reads, which may cause missed updates or inaccurate statistics.

Enterprise mitigation:

Ordinary business often tolerates phantom reads.

Financial reconciliation or batch updates combine gap locks, pessimistic locks, and unique constraints to eliminate phantom reads.

Step 6: Spring Boot transaction implementation

Production‑grade service:

@Service
public class AccountService {

    @Autowired
    private AccountMapper accountMapper;

    @Transactional(rollbackFor = Exception.class)
    public void transferMoney(String fromUser, String toUser, BigDecimal money) throws InterruptedException {
        accountMapper.subBalance(fromUser, money);
        Thread.sleep(200);
        accountMapper.addBalance(toUser, money);
    }
}

Annotation rollbackFor = Exception.class ensures all exceptions trigger a rollback.

6. Common pitfalls

Including non‑database operations (IO, network calls, third‑party APIs) inside a transaction leads to timeouts and blocking.

Omitting rollbackFor causes exceptions without rollback.

Misusing nested transactions due to unfamiliar propagation rules results in partial rollbacks.

Long‑running transactions hold row locks, causing deadlocks and request timeouts.

Incorrect isolation level selection: Serializable in high‑concurrency scenarios degrades performance; Read Committed in financial scenarios causes data anomalies.

Transactional methods that are not public are ignored by Spring AOP proxies.

7. Core cheat sheet

ACID: Atomicity (all‑or‑nothing), Consistency (data remains valid), Isolation (concurrent transactions don’t interfere), Durability (committed data persists).

Three concurrency problems—dirty read, non‑repeatable read, phantom read—are addressed by specific isolation levels.

Production selection: RC for general high‑performance workloads; RR for core financial consistency.

Code essentials: configure rollbackFor, keep transactions short, avoid non‑DB operations inside transactions.

≈90 % of online data chaos originates from transaction failures, wrong isolation level choices, and long‑running transactions.