Hi,
I am wondering if this might be a candidate for improvement. Here
is the case which shows what's going on (see also repro enclosed):
Transaction 1 has an update lock on a row. Transaction 2 opens a
scrollable (read-only) result set which has an underlying "FOR UPDATE"
query, so update locks will be used for this transaction, too. Next,
we position that result set of trans 2 to a row *past* the locked row
of trans 1, using ResultSet#absolute. This is the first use of this
result set, so each qualifying row we move past will be read (and momentarily
update locked!), then inserted into the hash table for later scrolling.
In the repro, in the positioning, trans 2 locks on trans 1's update
lock for the row we move past.
It would seem that only the destination row indicated by the
absolute() requires the lock.
This seems to be a candidate for improvement. The same problem occurs
for ResultSet#relative and ResultSet#last too, most likely.
Once all rows have been read into the cache, later positioning will
not have this problem, so it is a corner case, but I thought
I'd mention it. It will be more likely to occur when scrollable
result sets are made updatable.
Dag
/*
* Main.java
*
* Created on November 3, 2005, 4:32 PM, Dag Wanvik
*
* Scrollable cursor sets unnecessary(?) locks on intervening row when
repositioning
* In the example a row locked for update by con1, leads to another transaction
* being held to wait for a lock when it tries to reposition past that row.
*/
package AbsoluteUsesLockingNext;
import java.sql.*;
public class Main {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
//Statement updateStatement = null;
Statement s1 = null;
Statement s2 = null;
Statement ddlStatement = null;
Connection con = null;
Connection con2 = null;
ResultSet rs = null;
ResultSet rs2 = null;
try {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
con =
DriverManager.getConnection("jdbc:derby:testdb;create=true;territory=en_US");
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
con2 = DriverManager.getConnection("jdbc:derby:testdb");
con2.setAutoCommit(false);
con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);// Create
table
ddlStatement = con.createStatement();
ddlStatement.execute("CREATE TABLE myTable (id int primary key, name
varchar(50))");
ddlStatement.execute(
"CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '5')");
}
catch (Exception e) {
System.out.println(e);
return;
}
try {
// Insert data
//
PreparedStatement ps = con.prepareStatement("INSERT INTO myTable
VALUES (?, ?)");
for (int i=1; i<=10; i++) {
ps.setInt(1, i);
ps.setString(2, "Testing " + i);
ps.executeUpdate();
}
ps.close();
con.commit();
// Get ResultSet
//
s1 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = s1.executeQuery("select * from myTable for update");
s2 = con2.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs2 = s2.executeQuery("select * from myTable for update");
for (int i=0;i<5;i++)
rs.next();
rs.previous();
System.out.println("RS : Current value " + rs.getInt(1));
int i = 0;
System.out.println("RS2 : trying absolute 10");
rs2.absolute(10);
System.out.println("RS2 : Current value " + rs2.getInt(1));
rs.close();
rs2.close();
} catch (SQLException e) {
String s = e.getSQLState();
System.out.println(e.toString() + " state: " + s + " : code=" +
e.getErrorCode());
} catch (Exception e) {
System.out.println(e.toString());
}
finally {
try {
con.commit();
con2.commit();
s1.close();
s2.close();
if (ddlStatement != null) {
System.out.println("trying to do drop table");
ddlStatement.execute("DROP TABLE myTable");
System.out.println("trying to do close ddlstmt");
ddlStatement.close();
}
System.out.println("trying to close con1");
con.commit();
con.close();
System.out.println("trying to close con2");
con2.commit();
con2.close();
} catch (Exception e) {
System.out.println(e.toString());
}
}
}
}
--
Dag H. Wanvik
Sun Microsystems, Web Services, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax: +47 73842101
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact
the sender by reply email and destroy all copies of the original
message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
