Pessimistic and Optimistic Locking
Created on: Jan 19, 2025
In this article we will understand practically how Pessimistic and Optimistic Locking works. This article will illustrate update for e-commerce product which is done by applying both types of locks. Let's see the formal definition of both.
Optimistic locking is used when data conflicts are rare, using a version column to prevent concurrent updates unless the data is unchanged.
Pessimistic locking assumes conflicts are likely, locking data for the duration of a transaction to prevent access by other transactions.
Let's start by creating an initial project from start.spring.io. Add Spring Web, Lombok and MySQL Driver as dependency. Select Maven as the project type and Java as the language. Provide appropriate metadata for the project and generate it.
We will be using mysql for this project. So you can install in your local system or use a docker command to quickly start mysql service.
docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=secret@123 -p 3300:3306 -d mysql:latest
Access mysql terminal and create database.
mysql -h 127.0.0.1 -P 3300 -u root -psecret@123 create database ecom; use ecom;
Check if you forget any of dependency in pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.3.4</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>locking</artifactId> <version>0.0.1-SNAPSHOT</version> <name>db-locking</name> <description>Demo project for Spring Boot Pessimistic and Optimistic locking</description> <url/> <licenses> <license/> </licenses> <developers> <developer/> </developers> <scm> <connection/> <developerConnection/> <tag/> <url/> </scm> <properties> <java.version>17</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
Update DbLockingApplication class with below code. Since this is just a poc, so i have added every class in a single file. In a real world application, we will have separate for each class.
package com.example.locking; import jakarta.persistence.Entity; import jakarta.persistence.EntityNotFoundException; import jakarta.persistence.GeneratedValue; import jakarta.persistence.GenerationType; import jakarta.persistence.Id; import jakarta.persistence.LockModeType; import jakarta.persistence.OptimisticLockException; import jakarta.persistence.Version; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Lock; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.orm.ObjectOptimisticLockingFailureException; import org.springframework.stereotype.Repository; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; import java.util.Optional; import java.util.concurrent.CompletableFuture; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; @SpringBootApplication public class DbLockingApplication implements CommandLineRunner { public static void main(String[] args) { SpringApplication.run(DbLockingApplication.class, args); } @Autowired private ProductRepository productRepository; @Autowired private ProductService productService; @Override public void run(String... args) throws Exception { Product product = Product.builder().name("Iphone").price(100000).build(); productRepository.save(product); Product product2 = Product.builder().name("Samsung Galexy s22").price(90000).build(); productRepository.save(product2); ExecutorService executorService = Executors.newFixedThreadPool(4); try { // Simulate concurrent access for PESSISTIMIC LOCKING CompletableFuture<Void> future3 = CompletableFuture.runAsync(() -> { productService.updatePrice(product.getId(), 150000, Thread.currentThread().getName()); }, executorService).exceptionally((e) -> { System.out.println("Exception in thread "+ Thread.currentThread().getName()+" : "+e.getMessage()); return null; }); CompletableFuture<Void> future4 = CompletableFuture.runAsync(() -> { productService.updatePrice(product.getId(), 200000, Thread.currentThread().getName()); }, executorService).exceptionally((e) -> { System.out.println("Exception in thread "+ Thread.currentThread().getName()+" : "+e.getMessage()); return null; }); List<CompletableFuture<Void>> futures2 = List.of(future3, future4); // Combine all results using CompletableFuture.allOf CompletableFuture<Void> allOf2 = CompletableFuture.allOf(futures2.toArray(new CompletableFuture[0])); allOf2.join(); System.out.println(); System.out.println(); // Simulate concurrent access for OPTIMISTIC LOCKING CompletableFuture<Void> future = CompletableFuture.runAsync(() -> { productService.updatePriceOptimistic(product2.getId(), 150000, Thread.currentThread().getName()); }, executorService).exceptionally((e) -> { System.out.println("Exception in thread "+ Thread.currentThread().getName()+" : "+e.getMessage()); return null; }); CompletableFuture<Void> future2 = CompletableFuture.runAsync(() -> { productService.updatePriceOptimistic(product2.getId(), 200000, Thread.currentThread().getName()); }, executorService).exceptionally((e) -> { System.out.println("Exception in thread "+ Thread.currentThread().getName()+" : "+e.getMessage()); return null; }); List<CompletableFuture<Void>> futures = List.of(future, future2); // Combine all results using CompletableFuture.allOf CompletableFuture<Void> allOf = CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])); allOf.join(); } catch (Exception e) { executorService.shutdown(); } } } @Repository interface ProductRepository extends JpaRepository<Product, Long> { @Lock(LockModeType.PESSIMISTIC_WRITE) @Query("SELECT p FROM Product p WHERE p.id = :id") Optional<Product> findByIdWithLock(@Param("id") Long id); @Transactional @Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT) @Query("SELECT p FROM Product p WHERE p.id = :id") Optional<Product> findByIdWithOptimisticLock(@Param("id") Long id); } @Service class ProductService { @Autowired private ProductRepository productRepository; @Transactional public void updatePrice(Long id, double newPrice, String threadName) { System.out.println(threadName + " attempting to fetch product with id: " + id); Product product = productRepository .findByIdWithLock(id) .orElseThrow(EntityNotFoundException::new); System.out.println(threadName + " locked product: " + product); // Simulate some processing try { Thread.sleep(5000); // Simulate delay to hold the lock } catch (InterruptedException e) { Thread.currentThread().interrupt(); } product.setPrice(newPrice); System.out.println(threadName + " updated price to: " + newPrice); } @Transactional public void updatePriceOptimistic(Long id, double newPrice, String threadName) { try { System.out.println(threadName + " attempting to fetch product with id: " + id); Product product = productRepository .findByIdWithOptimisticLock(id) .orElseThrow(EntityNotFoundException::new); System.out.println(threadName + " fetched product: " + product); // Simulate some processing Thread.sleep(5000); // Simulate delay product.setPrice(newPrice); productRepository.save(product); // This will trigger version check System.out.println(threadName + " successfully updated price to: " + newPrice); } catch (InterruptedException e) { Thread.currentThread().interrupt(); } catch (Exception e) { System.out.println("coming in last cache"); e.printStackTrace(); } } } @Data @Builder @Entity @AllArgsConstructor @NoArgsConstructor class Product { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Version private Integer version; private String name; private double price; public Product(String name, double price) { this.name = name; this.price = price; } }
Updtae the properties file
spring.application.name=db-locking spring.jpa.hibernate.ddl-auto=update spring.datasource.url=jdbc:mysql://localhost:3300/ecom spring.datasource.username=root spring.datasource.password=secret@123 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.show-sql=true spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true logging.pattern.console= %d [%level] %c{1.} [%t] %m%n
Run the main application you can see below logs
pool-2-thread-1 attempting to fetch product with id: 58 pool-2-thread-2 attempting to fetch product with id: 58 Hibernate: select p1_0.id,p1_0.name,p1_0.price,p1_0.version from Product p1_0 where p1_0.id=? for update Hibernate: select p1_0.id,p1_0.name,p1_0.price,p1_0.version from Product p1_0 where p1_0.id=? for update pool-2-thread-1 locked product: Product(id=58, version=0, name=Iphone, price=100000.0) pool-2-thread-1 updated price to: 150000.0 Hibernate: update Product set name=?,price=?,version=? where id=? and version=? pool-2-thread-2 locked product: Product(id=58, version=1, name=Iphone, price=150000.0) pool-2-thread-2 updated price to: 200000.0 Hibernate: update Product set name=?,price=?,version=? where id=? and version=? pool-2-thread-4 attempting to fetch product with id: 59 pool-2-thread-3 attempting to fetch product with id: 59 Hibernate: select p1_0.id,p1_0.name,p1_0.price,p1_0.version from Product p1_0 where p1_0.id=? Hibernate: select p1_0.id,p1_0.name,p1_0.price,p1_0.version from Product p1_0 where p1_0.id=? pool-2-thread-4 fetched product: Product(id=59, version=0, name=Samsung Galexy s22, price=90000.0) pool-2-thread-3 fetched product: Product(id=59, version=0, name=Samsung Galexy s22, price=90000.0) pool-2-thread-4 successfully updated price to: 200000.0 pool-2-thread-3 successfully updated price to: 150000.0 Hibernate: update Product set name=?,price=?,version=? where id=? and version=? Hibernate: update Product set name=?,price=?,version=? where id=? and version=? Hibernate: update Product set version=? where id=? and version=? Hibernate: select p1_0.id,p1_0.name,p1_0.price,p1_0.version from Product p1_0 where p1_0.id=? Exception in thread pool-2-thread-3 : org.springframework.orm.ObjectOptimisticLockingFailureException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [com.example.locking.Product#59]
- pool-2-thread-1 locks the product and updates its price to 150000. During this time, Thread-2 waits for the lock to be released.
- Once pool-2-thread-1 completes its transaction and commits, pool-2-thread-2 acquires the lock and updates the price to 200000.
- This demonstrates how pessimistic locking ensures that only one transaction modifies the entity at a time, avoiding race conditions or inconsistent state in the database.
- pool-2-thread-4 successfully updated the product. Also pool-2-thread-3 tried to save product but it failed.
- pool-2-thread-3 encountered mismatched version of
versionandObjectOptimisticLockingFailureExceptionexception is thrown. - last log line
Exception in thread pool-2-thread-3shows the exception ObjectOptimisticLockingFailureExceptionis not cached in the method annotated with@Transactional. This is because When using @Transactional, Spring wraps the method in a proxy to manage the transaction. If an exception occurs within the method and it propagates beyond the proxy, the proxy may transform or wrap the exception. This can lead to the original exception (e.g., ObjectOptimisticLockingFailureException) not being directly caught within the transactional method.
Check the same in mysql db
mysql -h 127.0.0.1 -P 3300 -u root -psecret@123 use ecom; mysql> select * from Product where id in (58, 59) \G; *************************** 1. row *************************** id: 58 name: Iphone price: 200000 version: 2 *************************** 2. row *************************** id: 59 name: Samsung Galexy s22 price: 200000 version: 2 2 rows in set (0.06 sec)
Find whole code in my github here