From noreply at atlassian.com Mon Feb 27 09:12:49 2012 Content-Type: multipart/mixed; boundary="===============8824416475035843246==" MIME-Version: 1.0 From: Steve Ebersole (JIRA) To: hibernate-issues at lists.jboss.org Subject: [hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1512) Problem to lock a row in a DB2 database with LockMode UPGRADE Date: Mon, 27 Feb 2012 08:12:48 -0600 Message-ID: <30091952.1063.1330351968947.JavaMail.j2ee-onjira@hibernate.onjira.com> --===============8824416475035843246== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable [ https://hibernate.onjira.com/browse/HHH-1512?page=3Dcom.atlassian.jir= a.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D45711#co= mment-45711 ] = Steve Ebersole commented on HHH-1512: ------------------------------------- Strong, as I said here 5 years ago, I think the correct solution is to alte= r thew DB2 dialect to use {{for read only with rs use and keep update locks= }} or {{for read only with rr use and keep update locks}}. But as I also s= aid, I am not a DB2 expert and we need help from DB2 users to verify this. = Ideally we'd really just have someone to take ownership of these dialect= s, but we've been asking for that for 5 years as well, so I am not holding = my breath on that. I am using the following resources: * http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z= 10.doc.sqlref/src/tpc/db2z_sql_isolationclause.htm * http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z= 10.doc.perf/src/tpc/db2z_lockmode.htm#db2z_lockmode It sounds like the type of lock to request in the {{use and keep}} portion = is what we need to resolve. Using {{for read only with[rs|rr]}} seems abso= lutely correct. That second link describes the "lock modes". We need to c= hoose between: * {{use and keep update locks}} Will allow other transactions to read the l= ocked row(s) but only with shared lock mode (aka only if the other transact= ion does not request pessimistic lock) * {{use and keep exclusive locks}} Will not allow other transactions to rea= d the locked row(s) at all. So we are back to where we were 5 years ago :) I think we should use {{for= read only with rs use and keep update locks}} or {{for read only with rr u= se and keep update locks}} but need some DB2 users to verify that changing = the dialect that way works as anticipated by description here. > Problem to lock a row in a DB2 database with LockMode UPGRADE > ------------------------------------------------------------- > > Key: HHH-1512 > URL: https://hibernate.onjira.com/browse/HHH-1512 > Project: Hibernate ORM > Issue Type: Bug > Components: core > Affects Versions: 3.1 > Environment: DB2 v8.2, Hibernate 3.1 and higher > Reporter: Peter Potthoff > Assignee: Steve Ebersole > Attachments: locks.txt > > Original Estimate: 1h > Remaining Estimate: 1h > > Using the LockMode UPGRADE to lock a row in the database, this will resul= t in > a sql-statement: select ID from . where ID =3D? and versio= n =3D? 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/hibern= ate/dialect/DB2Dialect.java?r1=3D1.34&r2=3D1.35 > and from 1.33 to 1.34 > http://cvs.sourceforge.net/viewcvs.py/hibernate/Hibernate3/src/org/hibern= ate/dialect/DB2Dialect.java?r1=3D1.33&r2=3D1.34 > Please take a look at: HHH-378 and http://forum.hibernate.org/viewtopic.p= hp?t=3D954639&highlight=3Ddb2+lock+upgrade -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira =20 --===============8824416475035843246==--