[Advanced Spring Boot] Locking và concurrency trong Spring Boot: optimistic @Version, pessimistic lock và race condition

curl -s "https://start.spring.io/starter.zip?type=gradle-project&language=java&bootVersion=4.1.1&javaVersion=21&groupId=com.example&artifactId=demo&name=demo&packageName=com.example.demo&dependencies=web,data-jpa,postgresql,flyway,validation" -o demo.zip

docker run -d --name sba-a7-pg -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5507:5432 postgres:18

spring.application.name=demo
server.port=8207
spring.datasource.url=jdbc:postgresql://localhost:5507/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=validate
logging.level.org.hibernate.SQL=debug

create table products (
    id          bigint generated by default as identity primary key,
    sku         varchar(40)    not null unique,
    name        varchar(120)   not null,
    description varchar(1000),
    price       numeric(10, 2) not null,
    stock       integer        not null
);
 
insert into products (sku, name, description, price, stock) values
    ('KB-01', 'Mechanical keyboard', 'Tenkeyless, brown switches', 89.90, 10),
    ('MS-01', 'Wireless mouse', 'Two-button, USB-C receiver', 24.50, 10),
    ('HUB-07', 'USB-C hub', 'Seven ports, 100 W pass-through', 39.00, 50);

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
 
@Entity
@Table(name = "products")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(nullable = false, length = 40, unique = true)
    private String sku;
 
    @Column(nullable = false, length = 120)
    private String name;
 
    @Column(length = 1000)
    private String description;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    @Column(nullable = false)
    private int stock;
 
    protected Product() {
    }
 
    public void update(String name, String description, BigDecimal price) {
        this.name = name;
        this.description = description;
        this.price = price;
    }
 
    public void decreaseStock(int quantity) {
        if (quantity > stock) {
            throw new InsufficientStockException(sku, stock, quantity);
        }
        stock -= quantity;
    }
 
    // getters for every field
}

package com.example.demo.product;
 
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
@Service
@Transactional(readOnly = true)
public class ProductService {
 
    private final ProductRepository products;
 
    public ProductService(ProductRepository products) {
        this.products = products;
    }
 
    public Product findById(Long id) {
        return products.findById(id).orElseThrow(() -> new ProductNotFoundException(id));
    }
 
    @Transactional
    public Product update(Long id, UpdateProductRequest request) {
        Product product = findById(id);
        product.update(request.name(), request.description(), request.price());
        return product;
    }
}

package com.example.demo.product;
 
import jakarta.validation.Valid;
 
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
@RequestMapping("/api/products")
public class ProductController {
 
    private final ProductService service;
 
    public ProductController(ProductService service) {
        this.service = service;
    }
 
    @GetMapping("/{id}")
    public ProductResponse findById(@PathVariable Long id) {
        return ProductResponse.from(service.findById(id));
    }
 
    @PutMapping("/{id}")
    public ProductResponse update(@PathVariable Long id, @Valid @RequestBody UpdateProductRequest request) {
        return ProductResponse.from(service.update(id, request));
    }
}

package com.example.demo;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
 
public final class Race {
 
    public record Outcome(Map<String, Integer> counts, long millis) {
    }
 
    private Race() {
    }
 
    public static Outcome run(int threads, Runnable task) throws Exception {
        CountDownLatch ready = new CountDownLatch(threads);
        CountDownLatch start = new CountDownLatch(1);
        List<Future<String>> results = new ArrayList<>();
        try (ExecutorService pool = Executors.newFixedThreadPool(threads)) {
            for (int i = 0; i < threads; i++) {
                results.add(pool.submit(() -> {
                    ready.countDown();
                    start.await();
                    try {
                        task.run();
                        return "ok";
                    } catch (RuntimeException e) {
                        return e.getClass().getSimpleName();
                    }
                }));
            }
            ready.await();
            long begin = System.nanoTime();
            start.countDown();
            Map<String, Integer> counts = new TreeMap<>();
            for (Future<String> result : results) {
                counts.merge(result.get(), 1, Integer::sum);
            }
            return new Outcome(counts, (System.nanoTime() - begin) / 1_000_000);
        }
    }
}

tasks.named('test') {
	testLogging {
		showStandardStreams = true
	}
}

./gradlew test --tests '*LostUpdateTest'

curl -s http://localhost:8207/api/products/1

{"id":1,"sku":"KB-01","name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":89.90,"stock":10}

curl -s -X PUT -H 'Content-Type: application/json' -d '{"name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":79.90}' http://localhost:8207/api/products/1

{"id":1,"sku":"KB-01","name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":79.90,"stock":10}

curl -s -X PUT -H 'Content-Type: application/json' -d '{"name":"Mechanical keyboard","description":"Tenkeyless, brown switches, PBT keycaps","price":89.90}' http://localhost:8207/api/products/1

{"id":1,"sku":"KB-01","name":"Mechanical keyboard","description":"Tenkeyless, brown switches, PBT keycaps","price":89.90,"stock":10}

select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where p1_0.id=?
update products set description=?,name=?,price=?,sku=?,stock=? where id=?

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
class LostUpdateTest {
 
    @Autowired
    ProductRepository products;
 
    @Autowired
    TransactionTemplate tx;
 
    @Autowired
    JdbcClient jdbc;
 
    @Test
    void twoEditorsOfOneProduct() throws Exception {
        for (int round = 1; round <= 10; round++) {
            jdbc.sql("update products set price = 89.90, description = 'Tenkeyless, brown switches' where id = 1").update();
            CyclicBarrier bothLoaded = new CyclicBarrier(2);
            String[] errors = new String[2];
            Thread alice = new Thread(() -> errors[0] = edit(bothLoaded, p -> p.update(p.getName(), p.getDescription(), new BigDecimal("79.90"))));
            Thread bob = new Thread(() -> errors[1] = edit(bothLoaded, p -> p.update(p.getName(), "Tenkeyless, brown switches, PBT keycaps", p.getPrice())));
            alice.start();
            bob.start();
            alice.join();
            bob.join();
            // read price and description back with JdbcClient, count and print the round
        }
    }
 
    private String edit(CyclicBarrier bothLoaded, Consumer<Product> change) {
        try {
            tx.executeWithoutResult(status -> {
                Product product = products.findById(1L).orElseThrow();
                await(bothLoaded); // both transactions have read the row
                change.accept(product);
            });
            return null;
        } catch (RuntimeException e) {
            return e.getClass().getSimpleName();
        }
    }
}

round  1: price 79.90, description 'Tenkeyless, brown switches', alice committed, bob committed
round  2: price 79.90, description 'Tenkeyless, brown switches', alice committed, bob committed
round  3: price 89.90, description 'Tenkeyless, brown switches, PBT keycaps', alice committed, bob committed
both kept 0, price lost 5, description lost 5, rounds with an exception 0

alter table products add column version bigint not null default 0;

import jakarta.persistence.Table;
import jakarta.persistence.Version; 
 
@Entity
@Table(name = "products")
public class Product {
 
    // ...
 
    @Column(nullable = false)
    private int stock;
 
    @Version
    private Long version; 
 
    // ...
 
    public Long getVersion() { 
        return version; 
    } 
}

update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?

round  1: price 79.90, description 'Tenkeyless, brown switches', alice committed, bob ObjectOptimisticLockingFailureException
round  2: price 79.90, description 'Tenkeyless, brown switches', alice committed, bob ObjectOptimisticLockingFailureException
round  3: price 89.90, description 'Tenkeyless, brown switches, PBT keycaps', alice ObjectOptimisticLockingFailureException, bob committed
both kept 0, price lost 8, description lost 2, rounds with an exception 10

A flushed at 27 ms, holding the row for 1000 ms
B flushes at 228 ms
A committed at 1037 ms
B threw ObjectOptimisticLockingFailureException at 1046 ms

org.springframework.orm.ObjectOptimisticLockingFailureException: Unexpected row count (expected row count 1 but was 0) [update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?] for entity [com.example.demo.product.Product with id '1']
org.hibernate.StaleObjectStateException: Unexpected row count (expected row count 1 but was 0) [update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?] for entity [com.example.demo.product.Product with id '1']
org.hibernate.StaleStateException: Unexpected row count (expected row count 1 but was 0) [update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?]

    @Test
    void saveAllOfStaleDetachedEntities() {
        List<Product> all = products.findAll(); // detached once the repository call returns
        productService.update(2L, new UpdateProductRequest("Wireless mouse", "Two-button, USB-C receiver",
                new BigDecimal("22.00"), 0L));
        all.forEach(p -> p.update(p.getName(), p.getDescription(), p.getPrice().multiply(new BigDecimal("1.10"))));
        products.saveAll(all); // wrapped in try/catch and printed in the real test
    }

after the edit: [{id=1, price=89.90, version=0}, {id=2, price=22.00, version=1}, {id=3, price=39.00, version=0}]
>>> saveAll
select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=?
select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=?
saveAll threw
  chain: org.springframework.orm.ObjectOptimisticLockingFailureException: Row was already updated or deleted by another transaction for entity [com.example.demo.product.Product with id '2']
  chain: org.hibernate.StaleObjectStateException: Row was already updated or deleted by another transaction for entity [com.example.demo.product.Product with id '2']
after saveAll: [{id=1, price=89.90, version=0}, {id=2, price=22.00, version=1}, {id=3, price=39.00, version=0}]

    @Modifying
    @Query("update Product p set p.price = p.price * :factor")
    int repriceAll(BigDecimal factor);
 
    @Modifying
    @Query("update versioned Product p set p.price = p.price * :factor")
    int repriceAllVersioned(BigDecimal factor);

>>> bulk update, versioned=false
update products p1_0 set price=(p1_0.price*?)
bulk update changed 3 rows
after the bulk update: [{id=1, price=98.89, version=0}, {id=2, price=26.95, version=0}, {id=3, price=42.90, version=0}]
update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?
editor committed
after the editor: [{id=1, price=89.90, version=1}, {id=2, price=26.95, version=0}, {id=3, price=42.90, version=0}]

>>> bulk update, versioned=true
update products p1_0 set price=(p1_0.price*?),version=(p1_0.version+1)
bulk update changed 3 rows
after the bulk update: [{id=1, price=98.89, version=1}, {id=2, price=26.95, version=1}, {id=3, price=42.90, version=1}]
update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?
editor threw
  chain: org.springframework.orm.ObjectOptimisticLockingFailureException: Unexpected row count (expected row count 1 but was 0) [update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?] for entity [com.example.demo.product.Product with id '1']

package com.example.demo.product;
 
import java.math.BigDecimal;
 
public record ProductResponse(Long id, String sku, String name, String description, BigDecimal price, int stock,
                              Long version) {
 
    static ProductResponse from(Product product) {
        return new ProductResponse(product.getId(), product.getSku(), product.getName(),
                product.getDescription(), product.getPrice(), product.getStock(), product.getVersion());
    }
}

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import jakarta.validation.constraints.Digits;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import jakarta.validation.constraints.Positive;
import jakarta.validation.constraints.Size;
 
public record UpdateProductRequest(
        @NotBlank @Size(max = 120) String name,
        @Size(max = 1000) String description,
        @NotNull @Positive @Digits(integer = 8, fraction = 2) BigDecimal price,
        @NotNull Long version) {
}

package com.example.demo.product;
 
import com.example.demo.common.ConflictException;
 
public class StaleProductException extends ConflictException {
 
    public StaleProductException(Long id, Long current, Long expected) {
        super("Product " + id + " is at version " + current + ", the update was based on version " + expected);
    }
}

    @Transactional
    public Product update(Long id, UpdateProductRequest request) {
        Product product = findById(id);
        if (!Objects.equals(product.getVersion(), request.version())) { 
            throw new StaleProductException(id, product.getVersion(), request.version()); 
        } 
        product.update(request.name(), request.description(), request.price());
        return product;
    }

    @ExceptionHandler(ConflictException.class)
    public ProblemDetail conflict(ConflictException ex) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT, ex.getMessage());
    }
 
    @ExceptionHandler(OptimisticLockingFailureException.class) 
    public ProblemDetail concurrentUpdate(OptimisticLockingFailureException ex) { 
        log.info("Optimistic lock conflict: {}", ex.getMessage()); 
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT, 
                "The resource was changed by another request. Reload it and apply your change again."); 
    } 

curl -s http://localhost:8207/api/products/1

{"id":1,"sku":"KB-01","name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":89.90,"stock":10,"version":3}

curl -i -s -X PUT -H 'Content-Type: application/json' -d '{"name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":79.90,"version":3}' http://localhost:8207/api/products/1

HTTP/1.1 200
Content-Type: application/json
Content-Length: 131
Date: Fri, 18 Sep 2026 05:04:15 GMT
 
{"id":1,"sku":"KB-01","name":"Mechanical keyboard","description":"Tenkeyless, brown switches","price":79.90,"stock":10,"version":4}

curl -i -s -X PUT -H 'Content-Type: application/json' -d '{"name":"Mechanical keyboard","description":"Tenkeyless, brown switches, PBT keycaps","price":89.90,"version":3}' http://localhost:8207/api/products/1

HTTP/1.1 409
Content-Type: application/problem+json
Transfer-Encoding: chunked
Date: Fri, 18 Sep 2026 05:04:15 GMT
 
{"detail":"Product 1 is at version 4, the update was based on version 3","instance":"/api/products/1","status":409,"title":"Conflict"}

HTTP/1.1 422
Content-Type: application/problem+json
Transfer-Encoding: chunked
Date: Fri, 18 Sep 2026 05:04:15 GMT
 
{"detail":"Request has 1 invalid value(s).","instance":"/api/products/1","status":422,"title":"Unprocessable Content","errors":[{"field":"version","message":"must not be null"}]}

seq 1 10 | xargs -P 10 -I{} sh -c "curl -s -o put/{}.json -w '%{http_code}\n' -X PUT -H 'Content-Type: application/json' -d '{\"name\":\"Mechanical keyboard\",\"description\":\"Run 1, edit {}\",\"price\":79.90,\"version\":10}' http://localhost:8207/api/products/1" | sort | uniq -c

   1 200
   9 409

cat put/*.json | jq -r '.detail // "200 OK"' | sort | uniq -c

   1 200 OK
   6 Product 1 is at version 11, the update was based on version 10
   3 The resource was changed by another request. Reload it and apply your change again.

@Service
public class StockService {
 
    private final ProductRepository products;
 
    public StockService(ProductRepository products) {
        this.products = products;
    }
 
    @Transactional
    public void reserve(Long id, int quantity) {
        Product product = products.findById(id).orElseThrow(() -> new ProductNotFoundException(id));
        product.decreaseStock(quantity);
    }
}

package com.example.demo.product;
 
import java.util.concurrent.ThreadLocalRandom;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.OptimisticLockingFailureException;
import org.springframework.stereotype.Service;
 
@Service
public class StockReservations {
 
    private static final Logger log = LoggerFactory.getLogger(StockReservations.class);
    private static final int MAX_ATTEMPTS = 5;
 
    private final StockService stock;
 
    public StockReservations(StockService stock) {
        this.stock = stock;
    }
 
    public void reserve(Long id, int quantity) {
        for (int attempt = 1; ; attempt++) {
            try {
                stock.reserve(id, quantity);
                return;
            } catch (OptimisticLockingFailureException e) {
                if (attempt == MAX_ATTEMPTS) {
                    throw e;
                }
                log.debug("Conflict on product {}, attempt {} of {}", id, attempt, MAX_ATTEMPTS);
                backOff(attempt);
            }
        }
    }
 
    private static void backOff(int attempt) {
        try {
            Thread.sleep(ThreadLocalRandom.current().nextLong(1, 10L * attempt));
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new IllegalStateException("Interrupted while retrying", e);
        }
    }
}

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE,
        properties = {"logging.level.org.hibernate.SQL=info", "logging.level.com.example.demo.product.StockReservations=debug"})
class RetryTest {
 
    // StockService stock, StockReservations reservations and JdbcClient jdbc are @Autowired
 
    @Test
    void retryPlacement() throws Exception {
        run("no retry", () -> stock.reserve(HUB, 1));
        run("retry around the transaction", () -> reservations.reserve(HUB, 1));
        run("retry inside one transaction", () -> reservations.reserveInOneTransaction(HUB, 1));
    }
 
    private void run(String name, Runnable task) throws Exception {
        for (int round = 1; round <= 3; round++) {
            jdbc.sql("update products set stock = 50 where id = ?").param(HUB).update();
            Race.Outcome outcome = Race.run(20, task);
            // print outcome.counts() and the stock left
        }
    }
}

    @Transactional
    public void reserveInOneTransaction(Long id, int quantity) {
        for (int attempt = 1; ; attempt++) {
            try {
                stock.reserve(id, quantity);
                return;
            } catch (OptimisticLockingFailureException e) {
                if (attempt == MAX_ATTEMPTS) {
                    throw e;
                }
                log.debug("Conflict on product {}, attempt {} of {}", id, attempt, MAX_ATTEMPTS);
            }
        }
    }

org.springframework.orm.ObjectOptimisticLockingFailureException: Unexpected row count (expected row count 1 but was 0) [update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?] for entity [com.example.demo.product.Product with id '3']
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:794)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:757)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:687)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:408)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:130)
	at com.example.demo.product.StockReservations$$SpringCGLIB$$0.reserveInOneTransaction(<generated>)

attempt 1 read stock 50 version 108
attempt 1 caught ObjectOptimisticLockingFailureException
attempt 2 read stock 49 version 108
attempt 2 caught ObjectOptimisticLockingFailureException
attempt 3 read stock 48 version 108
attempt 3 caught ObjectOptimisticLockingFailureException
outer threw org.springframework.transaction.UnexpectedRollbackException: Transaction silently rolled back because it has been marked as rollback-only

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Product> findForUpdateById(Long id);
 
    @Lock(LockModeType.PESSIMISTIC_READ)
    Optional<Product> findForShareById(Long id);
 
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "2000"))
    Optional<Product> findForUpdateWithTimeoutById(Long id);
 
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "0"))
    Optional<Product> findForUpdateNoWaitById(Long id);
 
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "-2"))
    Optional<Product> findForUpdateSkipLockedById(Long id);

    void contend(String label, Runnable holderLock, long holdMillis, Supplier<Object> contender) throws Exception {
        CountDownLatch locked = new CountDownLatch(1);
        Thread holder = new Thread(() -> tx.executeWithoutResult(s -> {
            holderLock.run();
            locked.countDown();
            sleep(holdMillis);
        }));
        holder.start();
        locked.await();
        sleep(100);
        long begin = System.nanoTime();
        // tx.execute(s -> contender.get()), then print the result or the exception chain
        // and the milliseconds since begin
    }
 
    @Test
    void lockModes() throws Exception {
        Runnable write = () -> products.findForUpdateById(3L).orElseThrow();
        Runnable read = () -> products.findForShareById(3L).orElseThrow();
        contend("WRITE held, WRITE requested", write, 1500, () -> products.findForUpdateById(3L).map(Product::getStock));
        contend("WRITE held, READ requested", write, 1500, () -> products.findForShareById(3L).map(Product::getStock));
        contend("WRITE held, plain read", write, 1500, () -> products.findById(3L).map(Product::getStock));
        contend("READ held, READ requested", read, 1500, () -> products.findForShareById(3L).map(Product::getStock));
        contend("READ held, WRITE requested", read, 1500, () -> products.findForUpdateById(3L).map(Product::getStock));
        contend("WRITE held, NOWAIT", write, 1500, () -> products.findForUpdateNoWaitById(3L).map(Product::getStock));
        contend("WRITE held, timeout 2000", write, 3000, () -> products.findForUpdateWithTimeoutById(3L).map(Product::getStock));
        contend("WRITE held, SKIP LOCKED", write, 1500, () -> products.findForUpdateSkipLockedById(3L).map(Product::getStock));
    }

            tx.executeWithoutResult(s -> {
                Product product = products.findForShareById(3L).orElseThrow();
                await(bothShared);
                product.decreaseStock(1);
                products.flush();
            });

shared round  1: committed after 1025 ms, CannotAcquireLockException after 1023 ms, stock 49
shared round  2: committed after 1011 ms, CannotAcquireLockException after 1011 ms, stock 49
shared round  3: committed after 1004 ms, CannotAcquireLockException after 1004 ms, stock 49
PESSIMISTIC_READ then update: rounds with a failure 10 of 10

select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=? for no key update of p1_0 nowait
HHH000247: ErrorCode: 0, SQLState: 55P03
ERROR: could not obtain lock on row in relation "products"
  chain: org.springframework.dao.CannotAcquireLockException: JDBC exception executing SQL [ERROR: could not obtain lock on row in relation "products"] [select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=? for no key update of p1_0 nowait]; SQL [select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=? for no key update of p1_0 nowait]
  chain: org.hibernate.exception.LockTimeoutException: JDBC exception executing SQL [ERROR: could not obtain lock on row in relation "products"] [select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=? for no key update of p1_0 nowait]
  chain: org.postgresql.util.PSQLException: ERROR: could not obtain lock on row in relation "products"

LOG:  execute <unnamed>: select current_setting('lock_timeout', true)
LOG:  execute <unnamed>: set local lock_timeout = 2000
LOG:  execute <unnamed>: select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=$1 for no key update of p1_0
ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (15,2) in relation "products"
LOG:  execute S_2: ROLLBACK

select current_setting('lock_timeout', true)
set local lock_timeout = 2000
set local lock_timeout = 0
lock_timeout after the hinted query: 0

    @Transactional
    public void reserveWithLock(Long id, int quantity) {
        Product product = products.findForUpdateWithTimeoutById(id).orElseThrow(() -> new ProductNotFoundException(id));
        product.decreaseStock(quantity);
    }

    @PostMapping("/{id}/reservations") 
    @ResponseStatus(HttpStatus.NO_CONTENT) 
    public void reserve(@PathVariable Long id, @Valid @RequestBody ReservationRequest request) { 
        stock.reserveWithLock(id, request.quantity()); 
    } 

    @ExceptionHandler(PessimisticLockingFailureException.class) 
    public ProblemDetail lockNotAcquired(PessimisticLockingFailureException ex) { 
        log.info("Lock not acquired: {}", ex.getMostSpecificCause().getMessage()); 
        return ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT, 
                "The resource is locked by another request. Try again."); 
    } 

docker exec sba-a7-pg psql -U demo -d demo -c "begin" -c "select id, stock from products where id = 3 for update" -c "select pg_sleep(5)" -c "commit"

curl -i -s -H 'Content-Type: application/json' -d '{"quantity":1}' -w 'time %{time_total}s\n' http://localhost:8207/api/products/3/reservations

HTTP/1.1 409
Content-Type: application/problem+json
Transfer-Encoding: chunked
Date: Fri, 18 Sep 2026 05:05:04 GMT
 
{"detail":"The resource is locked by another request. Try again.","instance":"/api/products/3/reservations","status":409,"title":"Conflict"}time 2.037096s

create table jobs (
    id           bigint generated by default as identity primary key,
    payload      varchar(200) not null,
    status       varchar(20)  not null,
    processed_by varchar(40),
    processed_at timestamp with time zone
);
 
create index jobs_status_id_idx on jobs (status, id);
 
create table job_runs (
    id     bigint generated by default as identity primary key,
    job_id bigint      not null references jobs (id),
    worker varchar(40) not null
);

package com.example.demo.job;
 
import java.util.List;
 
import jakarta.persistence.LockModeType;
import jakarta.persistence.QueryHint;
 
import org.springframework.data.domain.Limit;
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.jpa.repository.QueryHints;
 
public interface JobRepository extends JpaRepository<Job, Long> {
 
    @Query("select j from Job j where j.status = :status order by j.id")
    List<Job> findNext(JobStatus status, Limit limit);
 
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select j from Job j where j.status = :status order by j.id")
    List<Job> findNextLocked(JobStatus status, Limit limit);
 
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "-2"))
    @Query("select j from Job j where j.status = :status order by j.id")
    List<Job> findNextSkippingLocked(JobStatus status, Limit limit);
}

select j1_0.id,j1_0.payload,j1_0.processed_at,j1_0.processed_by,j1_0.status from jobs j1_0 where j1_0.status=? order by j1_0.id fetch first ? rows only
select j1_0.id,j1_0.payload,j1_0.processed_at,j1_0.processed_by,j1_0.status from jobs j1_0 where j1_0.status=? order by j1_0.id fetch first ? rows only for no key update of j1_0
select j1_0.id,j1_0.payload,j1_0.processed_at,j1_0.processed_by,j1_0.status from jobs j1_0 where j1_0.status=? order by j1_0.id fetch first ? rows only for no key update of j1_0 skip locked

package com.example.demo.job;
 
import java.util.List;
 
import org.springframework.data.domain.Limit;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
@Service
public class JobProcessor {
 
    private static final Limit BATCH = Limit.of(10);
 
    private final JobRepository jobs;
    private final JdbcClient jdbc;
 
    public JobProcessor(JobRepository jobs, JdbcClient jdbc) {
        this.jobs = jobs;
        this.jdbc = jdbc;
    }
 
    @Transactional
    public int processNextBatch(String worker, ClaimStrategy strategy) {
        List<Job> batch = switch (strategy) {
            case NO_LOCK -> jobs.findNext(JobStatus.NEW, BATCH);
            case LOCK -> jobs.findNextLocked(JobStatus.NEW, BATCH);
            case SKIP_LOCKED -> jobs.findNextSkippingLocked(JobStatus.NEW, BATCH);
        };
        for (Job job : batch) {
            process(job, worker);
            job.complete(worker);
        }
        return batch.size();
    }
 
    private void process(Job job, String worker) {
        // the side effect that must happen once per job: an email, a payment call...
        jdbc.sql("insert into job_runs (job_id, worker) values (?, ?)").params(job.getId(), worker).update();
        try {
            Thread.sleep(2);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

                batches.add(pool.submit(() -> {
                    start.await();
                    int n = 0;
                    long longest = 0;
                    while (true) {
                        long t0 = System.nanoTime();
                        int size = processor.processNextBatch(worker, strategy);
                        longest = Math.max(longest, (System.nanoTime() - t0) / 1_000_000);
                        if (size == 0) {
                            break;
                        }
                        n++;
                    }
                    // print the worker's batches, longest call and the jobs still NEW
                    return n;
                }));

    private String lockTwo(CyclicBarrier firstLocks, long first, long second) {
        return run(() -> tx.executeWithoutResult(s -> {
            products.findForUpdateById(first).orElseThrow();
            await(firstLocks); // both hold their first row before asking for the second
            products.findForUpdateById(second).orElseThrow();
        }));
    }

opposite order round  1: A CannotAcquireLockException after 1064 ms, B committed after 1058 ms
opposite order round  2: A committed after 1010 ms, B CannotAcquireLockException after 1010 ms
opposite order round  3: A CannotAcquireLockException after 1009 ms, B committed after 1011 ms
opposite order: rounds with a failure 10 of 10

HHH000247: ErrorCode: 0, SQLState: 40P01
ERROR: deadlock detected
  Detail: Process 290 waits for ShareLock on transaction 22060; blocked by process 291.
Process 291 waits for ShareLock on transaction 22061; blocked by process 290.
  Hint: See server log for query details.
  Where: while locking tuple (0,34) in relation "products"

same order round  1: A committed after 160 ms, B committed after 272 ms
same order round  2: A committed after 228 ms, B committed after 115 ms
same order: rounds with a failure 0 of 10

    @Modifying
    @Query("update Product p set p.stock = p.stock - :quantity where p.id = :id and p.stock >= :quantity")
    int decreaseStockIfAvailable(Long id, int quantity);

    @Transactional
    public void reserveAtomically(Long id, int quantity) {
        if (products.decreaseStockIfAvailable(id, quantity) == 0) {
            Product product = products.findById(id).orElseThrow(() -> new ProductNotFoundException(id));
            throw new InsufficientStockException(product.getSku(), product.getStock(), quantity);
        }
    }

update products p1_0 set stock=(p1_0.stock-?) where p1_0.id=? and p1_0.stock>=?

update products p1_0 set stock=(p1_0.stock-?) where p1_0.id=? and p1_0.stock>=?
select p1_0.id,p1_0.description,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock,p1_0.version from products p1_0 where p1_0.id=?
threw com.example.demo.product.InsufficientStockException: Only 0 of HUB-07 in stock, 1 requested

>>> plain atomic update: before the sales {stock=50, price=39.00, version=2803}
after 10 sales {stock=40, price=39.00, version=2803}
update products set description=?,name=?,price=?,sku=?,stock=?,version=? where id=? and version=?
editor committed
after the editor {stock=50, price=35.00, version=2804}

update products p1_0 set stock=(p1_0.stock-?),version=(p1_0.version+1) where p1_0.id=? and p1_0.stock>=?
editor threw ObjectOptimisticLockingFailureException
after the editor {stock=40, price=39.00, version=2814}

@Entity
@DynamicUpdate
@Table(name = "products")
public class Product {

after 10 sales {stock=40, price=39.00, version=2815}
update products set price=?,version=? where id=? and version=?
editor committed
after the editor {stock=40, price=35.00, version=2816}

alter table products add constraint products_stock_check check (stock >= 0);

unconditional round 1: {DataIntegrityViolationException=50, ok=50}, {stock=0, price=39.00, version=2814}
unconditional round 2: {DataIntegrityViolationException=50, ok=50}, {stock=0, price=39.00, version=2814}
unconditional round 3: {DataIntegrityViolationException=50, ok=50}, {stock=0, price=39.00, version=2814}

  chain: org.springframework.dao.DataIntegrityViolationException: JDBC exception executing SQL [ERROR: new row for relation "products" violates check constraint "products_stock_check"
  Detail: Failing row contains (3, HUB-07, USB-C hub, Seven ports, 100 W pass-through, 39.00, -1, 2814).] [update products p1_0 set stock=(p1_0.stock-?) where p1_0.id=?]; SQL [update products p1_0 set stock=(p1_0.stock-?) where p1_0.id=?]; constraint [products_stock_check]
  chain: org.hibernate.exception.ConstraintViolationException: JDBC exception executing SQL [ERROR: new row for relation "products" violates check constraint "products_stock_check"
  chain: org.postgresql.util.PSQLException: ERROR: new row for relation "products" violates check constraint "products_stock_check"

    @Test
    void hundredReservationsForFiftyHubs() throws Exception {
        measure("optimistic", () -> stock.reserve(HUB, 1));
        measure("optimistic + retry", () -> reservations.reserve(HUB, 1));
        measure("pessimistic", () -> stock.reserveWithLock(HUB, 1));
        measure("pessimistic, no hint", () -> tx.executeWithoutResult(status ->
                products.findForUpdateById(HUB).orElseThrow().decreaseStock(1)));
        measure("atomic", () -> stock.reserveAtomically(HUB, 1));
 
        measure("optimistic +5ms", () -> withWorkAfter(() -> stock.reserve(HUB, 1)));
        measure("optimistic + retry +5ms", () -> retrying(() -> withWorkAfter(() -> stock.reserve(HUB, 1))));
        measure("pessimistic +5ms", () -> withWorkAfter(() -> stock.reserveWithLock(HUB, 1)));
        measure("atomic +5ms", () -> withWorkAfter(() -> stock.reserveAtomically(HUB, 1)));
        measure("pessimistic 5ms+", () -> withWorkBefore(() -> stock.reserveWithLock(HUB, 1)));
        measure("atomic 5ms+", () -> withWorkBefore(() -> stock.reserveAtomically(HUB, 1)));
    }
 
    // the rest of the order, inside the same transaction, after taking the stock
    private void withWorkAfter(Runnable reserve) {
        tx.executeWithoutResult(status -> {
            reserve.run();
            sleep(5);
        });
    }
 
    private void measure(String name, Runnable task) throws Exception {
        for (int round = 0; round <= 5; round++) { // round 0 warms up
            jdbc.sql("update products set stock = 50 where id = ?").param(HUB).update();
            Race.Outcome outcome = Race.run(100, task);
            // print outcome.counts(), the stock left and outcome.millis(); then best and median of rounds 1-5
        }
    }