[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1512) Problem to lock a row in a DB2 database with LockMode UPGRADE
Sridhar Ramachandran (JIRA)
noreply at atlassian.com
Fri Feb 29 16:58:33 EST 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29635 ]
Sridhar Ramachandran commented on HHH-1512:
-------------------------------------------
Hi,
I encountered this issue while running a concurrency test on DB2. In this test, two threads execute the following pseudo-code
<Get a new session>
<Open a transaction>
System.out.println("Waiting to lock");
session.load(<some class name>, <some PK>, LockMode.UPGRADE)
System.out.println("Got a lock");
<sleep for 10 seconds>
<do an update on the record>
<Commit the transaction>
<Close the session>
System.out.println("Completed transaction");
If I launch these threads with a 5 second gap, both of them get the lock.
Thread 1 - Waiting to lock
Thread 1 - Got a lock
... after 5 seconds
Thread 2 - Waiting to lock
Thread 2 - Got a lock
... after 5 seconds
Thread 1 - Completed transaction
... after 5 seconds
Thread 2 - Throws Exception
The Exception is a LockAcquisitionException. I have seen two types of exceptions
TYPE 1:
org.hibernate.exception.LockAcquisitionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
...
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
at com.ibm.db2.jcc.b.rc.b(rc.java:3180)
at com.ibm.db2.jcc.a.eb.i(eb.java:227)
at com.ibm.db2.jcc.a.eb.a(eb.java:183)
at com.ibm.db2.jcc.a.eb.a(eb.java:34)
at com.ibm.db2.jcc.a.s.a(s.java:30)
at com.ibm.db2.jcc.a.h.Gb(h.java:325)
at com.ibm.db2.jcc.b.rc.R(rc.java:3022)
at com.ibm.db2.jcc.a.d.f(d.java:1070)
at com.ibm.db2.jcc.b.r.a(r.java:191)
at com.ibm.db2.jcc.b.rc.c(rc.java:302)
at com.ibm.db2.jcc.b.rc.next(rc.java:277)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:135)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 8 more
TYPE 2:
Exception in thread "main" org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
...
Caused by: com.ibm.db2.jcc.b.vd: Non-atomic batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
at com.ibm.db2.jcc.b.i.a(i.java:390)
at com.ibm.db2.jcc.b.ad.b(ad.java:2863)
at com.ibm.db2.jcc.b.ad.a(ad.java:2632)
at com.ibm.db2.jcc.b.ad.executeBatch(ad.java:2444)
at com.ibm.db2.jcc.b.ad.executeBatch(ad.java:1329)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeBatch(DelegatingPreparedStatement.java:205)
at com.oatsystems.util.SimpleStatement.executeBatch(SimpleStatement.java:131)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 18 more
---
I then changed the DB2Dialect to a point to an implementation suggested in http://forum.hibernate.org/viewtopic.php?t=954639&highlight=db2+lock+upgrade:
public class DB2Dialect extends org.hibernate.dialect.DB2Dialect {
public String getForUpdateString() {
return " for update with rr";
}
}
Now, the lock behaves like a pessimistic lock.
Thread 1 - Waiting to lock
Thread 1 - Got a lock
... after 5 seconds
Thread 2 - Waiting to lock
... after 5 seconds
Thread 1 - Got a lock
Thread 2 - Completed transaction
... after 5 seconds
Thread 2 - Completed transaction
The second transaction gets the lock on the row only after the first transaction is committed.
In SQL Server and Oracle, I think two transactions can not get locks of the LockMode.UPGRADE level at the same time on the same row. It will be great if DB2 also behaves the same way.
I went though the 7 statements mentioned in the previous comments, and my results match those of Milosz:
1) for update with rr (Old version): Pessimistic lock
2) for update with rs: Pessimistic lock
3) for update with cs: No Lock
4) for read only with rs (Current version): Optimistic lock.
5) for read only with rr use and keep update locks: Pessimistic lock
6) for read only with rs use and keep update locks: Pessimistic lock
7) for read only with rr use and keep exclusive locks: Pessimistic lock
Can we change the getForUpdateString() to one of 1, 2, 5, 6 or 7?
Regards,
Sridhar.
> 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
> 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: 1 hour
> Remaining Estimate: 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
More information about the hibernate-issues
mailing list