[DERBY-1799] SUR: current row not locked when renavigating to it, in queries with indexes

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.2.1.6, 10.2.2.0, 10.3.1.4
• Component/s: SQL, Store
• Labels: derby_triage10_5_2 derby_triage10_9
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Andreas Korneliussen
• Created: 01/Sep/06 08:53
• Updated: 21/Feb/12 07:43

Description

This problem is detected in transactions with isolation level read-committed/read-uncommitted.

We have a table (T) which has a primary key (a), and a query which does "select A from T" (an indexed select)

If the result set is scrollable updatable, we expect the current row to be locked with an update lock. This does not seem to happen when repositioning to a row which has been already been fetched previously.

The result is that either the wrong row is locked, or if the result set has been on after last position, no row is locked.

Output from ij:
ij> get scroll insensitive cursor c1 as 'select a from t for update';
ij> next c1;
A
-----------
1
ij> select * from SYSCS_DIAG.LOCK_TABLE;
XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
243 |ROW |U |T |(1,7) |GRANT|T |1 |NULL
243 |ROW |S |T |(1,1) |GRANT|T |1 |SQL060901103455010
243 |TABLE|IX |T |Tablelock |GRANT|T |4 |NULL

3 rows selected

ij> after last c1;
No current row
ij> previous c1;
A
-----------
3
ij> select * from SYSCS_DIAG.LOCK_TABLE;
XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
243 |TABLE|IX |T |Tablelock |GRANT|T |4 |NULL

1 row selected

The last select shows that no row is locked at this point, however we expect one row to be locked.

Issue Links

• relates to: DERBY-1696 transaction may sometimes keep lock on a row after moving off the resultset in scrollable updatable resultset