• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Select FOR UPDATE native query VS. entityManager.lock()?

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to lock a row in the DB and O am wondering which is better.

Option 1.
Lock the row using a select FOR UPDATE native query.

Option 2.
use the entity manager to get the entity -> o = em.find(..)
lock the entity - > em.lock(o, READ)

With option 1 I am not really portable since I assume that select FOR UPDATE does not work on all DB's.
But at least I am locking with one statement.

With option 2 I am portable but since I need to select first to get the entity and then lock, there is a chance that between those two statements another thread could have obtained the lock.

Anyone have an opinion on which I should use???

Thanks
 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The two are quite different as one is pessimistic locking, and the other is optimistic locking.

For the lock(READ), there is no hole between the find() and the lock(), the lock is an optimistic lock, and does not do anything until the end of the transaction, when it will check and lock the version number of the row with the version of the object from the persistence context (which is from the find(), or even before if it were read earlier, or even merged from a different transaction).

Note, that if you are updating the object, you don't need to call lock(READ) at all, the version is always checked on update when optimistic locking is used.

The advantages of optimistic locking is that it is does not hold locks on the database until commit, allows concurrent reads, and allows a lock across transaction boundaries (if the version is merged).

The "Select for Update" is pessimistic locking, and the syntax is database specific, but most databases have some syntax. JPA 2.0 also support pessimistic locking at the JPA API level. The advantages of pessimistic locking is that once the lock is obtained, the transaction is less likely to fail from lock contention.

See,
http://en.wikibooks.org/wiki/Java_Persistence/Locking
 
Theodore David Williams
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


For the lock(READ), there is no hole between the find() and the lock(), the lock is an optimistic lock, and does not do anything until the end of the transaction, when it will check and lock the version number of the row with the version of the object from the persistence context (which is from the find(), or even before if it were read earlier, or even merged from a different transaction).



Does this throw an exception?? Can I catch it? I have just used annotations (more specifically spring @Transactional annotation) so I really have no try/catch block.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic