Your Spring Boot App Is Losing Data — Here’s How Database Locking Fixes It

Introduction

Concurrency is a challenge I’ve faced many times while working on applications where multiple users — or services — access the same data. Whether it’s an inventory management system, a banking application, or an e-commerce platform, ensuring data consistency in a concurrent environment is critical. One of the most effective ways to handle this is through database locking.

In this blog, I’m going to walk you through pessimistic locking and optimistic locking, two widely used strategies for handling concurrency in databases. Along the way, I’ll share practical examples in Java and Spring Boot to help you understand how to implement these strategies in your own projects.

What is Database Locking?

Before diving into the specifics, let’s talk about why locking is crucial. When multiple processes or users access the same data simultaneously, there’s a risk of issues like:

• Dirty reads: Reading uncommitted changes from another transaction.
• Lost updates: Overwriting changes made by other transactions.
• Race conditions: Conflicts that occur when multiple operations simultaneously modify the same data.

To prevent these issues, databases use locking mechanisms to control access to data. This ensures that no two transactions can interfere with each other in a way that compromises consistency.

Pessimistic Locking

What is Pessimistic Locking?

Pessimistic locking assumes that conflicts are likely. It locks data as soon as it’s accessed, preventing other transactions from modifying — or even reading — it until the current transaction is completed.

Best for:

High-write scenarios or when you expect contention.

How it works:

Locks the row in the database during the transaction to prevent other transactions from modifying it until the current transaction completes.

Advantages

• Prevents conflicts proactively: Other transactions can’t interfere until the lock is released.
• Guarantees data consistency.

Disadvantages

• Reduces concurrency: Other transactions have to wait for locks to be released.
• Risk of deadlocks: Two transactions waiting on each other’s locks can cause a deadlock.

Steps:

1. Add a query with PESSIMISTIC_WRITE in your repository:

public interface AccountRepository extends JpaRepository<Account, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT a FROM Account a WHERE a.id = :id")
    Optional<Account> findByIdForUpdate(@Param("id") Long id);
}

2. Deduct balance in a service method:

@Transactional
public void deductBalance(Long accountId, BigDecimal amount) {
    Account account = accountRepository.findByIdForUpdate(accountId)
            .orElseThrow(() -> new RuntimeException("Account not found"));

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

    account.setBalance(account.getBalance().subtract(amount));
    accountRepository.save(account);
}

In this example:

• LockModeType.PESSIMISTIC_WRITE ensures that no other transaction can read or write the record until the current transaction is finished.
• This is great for scenarios with high contention where we need to prevent conflicts entirely.

Optimistic Locking

What is Optimistic Locking?

Optimistic locking assumes that conflicts are rare. Rather than locking the data, it uses versioning to detect conflicts when they happen.

Best for:

High-read, low-write scenarios.

How It Works

1. Add a version column to your database table.
2. When a transaction reads the record, it also reads the version.
3. Before updating the record, the transaction checks whether the version has changed. If it has, the update fails, OptimisticLockException is thrown, and you can retry.

Advantages

• Allows higher concurrency: Multiple transactions can read the same data at the same time.
• No risk of deadlocks.

Disadvantages

• Reactive conflict handling: You have to handle update failures in your application logic.

Steps:

1. Add @Version to your entity:

@Entity
public class Account {
    @Id
    private Long id;

    private BigDecimal balance;

    @Version
    private Integer version;

    // Getters and setters
}

2. Deduct balance in a service method: Add @Version to your entity:

@Transactional
public void deductBalance(Long accountId, BigDecimal amount) {
    Account account = accountRepository.findById(accountId)
            .orElseThrow(() -> new RuntimeException("Account not found"));

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

    account.setBalance(account.getBalance().subtract(amount));
    accountRepository.save(account);
}

In this example:

• The @Version annotation enables optimistic locking.
• If another transaction updates the record before yours, the version mismatch will throw an exception (e.g., OptimisticLockException).

When to Use Each Approach

Here’s how I decide which locking strategy to use:

Use Pessimistic Locking When:

• You expect frequent conflicts (e.g., in inventory systems where multiple users update stock).
• Consistency is critical, and you can’t afford update failures.

Use Optimistic Locking When:

• You expect minimal conflicts (e.g., read-heavy applications like e-commerce platforms).
• You need better performance and higher concurrency.

Conclusion

Concurrency challenges are inevitable in modern applications, but database locking provides powerful tools to handle them. While pessimistic locking is great for preventing conflicts in high-contention scenarios, optimistic locking offers better performance in systems where conflicts are rare.

I hope this guide helps you understand and implement these strategies in your projects. If you have any questions or insights, feel free to share them in the comments. :)