db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Question on SUR and U lock.
Date Wed, 23 Aug 2006 17:40:16 GMT
I was doing some testing for SUR and had a question about the expected 
behavior.

The SUR related doc on 
http://db.apache.org/derby/docs/dev/devguide/rdevconceptssur.html  says
"The row which the cursor is positioned on is locked, however once it 
moves to another row,
the lock may be released depending on transaction isolation level."

In my test (isolation level is default - RC),   I have a SUR, resultset 
has 1000 rows,  all rows are materialized by calling absolute(-1). After 
this the cursor is positioned before the first row by calling 
beforeFirst().  Printing the locktable shows
1)a IX lock on the table,  which is fine.
2)U row lock on the table. Why do we hold the U lock when the cursor is 
not positioned on any row ?.
If the U lock is expected, it maybe be good to update the doc for SUR to 
mention this case.

This U lock is released as soon as I move to the first row by calling 
next and a  U lock on the current row is obtained which is fine.  

Number of rows1000
PositionRow absolute(-1)
should have materialized all rows in sur2
call beforeFirst()
     XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
     --- ---- ---- --------- -------- ----- --------- --------- ---------
    {8069,TABLE,IX,BLOBTBL,Tablelock,GRANT,T,1,null}
    {8069,TABLE,S,*** TRANSIENT_1156352627035,Tablelock,GRANT,T,1,null}
    {8069,TABLE,X,*** TRANSIENT_1156352627035,Tablelock,GRANT,T,1,null}
    {8069,ROW,U,BLOBTBL,(2,298),GRANT,T,1,null}

Code snippet:
      // get another SUR.
        PreparedStatement p2 = conn.prepareStatement("select  
ID,POS,DLEN,CONTENT from BLOBTBL",ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
       
        ResultSet sur2  = p2.executeQuery();
        pos(sur2,-1);//should materialize all rows
        System.out.println("should have materialized all rows in sur2");
        System.out.println("call beforeFirst()");
        sur2.beforeFirst();
        locktbl();

---------------

Thanks,
Sunitha.


Mime
View raw message