db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: RowLocation lifetime
Date Thu, 10 Nov 2005 18:56:26 GMT
>From the store point of view, 3 things can happen to
RowLocations in heap tables:
1) It can be deleted (requires at least TABLE IX, ROW X locking)
       o internal to store it is just marked deleted
       o external to store requests for any operation on
         this row will fail.  Note that your cursor can
         experience this no matter what locking you do, as
         it is always possible for another statement in your
         transaction to do the delete.
2) It can be purged (requires at least TABLE IX, ROW X locking)
       o all physical evidence of the row is removed from table,
         both internal and external operations on this row will
         fail.  Only committed deleted rows are purged.
         Note this will never happen if you have some
         sort of lock on the row as the requested X lock is
         always requested in a system only transaction.
       o the actual RowLocation will not be reused while
         at least some sort of table level intent lock is held.
3) It can be reused (requires a table level X lock)
       o basically as part of a compress all rows can be shuffled
         in any way.  A former RowLocation can now point to
         a completely different row.

So as you point out, your implementation can have serious problems
with cursors held over commit.  This is why in current usage of
cursors over commit the only safe thing to do is to ask for the
next row location and use it.

Please make sure to consider the delete/purge cases also.  One case
that often causes problems is a transaction deleting a row that is
locked by it's own cursor from another statement in the same connection.

As you also discovered, even in read uncommitted Derby still gets
table level intent locks.  This can cause unexpected behavior for users
not doing row level locking, but it seemed a reasonable way to avoid
a lot of ugly problems that can be caused by concurrent DDL with read
uncommitted operations.

Andreas Korneliussen wrote:
> Hi,
>  We are planning on using RowLocation to position the cursor when doing
> scrollable updatable cursors (i.e when navigating backwards in the
> resultset) - see
> http://permalink.gmane.org/gmane.comp.apache.db.derby.devel/10028 for
> more details.
> The fact that the RowLocation could become invalid, do worry me a bit.
> I did a test on a simple select statement, using transaction level
> read-uncommitted.
> T1: select * from testtable
> Then (before doing commit) I called on another connection:
> T2: Got exception:The exception 'SQL Exception: A lock could not be
> obtained within the time requested' was thrown while evaluating an
> expression.
> So even in read-uncommitted mode, a lock intent level lock on the table
> is set (good), and it seems to be held until I close the resultset or
> commit the transaction T1.
> The problem I then see is for cursors that are held over commit
> (ResultSet.HOLD_CURSORS_OVER_COMMIT). Maybe we should not support it for
> scrollable updatable resultsets.
> Anyway , we would really appreciate to get some comments on the
> specification Dag sent out two days ago, to ensure that we are on the
> right track.
> Thanks
> -- Andreas
> Mike Matrigali wrote:
>> Assuming row is not deleted, the question can only be answered
>> knowing the isolation level.  Basically the RowLocation can only
>> be counted on while a lock intent level lock is held on the table.
>> Intent table locks may be released as soon as a statement is
>> completed, or may be held to end of transaction depending on
>> the type of statement and type of isolation level.
>> The thing that may move an existing row in a heap are the compress
>> table system procedures.
>> If a row is deleted then there are other factors.
>> Rick Hillegas wrote:
>>> Hello Store experts,
>>> How long is a RowLocation in a Heap good for? Provided that the row
>>> is not deleted, can you count on its RowLocation reliably identifying
>>> the row for the duration of a Statement, a Transaction, a Connection?
>>> Forever?
>>> Thanks,
>>> -Rick

View raw message