Always will be ready notify the world about expectations as easy as possible: job change page
May 26

Managing concurrent database updates

Managing concurrent database updates

Data consistency in a Spring Boot application

Concurrent database updates refer to situations in which multiple users or processes attempt to modify the same database record or data concurrently, at the same time or in rapid succession. In a multi-user or multi-threaded environment, concurrent updates can occur when multiple entities, such as users or applications, access and modify the same data simultaneously. Concurrent database updates can lead to various issues and challenges, including:

  1. Data inconsistency: If not managed properly, concurrent updates can result in data inconsistency, where the database contains conflicting or incorrect information.
  2. Lost updates: One update might overwrite the changes made by another update, leading to lost data.
  3. Dirty reads: A transaction may read data that is in the process of being updated by another transaction, resulting in inaccurate or incomplete information.
  4. Non-repeatable reads: A transaction may read the same data multiple times but get different results each time due to ongoing updates by other transactions.

To prevent concurrent database updates in a Spring Boot application, you can use various strategies and techniques:

  1. Database locking: Utilize database-level locks, such as row-level or table-level locks, to ensure that only one transaction can update a particular record at a time. Spring Boot supports declarative transaction management with the @Transactional annotation, which can be used in combination with database locking mechanisms.
  2. Optimistic locking: Use optimistic locking with versioning. This involves adding a version column to your database table and checking this version during updates. If the version has changed since you retrieved the data, the update will fail, indicating concurrent modification.
  3. Pessimistic locking: Implement pessimistic locking by explicitly locking records or tables before updates using the SELECT ... FOR UPDATE SQL statement or similar database-specific mechanisms.
  4. Isolation levels: Configure the isolation level of database transactions. Higher isolation levels like SERIALIZABLE ensure that transactions are executed in a way that prevents concurrent updates, but they can impact performance.
  5. Application-level locking: Implement application-level locking using Java constructs like synchronized blocks or other thread synchronization mechanisms to control access to critical sections of your code.
  6. Database transactions: Use database transactions wisely, ensuring that transactions are short-lived and only hold locks for the necessary duration to minimize the chance of conflicts.
  7. Retry strategies: Implement retry mechanisms in case of optimistic locking failures, allowing the application to retry the operation after a short delay.

The choice of strategy depends on the specific requirements and constraints of your application. In many cases, a combination of these techniques may be necessary to effectively prevent concurrent database updates and ensure data consistency.

Database locking

The following code block demonstrates how to use Spring’s @Transactional annotation to ensure that only one thread can update a specific database record at a time.

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

public class ProductService {
    private ProductRepository productRepository;

    public void updateProductPrice(int productId, double newPrice) {
        Product product = productRepository.findById(productId);
        // The @Transactional annotation ensures that this update operation is atomic.
        // Only one thread can update the product at a time.

@Transactional: This annotation is applied to the updateProductPrice method. It indicates that this method should run within a transaction. Transactions are used to group one or more database operations into a single, atomic unit. When you mark a method as @Transactional, Spring will handle transaction management for you.

Inside the updateProductPrice method:

  • It first fetches a Product entity by its productId using the productRepository.findById(productId) method. This fetch operation is part of the transaction.
  • Then, it sets the new price for the product with product.setPrice(newPrice).
  • Because the method is annotated with @Transactional, the entire sequence of fetching the product and updating its price is treated as a single, atomic transaction.

The key point to note here is that the @Transactional annotation ensures that only one thread can execute the updateProductPrice method at a time, preventing concurrent updates to the same product record. If another thread attempts to call this method while a transaction is in progress, it will have to wait until the transaction is completed, ensuring data consistency and preventing concurrency issues.

Optimistic locking

The following code block demonstrates how to implement optimistic locking in a Spring Boot application using the @Version annotation in JPA.

public class Product {

    private Long id;

    private String name;
    private double price;

    private int version; // Version field for optimistic locking

    // Getters and setters

private int version: This field is annotated with @Version, which is a special annotation used for optimistic locking. The purpose of this field is to keep track of the entity's version. Each time the entity is updated, the version is incremented automatically by JPA.

Optimistic locking works as follows:

  • When you retrieve a Product entity from the database, JPA records the version number of the entity at that time.
  • When you update the Product entity and save it back to the database, JPA automatically checks whether the version of the entity in the database matches the version you retrieved earlier. If they match, it allows the update to proceed. If they don't match, it indicates that another transaction has updated the same entity concurrently, and it will typically throw an exception (e.g., OptimisticLockException) to handle this scenario.

This mechanism ensures that updates are only applied when the entity’s version matches what you initially retrieved, preventing concurrent updates from causing data inconsistency.

In the service or repository methods, you would typically handle the optimistic locking exception by catching it and taking appropriate action, such as informing the user or retrying the operation.

Pessimistic locking

The following code block demonstrates how to implement pessimistic locking in a Spring Boot application using a native SQL query with the FOR UPDATE clause to explicitly lock the selected database record for update. This means that if another transaction attempts to update the same record concurrently, it will be blocked until the lock is released, ensuring data consistency.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

public class ProductRepository {

    private JdbcTemplate jdbcTemplate;

    public Product findByIdForUpdate(int productId) {
        // Use a native SQL query with "FOR UPDATE" to lock the record for update.
        return jdbcTemplate.queryForObject(
            "SELECT * FROM product WHERE id = ? FOR UPDATE",
            new Object[]{productId},
            (rs, rowNum) -> new Product(

findByIdForUpdate(int productId): This method is designed to retrieve a Product entity by its productId while locking it using a native SQL query. Inside the method:

  • It constructs a SQL query using the FOR UPDATE clause which is a database-specific feature that locks the selected rows until the transaction is committed, preventing other transactions from updating them concurrently.
  • The jdbcTemplate.queryForObject method is used to execute the SQL query and retrieve the Product entity. It also maps the result set (the row from the database) to a Product object.

Keep in mind that the exact SQL syntax for FOR UPDATE may vary depending on your database system (e.g., MySQL, PostgreSQL, Oracle), so you should adapt it to your specific database. Additionally, you'll need to handle exceptions and transaction management appropriately in your service layer when using pessimistic locking to deal with potential contention scenarios.

Isolation levels

Implementing isolation levels requires configuring the database and specifying the isolation level in your Spring Boot application. Below is a code block that demonstrates how to set Serializable isolation level in a Spring Boot application:

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

public class ProductService {

    private ProductRepository productRepository;

    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void updateProductPrice(int productId, double newPrice) {
        Product product = productRepository.findById(productId);
        // The @Transactional annotation with SERIALIZABLE isolation level ensures the highest level of isolation.

@Transactional(isolation = Isolation.SERIALIZABLE): The @Transactional annotation is applied to the updateProductPrice method with the isolation attribute set to Isolation.SERIALIZABLE. This attribute specifies the isolation level for the transaction.

Isolation levels

  • Isolation.SERIALIZABLE: This is the highest isolation level. It ensures that transactions are executed serially, one after the other. It provides full isolation, preventing concurrent transactions from affecting each other's data. However, it may impact performance due to the strict serialization.
  • Other isolation levels that can be used include Isolation.READ_COMMITTED, Isolation.REPEATABLE_READ, and Isolation.READ_UNCOMMITTED. Each of these levels provides varying degrees of data consistency and concurrency control.

Product product = productRepository.findById(productId);: Inside the updateProductPrice method, a Product entity is fetched from the database. The specific isolation level configured in the @Transactional annotation will affect how this read operation behaves in the presence of concurrent transactions.

SERIALIZABLE ensures the highest level of isolation but may lead to reduced concurrency and potentially longer transaction times. Other isolation levels provide a balance between concurrency and data consistency, allowing multiple transactions to occur simultaneously with varying degrees of isolation from each other.

The choice of isolation level should align with your application’s requirements, considering factors like data integrity, performance, and the likelihood of concurrent access.

Application-level locking

Implementing application-level locking using constructs like synchronized ensures that only one thread can execute a particular block of code at a time.

import org.springframework.stereotype.Service;

public class ProductService {
    private final Object lock = new Object();

    public void updateProductPrice(int productId, double newPrice) {
        synchronized (lock) {
            // This synchronized block ensures that only one thread can execute this code at a time.
            Product product = productRepository.findById(productId);

synchronized (lock) { ... }: This block of code is synchronized using the lock object. This means that only one thread can execute the code inside this block at any given time.

  • Inside the synchronized block, the Product entity is fetched from the database using productRepository.findById(productId);. This operation is now protected by the synchronization, ensuring that concurrent threads cannot simultaneously execute this part of the code.
  • After retrieving the Product, it sets the new price with product.setPrice(newPrice);.

Application-level locking using synchronized is a simple way to ensure that critical sections of your code are protected against concurrent access. However, it’s essential to use application-level locking judiciously, as excessive synchronization can lead to performance bottlenecks and potential deadlocks.

Retry strategies

Implementing retry strategies can be useful when dealing with concurrency issues or network-related problems.

import org.springframework.stereotype.Service;

public class ProductService {
    private static final int MAX_RETRIES = 3; // Maximum number of retry attempts
    public void updateProductPriceWithRetry(int productId, double newPrice) {
        int retryCount = 0;
        boolean success = false;

        while (retryCount < MAX_RETRIES && !success) {
            try {
                updateProductPrice(productId, newPrice);
                success = true;
            } catch (ConcurrencyException e) {
                // Handle concurrency exception, e.g., by logging or waiting before retrying.

        if (!success) {
            // Handle the situation where retries were exhausted, e.g., by throwing an error or logging.

    private void updateProductPrice(int productId, double newPrice) throws ConcurrencyException {
        // Simulate a concurrency issue by throwing an exception.
        // In practice, you would perform the actual update and handle any concurrency issues here.
        throw new ConcurrencyException("Concurrency issue occurred during update.");

This code demonstrates a simple retry strategy with a limited number of retry attempts. You can customize the retry logic, error handling, and maximum retry count to suit your specific requirements and use cases. Additionally, consider using more advanced retry libraries like Spring Retry for more robust and configurable retry strategies.


The strategies to prevent concurrent database updates play a pivotal role in maintaining data consistency and integrity in multi-user and multi-threaded environments. Whether through database locking mechanisms, optimistic or pessimistic locking, isolation levels, or application-level synchronization, each approach offers distinct advantages and trade-offs. The key lies in selecting and implementing these strategies thoughtfully, tailored to the specific needs of the application, to ensure a robust and reliable database system that stands up to the challenges of concurrent updates.

27 августа 2020 г.
Автор: Alex Myzgin
«Чистые» функции - это любые функции, исходные данные которых получены исключительно из их входных данных и не вызывают побочных эффектов в приложении. Математические функции являются примерами «чистых» функций. «Нечистые» функции бывают разных форм и размеров. Вот некоторые примеры: функции, вывод...
Dec 19, 2020
Author: Asaf Yigal
Relational Database Management Systems (RDBMS) are one of the most widely used database management systems in the world. Based on the relational model invented by Edgar F. Codd, these databases store data in the form of tables, and allow the...
Apr 6, 2013
Introduction A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set of statements stored in a variable and executed using a SQL command. There may be several methods of implementing this in SQL Server. This...
Mar 21
Author: Mohammad Hussain
Introduction A common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL Server. CTEs were introduced in SQL Server 2005 and are similar to derived tables...
Send message
Your name

© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban