This issue can't be edited
Because it belongs to an archived project. Jira admins can restore projects from the archive.
Support for session.lock in DB2 in Connection.TRANSACTION_READ_COMMITTED isolation level
Description
Environment
Attachments
Activity
CVS Notification ServiceOctober 29, 2004 at 11:19 AM
CVS COMMIT LOG:
SUBJECT: [Hibernate-commits] Hibernate3/src/org/hibernate/dialect DB2Dialect.java,1.8,1.9
Lari HotariOctober 29, 2004 at 10:32 AM
There is also a special SELECT syntax in DB2 which defines locking, it's syntax is "WITH RR USE AND KEEP UPDATE LOCKS". I couldn't get it working with dynamic sql statements. (driver complained about incorrect sql syntax).
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0000879.htm
scroll down to "lock-request-clause".
Lari HotariOctober 29, 2004 at 10:12 AM
Db2 will only keep a "share lock" (read) to the row, if "with rr" is only used. Db2 will make a "update lock" (intent to update) to the row if "for update with rr" is used.
http://www.it.fht-esslingen.de/%7Enonnast/DB2/jkunert/pdf/Concurrency.pdf
(look at pdf page 10)
The problem with plain "for update" is that locks will only be kept for the lifetime of the cursor. In RR isolation levels (and maybe in RS), the lock is kept to the end of the transaction.
(http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0005266.htm )
Explicit locks (write locks) are only caused by updates or deletes and they will be always (in all isolation levels) kept until the transaction ends.
GavinGOctober 29, 2004 at 9:55 AM
Are you sure?? Do you have a link for that?
Lari HotariOctober 29, 2004 at 9:50 AM
In Hibernate3 CVS the syntax should be "for update with rr", not just "with rr". (getForUpdateString() method in DB2Dialect class):
http://cvs.sourceforge.net/viewcvs.py/hibernate/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java?rev=1.8&view=markup
Details
Assignee
GavinGGavinGReporter
Lari HotariLari HotariParticipants
CVS Notification ServiceGavinGLari HotariComponents
Fix versions
Affects versions
Priority
Major
Details
Details
Assignee
GavinGReporter
Lari HotariParticipants
Components
Fix versions
Affects versions
Priority
Major
Currently in Hibernate 2.1.4, DB2 immediately releases row locks in Connection.TRANSACTION_READ_COMMITTED isolation
level when session.lock(object, LockMode.UPGRADE) (SELECT .... FOR UPDATE) is used. I assume that the underlying database cursor is closed when the JDBC ResultSet is closed and that releases the row level lock.
The isolation level must be at least Connection.TRANSACTION_REPEATABLE_READ for session.lock to work
properly.
Hibernate could be changed to support session.lock for Connection.TRANSACTION_READ_COMMITTED level.
The ResultSet which executes the SELECT ... FOR UPDATE statement should not be closed before the Hibernate session is closed.
The net.sf.hibernate.engine.Batcher interface should be changed so that it has a method which makes it possible to create a statement which is closed at the end of the session.
Db2 JDBC Isolation Level mapping:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0010326.htm
"
JDBC Transaction Isolation Levels DB2 Isolation Levels
Connection.TRANSACTION_READ_UNCOMMITED -> Uncommitted read (UR)
Connection.TRANSACTION_READ_COMMITTED -> Cursor stability (CS)
Connection.TRANSACTION_REPEATABLE_READ -> Read stability (RS)
Connection.TRANSACTION_SERIALIZABLE -> Repeatable read (RR)
Connection.TRANSACTION_NONE is not supported on DB2 Universal Database
"
Locks and concurrency control in DB2:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0005266.htm
"
The duration of row locking varies with the isolation level being used:
UR scans: No row locks are held unless row data is changing.
CS scans: Row locks are only held while the cursor is positioned on the row.
RS scans: Only qualifying row locks are held for the duration of the transaction.
RR scans: All row locks are held for the duration of the transaction.
"