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

[Milosz Tylenda (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1512?page=comments#action_25643 ] 

Milosz Tylenda commented on HHH-1512:
-------------------------------------

I run the tests using current SVN snapshot and DB2 9.1. Actually JPALockTest hangs up in
both testLockModeTypeRead and testLockModeTypeWrite methods when run on DB2 - no matter
which of 1-7 statements we choose. Even if we remove all for update/read only/with stuff
from SELECTs, the test still hangs.

This is because DB2 locks rows "aggressively" - after transaction 1 have updated a row,
transaction 2 tries to select this row and hangs until transaction 1 commits/rolls back. That is
the case even if transactions use Cursor Stability (=JDBC's Read Committed) isolation
level.

I opened two command line clients, switched auto-commit off and checked which phenomena
statements 1-7 prevent. The results are as follows:

1) prevents dirty reads, concurrent writes, concurrent reads
2) prevents dirty reads, concurrent writes, concurrent reads
3) prevents only uncommitted reads 
4) prevents dirty reads, concurrent writes
5) prevents dirty reads, concurrent writes, concurrent reads
6) prevents dirty reads, concurrent writes, concurrent reads
7) prevents dirty reads, concurrent writes, concurrent reads (even if the concurrent
transaction uses select without update/read only/with clause)

It seems that if we want to prevent only dirty reads and concurrent writes, the current
DB2Dialect is OK.

If we also want to prevent concurrent reads, we should use one of 1, 2, 5, 6, 7.
Maybe it can make Hibernate more portable across databases.



> Problem to lock a row in a DB2 database with LockMode UPGRADE
> -------------------------------------------------------------
>
>          Key: HHH-1512
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1512
>      Project: Hibernate3
>         Type: Bug

>   Components: core
>     Versions: 3.1
>  Environment: DB2 v8.2, Hibernate 3.1 and higher
>     Reporter: Peter Potthoff
>     Assignee: Steve Ebersole
>  Attachments: locks.txt
>
> Original Estimate: 1 hour
>         Remaining: 1 hour
>
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira