FOR UPDATE requests database-level locking for rows loaded by a query. In JPA, Hibernate emits it for pessimistic write locking, allowing concurrent access to be serialized within the transaction. section 11.2.3
Account for these operational properties:
- PESSIMISTIC_WRITE provides repeatable reads, serializes data access, and prevents phantom reads where the provider and database support those guarantees. section 11.2.3
- Lock duration is one database transaction; failure to obtain the lock raises an exception rather than deferring conflict detection until commit. section 11.2.3
- SQL varies by dialect: H2 uses FOR UPDATE, PostgreSQL uses FOR UPDATE for writes and FOR SHARE for reads, and MySQL uses FOR UPDATE for writes. section 11.2.3
JPA pessimistic write
Begin a transaction, load an entity with PESSIMISTIC_WRITE, change it, and commit. A supported dialect can execute SELECT ... FOR UPDATE WAIT 5000 when a lock timeout is configured. section 11.2.3
Listing 11.6 Locking data pessimistically
Path: /Ch11/transactions/src/test/java/com/manning/javapersistence/ch11 /concurrency/Locking.java – pessimisticReadWrite() EntityManager em = emf.createEntityManager(); em.getTransaction().begin(); BigDecimal totalPrice = BigDecimal.ZERO; for (Long categoryId : CATEGORIES) { List<Item> items = #1 em.createQuery("select i from Item i where i.category.id = :catId", Item.class) .setLockMode(LockModeType.PESSIMISTIC_READ) .setHint("javax.persistence.lock.timeout", 5000) .setParameter("catId", categoryId) .getResultList(); for (Item item : items) #2 totalPrice = totalPrice.add(item.getBuyNowPrice()); // . . . Another transaction tries to obtain a lock and fails } em.getTransaction().commit(); #3 em.close(); assertEquals(0, totalPrice.compareTo(new BigDecimal("108"))); #1 For each <kbd>Category</kbd>, query all <kbd>Item</kbd> instances in <kbd>PESSIMISTIC_READ</kbd> lock mode. Hibernate locks the rows in the database with the SQL query. If another transaction holds a conflicting lock, wait 5 seconds if possible. If the lock can’t be obtained, the query throws an exception. #2 If the query returns successfully, we know that we hold an exclusive lock on the data, and no other transaction can access it with an exclusive lock or modify it until this transaction commits. #3 The locks are released after committing, when the transaction completes.
Shows a complete EntityManager query using PESSIMISTIC_READ, a five-second timeout hint, and transaction-scoped locking. section 11.2.3
