8

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?

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Laird Nelson
  • 15,321
  • 19
  • 73
  • 127
  • Hi Laird. Did you had any opportunity to work on this? Any interesting finding? – Pascal Thivent Oct 06 '10 at 01:49
  • I haven't had the time to dig in and see what's actually going on. I put in a PESSIMISTIC_WRITE lock because the concurrency article seemed to be trying to insinuate that this would in fact allow me to do a race-condition-less UPSERT. Tangent: an UPSERT seems like such an unbelievably common operation that there MUST be a way to do it in JPA. – Laird Nelson Oct 08 '10 at 17:23
  • 1
    I have the feeling I miss something here. Why can't you just do a find() + insert() if necessary. I suppose the transaction will guarantee the atomicity? I think the locking type is less important, you could even use optimistic locking + versioning? I suppose duplicate entries are protected using a unique constraint. – Bruno Ranschaert Oct 22 '11 at 17:19

3 Answers3

4

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.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
3

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

Elbek
  • 3,434
  • 6
  • 37
  • 49
0

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

INSERT .. ON DUPLICATE KEY UPDATE does that.

Rick James
  • 135,179
  • 13
  • 127
  • 222