aspose file tools*
The moose likes Object Relational Mapping and the fly likes Select FOR UPDATE native query VS. entityManager.lock()? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Select FOR UPDATE native query VS. entityManager.lock()?" Watch "Select FOR UPDATE native query VS. entityManager.lock()?" New topic
Author

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

Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
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

Joined: Oct 01, 2007
Posts: 553
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


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102

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
 
subject: Select FOR UPDATE native query VS. entityManager.lock()?