db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andreas Korneliussen (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1799) SUR: current row not locked when renavigating to it, in queries with indexes
Date Fri, 01 Sep 2006 08:54:22 GMT
SUR: current row not locked when renavigating to it, in queries with indexes
----------------------------------------------------------------------------

                 Key: DERBY-1799
                 URL: http://issues.apache.org/jira/browse/DERBY-1799
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.2.1.0, 10.2.2.0, 10.3.0.0
            Reporter: Andreas Korneliussen


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.



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message