Even if the lock is kept between the two statements, this design is in
serious trouble of deadlocking at the database level because of lock
escalation. You typically never want to escalate a lock from a read lock to
a write lock in the same transaction because another trans having a read
lock will prevent you from doing so and result in deadlock. A better
solution is to perform the update first (which guarantees a write lock and
no escalation) and then do the select to see what the value is. Unless
automatic transactions have been turned off (they are on by default) then a
transaction will be invoked to handle the update/select.
Christian Nichols
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of John McNally
Sent: Friday, June 30, 2000 3:05 PM
To: Turbine
Subject: IDBroker and Castor's KeyGenerator
I was looking at the HighLowKeyGenerator in Castor which serves a similar
function to IDBroker. The part of the code that grabs the next base number
and updates the table with the new base number is written like:
rs = stmt.executeQuery();
if ( rs.next() ) {
Object value;
Class valClass;
TypeConvertor back = null;
value = rs.getObject( 1 );
...
if ( back != null ) {
rs.updateObject( 1, back.convert( max, null ) );
} else {
rs.updateBigDecimal( 1, max );
}
rs.updateRow();
last = last.add( ONE );
} else {
stmt = conn.prepareStatement("INSERT INTO " + _seqTable +
" (" + _seqKey + "," + _seqValue +
") VALUES (?, ?)");
stmt.setString( 1, tableName );
stmt.setInt( 2, _grabSize.intValue() );
stmt.executeUpdate();
...
}
This code uses jdbc2.0 api. The documentation states that
" The key generator seeks for the given table name, reads the last reserved
value and increases it by some number N, which is called "grab size". Then
the lock on the auxiliary table is released, so that concurrent transactions
can perform insert to the same table."
I am hoping to get a response from someone who knows the jdbc 2.0 spec
pretty well. Does jdbc2.0 guarantee the table/row is locked between the
stmt.executeQuery() and rs.updateRow() method calls?
And what is the level of compliance among drivers?
John McNally
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
