Hi there,

Thanks for this great quesion.

In the context of JPA and Hibernate, the PESSIMISTIC_WRITE lock mode is indeed a pessimistic lock and is usually intended to prevent other processes from both reading and writing simultaneously. I got it confusion because FOR NO KEY UPDATE is a PSQL clause.

PESSIMISTIC_WRITE (JPA):

Primarily used in JPA to acquire a pessimistic write lock on an entity during a SELECT operation. Prevents other transactions from acquiring any lock (read or write) on the same entity until the lock is released. It is designed to ensure data integrity by allowing only one transaction at a time to read or write to the entity.

Shared Locks (FOR NO KEY UPDATE in PostgreSQL):

In the PostgreSQL-specific FOR NO KEY UPDATE clause, it is possible to acquire shared locks for reading on the selected rows, allowing concurrent reads from other transactions without preventing them from acquiring exclusive locks for update or delete operations. I used PSQL in this project. It is a spesific thing in PSQL.

Some database systems implement multi-version concurrency control that allows readers to fetch data that has been already blocked. [quoted]

https://www.baeldung.com/jpa-pessimistic-locking

Other sources:

https://stackoverflow.com/questions/59712313/what-is-the-practical-use-of-for-key-share-in-postgres

https://www.postgresdba.com/bbs/board.php?bo_table=C05&wr_id=186

https://www.migops.com/blog/select-for-update-and-its-behavior-with-foreign-keys-in-postgresql/