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.