[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1512) Problem to lock a row in a DB2 database with LockMode UPGRADE

[Steve Ebersole (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-1512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45711#comment-45711 ] 

Steve Ebersole commented on HHH-1512:
-------------------------------------

Strong, as I said here 5 years ago, I think the correct solution is to alter 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 said, 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 dialects, 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.db2z10.doc.sqlref/src/tpc/db2z_sql_isolationclause.htm
* http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.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 absolutely correct.  That second link describes the "lock modes".  We need to choose between:

* {{use and keep update locks}} Will allow other transactions to read the locked row(s) but only with shared lock mode (aka only if the other transaction does not request pessimistic lock)
* {{use and keep exclusive locks}} Will not allow other transactions to read 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 use 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 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: HHH-378 and http://forum.hibernate.org/viewtopic.php?t=954639&highlight=db2+lock+upgrade

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira