Is LockModeType.PESSIMISTIC_WRITE sufficient for an UPSERT in JPA?

Question

I've read this article on JPA concurrency, but either I am too thick or it is not explicit enough.

I am looking to do a database-controlled atomic update-if-found-else-insert operation (an UPSERT).

It looks to my poor slow brain that I can--within a transaction of course--run a named query with a lock mode of PESSIMISTIC_WRITE, see if it returns any results, and then either a persist() or an update() afterwards.

What I am not clear on are the differences between doing this operation with a PESSIMISTIC_WRITE lock vs. a PESSIMISTIC_READ lock. I've read the sentences--I understand that PESSIMISTIC_READ is intended to prevent non-repeatable reads, and PESSIMISTIC_WRITE is...well, maybe I don't understand that one so well :-) --but underneath it's just a SQL SELECT FOR UPDATE, yeah? In both cases?

Answer 1

I am looking to do a database-controlled atomic update-if-found-else-insert operation (an UPSERT).

I'm maybe not answering exactly the whole question but if you want to implement the above without any race condition, you need IMO a table-level LOCK IN EXCLUSIVE MODE (not only rows). I don't know if this can be done with JPA. Maybe you could clarify what would be acceptable for you.

Answer 2

I have faced this kind of situation and found this:

Pessimistic Locking, that means locking of objects on transaction begin and keeping the lock during transaction is done by these 2 PessimisticLockModes: - LockModeType.PESSIMISTIC_READ --> entity can be read by other transactions but no changes can be made - LockModeType.PESSIMISTIC_WRITE --> entity can not be read or written by other transactions

link to the article