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: conflict detection strategies
Date Thu, 16 Feb 2006 18:10:07 GMT
I posted some questions about how the delete/update is done, those
answers would help me understand better what is needed for a solution.

I am going to start a separate thread concentrating on RowLocation
guarantees from store.

Some other answers below.

Andreas Korneliussen wrote:

> Mike Matrigali wrote:
>> There are very few cross thread dependencies not managed by locks
>> currently.  These things add extra complications to current and
>> future code.  Also I want to understand clearly the new restrictions
>> be imposted on the access methods (both current and possible
>> future).  In the future we would like to do more automatic space
>> reclamation as part of the zero-admin goal, the ability to do this
>> in the future internal to access methods is probably affected by
>> the proposals here.
> I think the complexities are there already. It seems very hard to know
> under exactly which conditions, a RowLocation remains valid.  We have
> assumed that the RowLocation will remain valid as long as we hold a
> table intent lock (valid means that it either points to the same row or
> to a deleted row).
> That is what we concluded from the previous discusssion about validity
> of RowLocation. If you in the future need to make code, or there already
> is code, which breaks this assumption, we would need to know which other
> mechanisms we should use to either verify that the RowLocation is valid,
> or to block the system to make it invalid.
There is already code that breaks this assumption, the in place compress
table.  It currently is only executed as a call to a system procedure:

But the hope was in the future to do the same kind of work that this
procedure does, internally in background.

As you have already determined that off-line compress is even more of a
problem, but it does run under an exclusive table lock.  After it runs
the container you were connected to does not even exist any more.

> Locks can be used to manage cross thread dependencies, however they are
> bound to the transaction, and therefore does not help very much for
> cursors held across commits. So if the only mechanism we can have to
> ensure that the RowLocations are valid, is by the means of locks, I
> doubt we can support the feature of scrollable insensitive *holdable*
> updatable resultset.

I agree, I don't believe derby is currently architected to correctly
implement "holdable" SUR.  I don't think the outside event driven
approach is the right way to go.
>> It is true that the current access methods don't reuse row locations
>> until a table level lock is granted.  But your project would be the
>> first dependency on this outside of the access method implementations
>> themselves.  It is very clear the contract that the access methods
>> have with their clients while locks are held on the data they are
>> looking at, what you are proposing is a contract on unlocked data.
> I guess we are the first to use RowLocation without holding a lock on
> the row. This is necessary, unless we simply make SUR cursors set locks
> for all rows in the cursor independent from isolation level.
>> Note that the current "in-place" compress will MOVE rows from one
>> row location to another if one does not have a row lock on the row.
>> This is done in the 2nd phase and only holds an intent lock, and
>> exclusive row locks on the rows being moved.
>> The off-line compress only does work under an X table lock.
>> So the row that you are updating actually will exist in the table,
>> but currently you will request the old location and will get back
>> a delete row indicator.  I think because of this option 1 does not
>> work.
> Are you saing that RowLocations can be invalidated by "in-place"
> compress even if we hold a Table intent lock ?
The "in-place" compress will do the following take a row a row location
N and do the
    insert row at new row location M
    delete row at old row location N
    possibly purge old row location

I don't understand how this will affect your code, it depends how you
search for the row, and what errors are ok to throw.
> How do you call "in-place" compress today ? Does the system use it
> automatically, or do the user have to call it manually ?
>> The state of held cursors across commits is very murky in the standards.
>> We looked very carefully at forward only held cursors, and the standards
>> there are carefully worded to basically not promise anything about the
>> rows that were viewed that preceded the commit (clearly since the
>> standard says the only thing you can do after the commit is a next to
>> get a new row or close - never can access rows looked at before the
>> commit).  What options are legal
>> implementations of updatable scrollable result sets for held cursors
>> across commits?  Do the standards guarantee anything about data in the
>> cursor looked at before the commit?
> I looked at the SQL standard, and for cursors held over commit, it says:
> "If the cursor is insensitive, then significant changes are not visible"
> Andreas
>> Andreas Korneliussen wrote:
>>> Mike Matrigali wrote:
>>> ..
>>>> If possible I would like to see a solution that does not require
>>>> special
>>>> messages sent back and forth between modules about state.
>>> I am not entirely sure what restrictions you want to put on the
>>> design, it is a bit unclear to me.
>>> I have considered some other solutions:
>>> 1. Change the locking behaviour, so that a table intent lock which is
>>> set by an updatable cursor, is kept as long as the cursor is open -
>>> this will ensure that the RowLocations are valid.
>>> 2. After a commit, we could clear all data in the internal table in
>>> the SUR. The problem with this approach is that the resultset would
>>> not necessarily be repopulated with the same data - it would be
>>> sensitive for changes across its own transactions commits, it would
>>> be highly ineffecient.
>>> 3. Let the cursors notify the OnlineCompress module that it should
>>> fail any attempt to compress/defragment or purge the table.
>>> More details on what I suggested yesterday:
>>> The OnlineCompress class could provide an event mechanism, where
>>> subscribers (OnlineCompressListener) register themselves to listen to
>>> OnlineCompressEvents. The ScrollInsensitiveResultSet class could then
>>> implement the OnlineCompressListener interface, and register itself
>>> once it starts populating the table with RowLocations. The
>>> OnlineCompress class then simply notifies all listeners once it is
>>> doing defragment / compress.
>>> The listeners should unregister themselves (i.e
>>> ScrollInsensitiveResultSet class could do it once it closes). The
>>> OnlineCompress class could use a WeakHashMap to put the listeners
>>> into, in case they are not well-behaved. I have not checked if derby
>>> already has event manager type of modules, if it does, I would
>>> attempt to reuse them.
>>> Please also let me know if any of the other alternatives seems better.
>>> Andreas
>>>> Andreas Korneliussen wrote:
>>>>> Some context: In scrollable updatable resultsets, we populate an
>>>>> internal table with the following data:
>>>>> <Position> <RowLocation> <RowUpdated> <RowDeleted>
>>>>> Example layeout:
>>>>>  1         <1,10>         false        false        1,"a",3
>>>>>  2         <1,11>         false        false        2,"b",2
>>>>>  3         <1,12>         false        false        3,"c",9
>>>>> When doing updateRow(), or deleteRow(), we use the RowLocation to
>>>>> navigate to the row being updated.
>>>>> Problem:
>>>>> For holdable cursors, we will release the table intent lock when doing
>>>>> commit on the transaction for the cursor.
>>>>> The table intent lock, prevents the system from doing a compress of
>>>>> the
>>>>> table, causing all RowLocations to be invalid. In addition, it
>>>>> prevents
>>>>> reuse of RowLocation for deleted + purged rows.
>>>>> In order to support holdable scrollable updatable cursors, we consider
>>>>> having a service which allows the system to notify subscribers (i.e
>>>>> cursors) that it has executed i.e a compress.
>>>>> If the user then calls updateRow() or deleteRow(), we can then give an
>>>>> exception like:
>>>>> "The row could not be updated, because its location has been
>>>>> updated by
>>>>> the system"
>>>>> In addition, we consider having a reclaim of locks, so that immediatly
>>>>> after a commit, the new transaction with the holdable cursor, may
>>>>> reclaim the table intent lock.  This will reduce the time period which
>>>>> the system may compress the table, however not completely remove the
>>>>> possibility of a compress.
>>>>> Any comments on implementing such strategy ?
>>>>> An alternative to this strategy, could be to go the other way: cursors
>>>>> notify the system that it should not do compress.
>>>>> I would appreciate feedback on this topic, especially if you find any
>>>>> pitfalls with the proposed strategies, or have better alternatives.
>>>>> Andreas

View raw message