Spring Boot + MySQL Deadlock

When building high-concurrency backend systems with Spring Boot and MySQL, developers often encounter a frustrating issue: intermittent API failures and unexpected transaction rollbacks. In many cases, these symptoms point to a deadlock.

A deadlock occurs when two or more transactions hold locks on resources that the others need, creating a circular dependency where no transaction can proceed. Fortunately, the InnoDB storage engine includes a deadlock detection mechanism (innodb_deadlock_detect). When a deadlock is identified, InnoDB automatically rolls back the transaction that has performed the least amount of work to break the cycle.

In a Spring Boot environment, this MySQL error is typically surfaced as a DeadlockLoserDataAccessException, which corresponds to MySQL error code 1213.
Understanding how deadlocks manifest in real-world business logic is the first step toward prevention.

1. Inconsistent Locking Order in Batch Updates

This is perhaps the most common cause of deadlocks in batch processing. If multiple transactions attempt to update the same set of rows but in a different order, a deadlock is almost inevitable.

Consider an account table. Suppose a service performs batch updates like this:

@Transactional
public void updateBalances(List<Long> ids) {
    for (Long id : ids) {
        accountMapper.updateBalance(id, 100);
    }
}

If Transaction A receives the list [1, 2] and Transaction B receives [2, 1], the following sequence occurs:

1. Transaction A acquires an exclusive lock on id=1.
2. Transaction B acquires an exclusive lock on id=2.
3. Transaction A requests a lock on id=2 and enters a "waiting" state.
4. Transaction B requests a lock on id=1 and enters a "waiting" state.

At this point, both transactions are waiting for each other, and a deadlock is triggered.

2. Lock Upgrades via Unique Key Conflicts

Deadlocks can also occur during concurrent INSERT ... ON DUPLICATE KEY UPDATE operations or when using a "Select-then-Insert" pattern.

When multiple transactions attempt to insert a record with the same unique key, they may both initially acquire a Shared (S) lock on the index record. When both transactions subsequently attempt to upgrade that S-lock to an Exclusive (X) lock to perform the update, they will both be waiting for the other to release the S-lock, resulting in a deadlock.

3. Gap Locks and the REPEATABLE READ Isolation Level

By default, MySQL uses the REPEATABLE READ isolation level. To prevent "phantom reads," InnoDB employs Gap Locks—locks on the "gaps" between index records.

If an UPDATE or DELETE statement uses a range condition or fails to hit a specific index, InnoDB may lock a wide range of gaps. If two transactions lock adjacent gaps and then both attempt to insert a new record into the gap held by the other, a deadlock occurs.

How to Troubleshoot Deadlocks

When a deadlock occurs in production, you need actionable data to find the root cause.

1. Inspect the InnoDB Status

The most immediate way to see the details of the most recent deadlock is to run the following SQL command:

SHOW ENGINE INNODB STATUS;

Look for the LATEST DETECTED DEADLOCK section. This output is invaluable because it provides:

• The specific SQL statements involved in the deadlock.
• The types of locks held by each transaction (S-locks, X-locks, etc.).
• The specific index and record where the conflict occurred.

2. Enable Persistent Deadlock Logging

Since SHOW ENGINE INNODB STATUS only shows the latest deadlock, it is difficult to catch intermittent issues. You should enable global logging in your MySQL configuration (my.cnf or my.ini):

innodb_print_all_deadlocks = ON

With this setting enabled, every deadlock detected by the engine will be written to the MySQL error log, allowing you to perform long-term trend analysis.

3. Correlate with Application Logs

Once you have identified the problematic SQL from the database logs, map it back to your Spring Boot code. Look for the @Transactional boundaries surrounding those queries to understand the full lifecycle of the transaction and the sequence of operations.

Best Practices and Solutions

Preventing deadlocks requires a combination of defensive coding and database optimization.

1. Enforce a Deterministic Locking Order

The most effective way to prevent the "Inconsistent Order" scenario is to ensure that all transactions acquire locks in the same sequence. Always sort your input lists by a unique identifier (like a Primary Key) before processing them.

@Transactional
public void updateBalances(List<Long> ids) {
    // Sort the IDs to ensure consistent locking order
    ids.stream().sorted().forEach(id -> {
        accountMapper.updateBalance(id, 100);
    });
}

2. Minimize Transactional Scope

The longer a transaction stays open, the longer it holds locks, increasing the probability of conflict.

• Avoid heavy I/O: Never perform RPC calls, send emails, or write large files inside a @Transactional method.
• Keep it lean: Perform all necessary data preparation before opening the transaction, and commit as soon as the database work is done.

3. Optimize Indexing to Avoid Gap Locks

To prevent wide-ranging gap locks, ensure that your WHERE clauses are backed by efficient indexes. If a query performs a full table scan, InnoDB may lock every record and every gap in the table. Using SELECT ... FOR UPDATE should be done cautiously and only on specific, indexed rows.

4. Adjust the Isolation Level

If your business logic can tolerate it, consider switching the isolation level to READ COMMITTED. This level significantly reduces the use of gap locks, which eliminates a large category of deadlocks common in REPEATABLE READ.

In Spring Boot, you can configure this in your datasource URL:

spring.datasource.url=jdbc:mysql://localhost:3306/db?transactionIsolation=READ-COMMITTED

5. Implement an Automated Retry Mechanism

Since deadlocks are sometimes unavoidable in highly concurrent environments, you should design your system to recover gracefully. Using Spring Retry, you can automatically re-execute a failed transaction.

@Retryable(
    retryFor = DeadlockLoserDataAccessException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 200)
)
@Transactional
public void updateBalances(List<Long> ids) {
    ids.stream().sorted().forEach(id -> accountMapper.updateBalance(id, 100));
}

Important Implementation Notes:

• Proxy Awareness: Ensure the @Retryable method is called from a different bean; otherwise, the Spring AOP proxy will be bypassed, and the retry will not trigger.
• Transaction Integrity: The @Retryable annotation should be on the entry point that initiates the transaction, ensuring the entire transaction is retried from the beginning.

Summary Table

Deadlocks are a natural byproduct of high concurrency. While you cannot always eliminate them entirely, a strategy of prevention through design (sorting, indexing) combined with resilience through retries will ensure your Spring Boot application remains stable under heavy load.