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

#mysql #database #transactions #backend

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

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

Ever found your Java Spring Boot application showing stale data to users, or worse, processing a payment twice under heavy load? These baffling production issues often trace back to a fundamental database concept: transactions. Specifically, how MySQL manages mysql transaction isolation levels determines data visibility and consistency across concurrent operations. As backend engineers, understanding these mechanisms isn't optional; it's critical for building resilient and correct systems. Let's unwrap the intricacies of ACID properties and isolation levels, ensuring your applications behave predictably, even when hammered by thousands of requests per second.

The Foundation: ACID Properties and Spring's @Transactional

At the heart of reliable database operations are ACID properties: Atomicity, Consistency, Isolation, and Durability. Atomicity ensures all or nothing. Consistency keeps your data valid. Durability guarantees committed changes persist. Isolation, our main focus, dictates how concurrent transactions interact and observe each other's changes. Spring Boot simplifies transaction management with its @Transactional annotation. By default, Spring will use your database's default isolation level, which for MySQL (InnoDB) is REPEATABLE READ.

Consider a service method updating a user's balance:

@Service
public class AccountService {

    @Autowired
    private AccountRepository accountRepository;

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

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

        fromAccount.setBalance(fromAccount.getBalance().subtract(amount));
        toAccount.setBalance(toAccount.getBalance().add(amount));

        accountRepository.save(fromAccount);
        accountRepository.save(toAccount);
    }
}

This @Transactional method wraps the entire fund transfer operation in a single database transaction. During a peak traffic event, if you're hitting this endpoint repeatedly, Spring's @Transactional leverages a connection from your HikariCP pool. The isolation level defines what other concurrent transactions see while this transfer is in progress. Misunderstanding this can lead to surprising data anomalies and debugging nightmares, impacting your service's latency p99 and data integrity.

Diving into Isolation: Read Committed

READ COMMITTED is a widely used isolation level that offers a good balance between consistency and concurrency. It prevents "dirty reads," meaning a transaction will never see uncommitted changes made by another transaction. Only data that has been successfully committed to the database is visible. However, READ COMMITTED still allows for "non-repeatable reads." This means if a transaction reads the same row multiple times, it might see different values if another transaction commits changes to that row in between its reads.

For scenarios where you need to see the absolute latest committed data, even if it changes mid-transaction, READ COMMITTED can be a suitable choice. For example, a dashboard showing real-time statistics might benefit from this, prioritizing freshness over perfectly consistent reads within a single transaction.

You can explicitly set READ_COMMITTED in Spring Boot like this:

@Service
public class ReportService {

    @Autowired
    private AuditLogRepository auditLogRepository;

    @Transactional(isolation = Isolation.READ_COMMITTED, readOnly = true)
    public List<AuditLogEntry> getRecentAuditLogs() {
        // First read: retrieves logs
        List<AuditLogEntry> logs1 = auditLogRepository.findTop100ByOrderByTimestampDesc();

        // Simulate other work or a second read
        // If another transaction commits new logs here, logs2 would reflect them.
        List<AuditLogEntry> logs2 = auditLogRepository.findTop100ByOrderByTimestampDesc();

        return logs1; // or merge logs1 and logs2 based on logic
    }
}

While READ COMMITTED generally provides better concurrency than REPEATABLE READ due to fewer read locks, it introduces the non-repeatable read challenge. Depending on your data access patterns and how critical it is for a transaction to see a consistent snapshot, you'll need to weigh this trade-off. Choosing this level can reduce the memory footprint associated with keeping older versions of rows for consistent reads, improving overall performance.

The Default and Beyond: Repeatable Read and Serializable

MySQL's default isolation level, REPEATABLE READ, offers stronger consistency. It guarantees that if a transaction reads a row multiple times, it will always see the same data, even if other transactions modify and commit changes to that row. This prevents "non-repeatable reads." However, REPEATABLE READ does not prevent "phantom reads" by default for range queries. A phantom read occurs when a transaction re-executes a query that returns a set of rows and finds that the set of rows has changed (e.g., new rows inserted or existing rows deleted by another transaction).

To fully prevent phantom reads and ensure strong consistency, especially in critical write operations like managing inventory or unique identifiers, you often need explicit locking using SELECT ... FOR UPDATE. This statement places an exclusive lock on the selected rows, preventing other transactions from modifying or locking them until your transaction commits.

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "SELECT p FROM Product p WHERE p.id = :productId FOR UPDATE")
    Optional<Product> findByIdForUpdate(Long productId);
}

@Service
public class InventoryService {

    @Autowired
    private ProductRepository productRepository;

    @Transactional(isolation = Isolation.REPEATABLE_READ) // MySQL default
    public void decreaseStock(Long productId, int quantity) {
        Product product = productRepository.findByIdForUpdate(productId)
                                .orElseThrow(() -> new RuntimeException("Product not found"));

        if (product.getStock() < quantity) {
            throw new RuntimeException("Not enough stock");
        }
        product.setStock(product.getStock() - quantity);
        productRepository.save(product); // This will update the locked row
    }
}

For absolute data consistency, SERIALIZABLE isolation ensures transactions execute as if they were running one after another, eliminating dirty reads, non-repeatable reads, and phantom reads. It achieves this by aggressively locking rows, ranges, or even entire tables. While offering maximum data integrity, SERIALIZABLE can severely impact concurrency and significantly increase the likelihood of deadlocks and transaction timeouts, leading to higher latency and lower throughput. Use it sparingly and only when the strictest data integrity is a non-negotiable requirement.

Common Pitfalls

Ignoring MySQL's Default: Relying on REPEATABLE READ for all scenarios without understanding its implications, especially regarding phantom reads and the need for SELECT ... FOR UPDATE in specific cases.
Long-Running Transactions: Keeping transactions open for extended periods, particularly with higher isolation levels, can lead to severe lock contention, reduced concurrency, and increased resource consumption from your HikariCP connection pool.
Overuse of SERIALIZABLE: Applying SERIALIZABLE as a blanket solution will tank your application's performance, leading to bottlenecks and degraded user experience. Reserve it for extremely critical, low-contention operations.
Not Handling Exceptions: Failing to roll back transactions on exceptions or connection drops can leave your database in an inconsistent state or hold locks indefinitely. Always ensure robust exception handling within your @Transactional methods.

Conclusion

Understanding MySQL mysql transaction isolation levels is crucial for any backend engineer building reliable Spring Boot applications. While Spring's @Transactional simplifies usage, knowing the nuances of READ COMMITTED, REPEATABLE READ, and SERIALIZABLE empowers you to make informed decisions. Choose the right isolation level for each use case, prioritize SELECT ... FOR UPDATE for critical consistency, and avoid common pitfalls to deliver applications that are both performant and correct.