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

#mysql #database #transactions #backend

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

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

Ever stared at a bug report claiming inconsistent data, a phantom debit or a lost update, and wondered "how did that even happen?" The culprit often hides in plain sight: your database's handling of concurrent operations. For backend engineers working with Java and Spring Boot, understanding mysql transaction isolation levels is not just theoretical knowledge; it's critical for building reliable, high-performance systems. Let's explore how these levels dictate what your transactions see, ensuring your applications behave as expected under heavy load.

The Foundation: ACID Properties and READ COMMITTED

At the core of reliable database operations are ACID properties: Atomicity, Consistency, Isolation, and Durability. Isolation, our focus, determines how operations appear to interact with each other. MySQL's default isolation level, REPEATABLE READ, offers strong guarantees but can introduce unexpected behavior. This is why many Spring Boot applications opt for READ COMMITTED.

With READ COMMITTED, a transaction only sees data that was committed before the statement started. This prevents "dirty reads" (reading uncommitted changes). However, it allows "non-repeatable reads," meaning if you read the same row twice within a single transaction, another committed transaction might have changed it between your reads, giving you different results. This level generally offers better concurrency because it holds fewer locks for shorter durations. It's a common sweet spot for many microservices where the business logic can tolerate non-repeatable reads and benefits from reduced mysql locking. For HikariCP, lower contention often means faster connection acquisition and less time spent blocking, potentially improving p99 latency for write operations.

@Service
public class AccountService {

    @Autowired
    private AccountRepository accountRepository;

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void transferFundsReadCommitted(Long fromAccountId, Long toAccountId, BigDecimal amount) {
        Account fromAccount = accountRepository.findById(fromAccountId)
                                            .orElseThrow(() -> new RuntimeException("From account not found"));
        // First read: fromAccount balance is X

        // Imagine another transaction commits an update to fromAccount here

        Account toAccount = accountRepository.findById(toAccountId)
                                          .orElseThrow(() -> new RuntimeException("To account 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);
        // If fromAccount was updated by another transaction, this transaction might use stale data from the first read.
    }
}

MySQL's Default: REPEATABLE READ and Its Nuances

REPEATABLE READ is MySQL's default isolation level (specifically for InnoDB). This level ensures that within a transaction, any row read once will always return the same data on subsequent reads, even if another transaction modifies and commits that row. It achieves this using Multi-Version Concurrency Control (MVCC), providing a consistent "snapshot" of the database at the start of the transaction. This eliminates dirty reads and non-repeatable reads.

However, REPEATABLE READ doesn't prevent "phantom reads." A phantom read occurs when a transaction re-executes a query that returns a set of rows, and finds rows that were not present previously (or are missing) due to another concurrently committed transaction. For example, if you query all users, then another transaction adds a user and commits, your next query for all users might see the new user. If your business logic requires absolute stability for range queries, you might need explicit FOR UPDATE or FOR SHARE locks or a stricter isolation level. While providing a consistent view, REPEATABLE READ can increase mysql locking contention for writes, impacting throughput and increasing latency p99 in high-write scenarios.

// application.yaml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: user
    password: password
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      idle-timeout: 30000
      connection-timeout: 30000

// In AccountService (no explicit isolation set, defaults to REPEATABLE_READ)
@Transactional
public void reportUserBalanceSummary() {
    List<Account> accounts = accountRepository.findAll();
    // Imagine another transaction adds new accounts and commits here.

    // If we query again later in this transaction, we might still see the original set of accounts
    // due to MVCC snapshot. However, a range query might show phantom rows if not locked.
    // For example, if you ran `SELECT COUNT(*) FROM ACCOUNTS` twice, it would return the same count.
    // But if you ran `SELECT * FROM ACCOUNTS WHERE balance > 100`, then another transaction added
    // an account with balance 200, the second execution of THIS query might return the new row
    // (this is the phantom read).
}

The Safest (and Slowest): SERIALIZABLE

SERIALIZABLE is the highest isolation level. It strictly enforces full isolation, making sure transactions execute completely independently, as if they were running one after another. This eliminates dirty reads, non-repeatable reads, and phantom reads. It guarantees that any concurrent reads and writes will produce the same result as if all transactions ran in some serial order.

While offering the highest data consistency, SERIALIZABLE achieves this by extensive mysql locking. This significantly reduces concurrent reads and writes, often leading to severe performance bottlenecks, increased transaction timeouts, and higher resource consumption. For most Spring Boot applications, SERIALIZABLE is overkill and should be used only for very specific, critical operations where data integrity absolutely cannot tolerate any form of concurrency anomaly, and the performance cost is acceptable. Its impact on latency p99 and overall throughput can be substantial.

@Service
public class PaymentProcessingService {

    @Autowired
    private OrderRepository orderRepository;

    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void processCriticalPayment(Long orderId) {
        Order order = orderRepository.findById(orderId)
                                   .orElseThrow(() -> new RuntimeException("Order not found"));

        // All reads and writes within this transaction are fully isolated.
        // No other transaction can modify data this transaction has read or will read,
        // nor can they insert data that would affect this transaction's range queries,
        // until this transaction commits or rolls back.
        // This comes at a significant cost to concurrency.

        order.setStatus(OrderStatus.PAID);
        orderRepository.save(order);
    }
}

Common Pitfalls

Forgetting @Transactional: Without this annotation (or Spring's transaction proxy not being applied), methods run in auto-commit mode. Each statement becomes its own transaction, completely negating isolation level settings.
Misunderstanding MVCC with REPEATABLE READ: While REPEATABLE READ provides a consistent snapshot for rows already read, range queries (SELECT ... WHERE some_condition) might still show phantom rows if not explicitly locked, leading to subtle data integrity issues.
Overusing SERIALIZABLE: Applying SERIALIZABLE broadly without understanding its performance implications can cripple your application's throughput and introduce severe concurrent reads bottlenecks. Use it sparingly.
Transaction Propagation Issues: Nesting @Transactional methods can lead to unexpected behavior depending on propagation settings (e.g., REQUIRES_NEW). Always be clear on whether a new transaction is started or the existing one is joined.

Conclusion

Choosing the right mysql transaction isolation levels is a delicate balance between data consistency and application performance. As backend engineers, understanding acid transactions, mysql locking and concurrent reads in the context of these levels empowers you to design robust and efficient Spring Boot services. Don't just stick to defaults; choose your isolation level thoughtfully based on your specific business requirements and the acceptable trade-offs.