Problem to lock a row in a DB2 database with LockMode UPGRADE
Description
Using the LockMode UPGRADE to lock a row in the database, this will result in a sql-statement: select ID from <schema>.<table> where ID =? and version =? for read only with rs
This statement produces a shared lock and cannot be used for pessimistic locking because this kind of lock won't stop a concurrent thread from accessing the data.
To take advantage of the possibly improved performance of FETCH operations while guaranteeing that selected data is not modified and preventing some types of deadlocks, you can specify FOR READ ONLY in combination with the optional syntax of USE AND KEEP ... LOCKS on the isolation-clause.
Again, keeping in mind that "selected data is not modified" is specifically in regards to positioned updates.
Steve EbersoleApril 26, 2012 at 8:03 PM
Hey Strong,
Just noticing that these suggestions remove the [FOR READ ONLY] portion, and I wonder why? Was that intentional?
According to the DB2 docs, [FOR READ ONLY] only refers to the resulting cursor. It is meant to indicate that the cursor will not be used in positioned updates (Hibernate never does positioned updates). It should be an optimization in our use cases.
Can you confirm that? For the time being I will leave it.
Steve EbersoleApril 26, 2012 at 4:05 PM
Ok, so we know the proper lock clause now
Strong LiuApril 25, 2012 at 5:05 PM
had a quick chat with my friend, and he also things "with rs use and keep update locks" would be the best option in this case
Using the LockMode UPGRADE to lock a row in the database, this will result in
a sql-statement: select ID from <schema>.<table> where ID =? and version =? for read only with rs
This statement produces a shared lock and cannot be used for pessimistic locking because
this kind of lock won't stop a concurrent thread from accessing the data.
The source of the class DB2Dialect.java was changed from release 1.34 to 1.35:
http://cvs.sourceforge.net/viewcvs.py/hibernate/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java?r1=1.34&r2=1.35
and from 1.33 to 1.34
http://cvs.sourceforge.net/viewcvs.py/hibernate/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java?r1=1.33&r2=1.34
Please take a look at: and http://forum.hibernate.org/viewtopic.php?t=954639&highlight=db2+lock+upgrade