MySQL Transactions and Isolation Levels: A Backend Engineer's Guide

#mysql #database #transactions #backend

Published 2026-07-26 by Shubham Bhati — Backend Engineer (Java 17, Spring Boot, Microservices).

MySQL Transactions and Isolation Levels: A Backend Engineer's Guide

Hey everyone, Shubham Bhati here. Ever stared blankly at logs after a report showed inconsistent data, or worse, found money missing from an account despite your code looking perfectly fine? It's a frustrating spot. Oftentimes, these elusive bugs are not application logic errors but stem from concurrent operations hitting your database in unexpected ways. Understanding MySQL transaction isolation levels isn't just academic; it's critical for building stable, predictable, and resilient backend systems. Let's dive into how to tame these concurrency beasts and ensure your data integrity, even under heavy load.

The Foundation: Transactions and ACID Properties

At its core, a transaction is a single, logical unit of work that either completes entirely or fails completely. Think of a bank transfer: debiting one account and crediting another must happen as one operation. If one part fails, the whole thing should roll back. This concept is underpinned by ACID properties: Atomicity (all or nothing), Consistency (data remains valid), Isolation (concurrent transactions don't interfere), and Durability (once committed, changes are permanent).

In Spring Boot, we manage transactions declaratively using the @Transactional annotation. This powerful annotation wraps your method execution in a database transaction, automatically handling commits and rollbacks based on exceptions. It's convenient, but don't let its simplicity mask the complexities beneath.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class AccountService {

    private final AccountRepository accountRepository;

    public AccountService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    @Transactional
    public void transferMoney(Long fromAccountId, Long toAccountId, double amount) {
        Account fromAccount = accountRepository.findById(fromAccountId)
                                .orElseThrow(() -> new IllegalArgumentException("Sender not found"));
        Account toAccount = accountRepository.findById(toAccountId)
                                .orElseThrow(() -> new IllegalArgumentException("Recipient not found"));

        if (fromAccount.getBalance() < amount) {
            throw new IllegalArgumentException("Insufficient funds");
        }

        fromAccount.setBalance(fromAccount.getBalance() - amount);
        toAccount.setBalance(toAccount.getBalance() + amount);

        accountRepository.save(fromAccount);
        accountRepository.save(toAccount);
        // If an exception occurs here, both saves will be rolled back.
    }
}

Production Note: When a method annotated with @Transactional is called, HikariCP (your connection pool) provides a database connection from its pool. This connection is then exclusively held by the transaction until it commits or rolls back. This means a long-running transaction can tie up a connection, potentially starving other requests and impacting overall application throughput. Keep transactions short and focused.

Demystifying Isolation Levels

Isolation is the "I" in ACID, dictating how operations in one transaction are visible to others. SQL defines four standard isolation levels, each preventing a different set of concurrency anomalies, with increasing strictness:

READ UNCOMMITTED: Least strict. Transactions can see uncommitted changes made by other transactions (Dirty Reads). Rarely used in production due to severe data integrity risks.
READ COMMITTED: Prevents Dirty Reads. Transactions only see changes that have been committed. However, a transaction might read different values for the same row if another transaction commits changes between reads (Non-Repeatable Reads).
REPEATABLE READ: Prevents Dirty Reads and Non-Repeatable Reads. If you read a row multiple times within a transaction, you'll always get the same data. MySQL's default isolation level for InnoDB tables. It still allows for Phantom Reads, where new rows inserted by another committed transaction might appear in subsequent queries within the same transaction.
SERIALIZABLE: Most strict. Prevents Dirty Reads, Non-Repeatable Reads, and Phantom Reads. Transactions execute as if they were run sequentially. This level provides maximum data integrity but comes with significant performance overhead due to extensive locking.

MySQL's default REPEATABLE READ is often a good balance for many applications. It uses MVCC (Multi-Version Concurrency Control) for reads to avoid blocking, but uses gap and next-key locks to prevent phantom reads for specific ranges, sometimes making it behave closer to SERIALIZABLE in certain scenarios.

import org.springframework.transaction.annotation.Isolation;

// ... inside a Spring @Service class
@Transactional(isolation = Isolation.READ_COMMITTED)
public Order processOrder(String orderId) {
    // This transaction will only see committed data.
    // However, if we read customer balance here, then later read it again,
    // and another transaction updates it in between, we might see different balances.
    Order order = orderRepository.findById(orderId)
                        .orElseThrow(() -> new OrderNotFoundException(orderId));
    // Perform processing...
    return orderRepository.save(order);
}

Production Note: Choosing an isolation level is a trade-off between data consistency and concurrency performance. Stricter levels (like SERIALIZABLE) mean more locks, which can lead to higher latency (p99) and increased chances of deadlocks, especially under high concurrent load. Less strict levels increase throughput but risk data anomalies. Measure and test your choices under realistic load.

When to Adjust Isolation Levels (and Why)

While REPEATABLE READ is MySQL's default, there are specific scenarios where you might consider adjusting it. For instance, if you have a dashboard reporting system that needs to display the absolute latest committed data without being concerned about "non-repeatable reads" within a single transaction, READ COMMITTED might offer slightly better concurrency. This is because READ COMMITTED releases read locks earlier, allowing other transactions to write to the same rows sooner.

Consider a system generating daily summary reports. If the report pulls data throughout the day and needs to reflect all committed changes up to the moment of each individual query, even if it means seeing different intermediate totals, READ COMMITTED could be appropriate.

import org.springframework.transaction.annotation.Isolation;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ReportService {

    private final SalesRepository salesRepository;
    private final InventoryRepository inventoryRepository;

    public ReportService(SalesRepository salesRepository, InventoryRepository inventoryRepository) {
        this.salesRepository = salesRepository;
        this.inventoryRepository = inventoryRepository;
    }

    // This method needs to read the latest committed data for summary.
    // We accept potential non-repeatable reads for different sub-queries
    // in favor of higher concurrency for the underlying tables.
    @Transactional(isolation = Isolation.READ_COMMITTED, readOnly = true)
    public DailySummary generateDailySummary() {
        long totalSales = salesRepository.countAllSalesToday();
        long currentInventory = inventoryRepository.countAllItemsInStock();
        // Even if inventory changes between these two queries,
        // we're fine with capturing the latest state at each query point.
        return new DailySummary(totalSales, currentInventory);
    }
}

Using SERIALIZABLE is generally discouraged unless absolute, bulletproof consistency is paramount (e.g., highly sensitive financial ledger updates where even phantom reads are unacceptable) and you can tolerate the significant performance hit. It applies range locks, which can drastically reduce concurrency. Always opt for the least strict isolation level that meets your application's consistency requirements.

Common Pitfalls

Ignoring MySQL's Default: Assuming your database's isolation level is what you configured in your application.properties without verifying. MySQL's default REPEATABLE READ often surprises developers expecting READ COMMITTED (like PostgreSQL's default).
Overusing SERIALIZABLE: While it offers maximum safety, the performance cost is often too high for most web applications. Extensive locking leads to slow response times and increased deadlocks.
Not Testing Concurrency Locally: Many concurrency issues only manifest under heavy load. Test your services with multiple concurrent users to identify potential data anomalies or deadlocks.
Misunderstanding Read Phenomena: Not knowing the difference between Dirty Reads, Non-Repeatable Reads, and Phantom Reads makes it impossible to correctly choose an isolation level. Invest time in understanding these concepts.

Conclusion

Mastering MySQL transactions and their isolation levels is fundamental for any backend engineer working with relational databases. It's about more than just slapping @Transactional on a method; it's about making informed decisions on data consistency versus performance. Understand your data's integrity requirements, choose the appropriate isolation level, and always test your assumptions under load. Your users and your future self debugging production issues will thank you.