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