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.
