• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Theodore David Williams
Ranch Hand
Posts: 102
  • 0
  • Mark post as helpful
  • send pies
  • 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
 
James Sutherland
Ranch Hand
Posts: 553
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic