conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Some context: In scrollable updatable resultsets, we populate an 
internal table with the following data:

<Position> <RowLocation> <RowUpdated> <RowDeleted> [<DataColumn>]+

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

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

I will update the javadoc for the store part with table level intent 
when I get a chance.

As to the autocommit requirement, I will let you propose/implement
that one.  I don't think it is the right way to go, but would not
veto if everyone agreed.  We currently don't have any such requirement
for other statments, and I think it is a hard one to explain to users
and enforce throughout the code.

Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>> What is your definition of a "valid" RowLocation?  Mine is:
>>
>> 1) A non-deleted RowLocation is guaranteed to point at the intended
>>    record until a delete is executed, by a client of the access method.
>>    This part requires no locking, and is the current protocol.
>>
>> 2) A row lock is required to prevent a delete of the row by another
>>    transaction.  There is no way to prevent a delete of the RowLocation
>>    by the same transaction.  This is the current protocol.
>>
>> 3) I think SUR requires that upon committed delete access to the
>>    RowLocation always return some sort of failure, and never access
>>    to a different row.  Truncate currently breaks this, for
>>    conglomerates that guarantee non-reusable RowLocations.  In current
>>    access methods this could be enforced this while holding a
>>    easily if the table level intent
>>    lock requirement is added.
>>    I would be comfortable adding this to store contract.  It
>>    seems reasonable and allows coordination through locking.
>>
> Yes, I agree with you definition, and I would appreciate if you could 
> add the table intent lock requirement to the contract.
> 
>> Note this does not adress other current client usages of the access
>> methods.  But I believe that all those clients could easily agree
>> to the table intent lock protocol.  This would mean that any client
>> that wanted to break this protol must get an exclusive table lock
>> first.  I believe all those clients already do, but for different
>> reasons.
>>
>> Does this solve your non-holdable case?  The holdable case is a 
>> different beast, and should be a different thread.
>>
> Yes, as far as I can see, this solves the non-holdable case.
> 
> And to be entirely safe, would it be possible to add a requirement that 
> truncate/defragment and other compress operations always should run in 
> autocommit mode ?
> 
> I am thinking of the cases were a user could run these in the same 
> transaction as they have an updatable cursor ? If they are in autocommit 
> mode, I think the cursor (non-holdable) will be closed.
> 
> Andreas
> 
> 
> 
> 
> 
>>
>>
>> Andreas Korneliussen wrote:
>>
>>> Mike Matrigali wrote:
>>>
>>>> It looks like I may have been thinking about future directions vs.
>>>> current reality.  The question still is what should the contract
>>>> be, rather than what a specific implementation currently provides.
>>>>
>>>
>>> I agree. I think the contract could be one of these:
>>>
>>> 1. A RowLocation is valid as long as the transaction has a table 
>>> intent lock
>>> 2. A RowLocation is valid as long as the transaction has a row lock 
>>> for the row.
>>>
>>> Both contracts have different tradeoffs, I guess. for store, alt. 2 
>>> gives more flexibility in future online compress operations, however 
>>> I think it would require SURs to set and hold locks for all isolation 
>>> levels.
>>>
>>> JavaDoc for RowLocation do say:
>>>
>>>   See the conglomerate implementation specification for
>>>   information about the conditions under which a row location
>>>   remains valid.
>>>
>>> So currently, it is implementation defined.
>>>
>>>
>>> Andreas
>>>
>>>>
>>>>
>>>> Andreas Korneliussen wrote:
>>>>
>>>>> Mike Matrigali wrote:
>>>>>
>>>>>> 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.
>>>>>>
>>>>> That is great.
>>>>>
>>>>>> 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:
>>>>>> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html 
>>>>>>
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>
>>>>> The online compress seems to require table-exclusive locks when 
>>>>> passing the defragment or truncate_end argument.
>>>>> There are two testcases in jdbcapi/ConcurrencyTest which test this 
>>>>> (testDefragmentDuringScan and testTruncateDuringScan).
>>>>>
>>>>> The tests first deletes all rows except the first and the last. 
>>>>> Then it commits. Then it opens a SUR in read-uncommitted mode. Read 
>>>>> all records into the resultset, and position the cursor to afterlast.
>>>>> Then it runs the defragment or truncate. The test will hang waiting 
>>>>> for a lock. Finally, it updates the rows correctly.
>>>>>
>>>>> Output from the tests (modified the test slightly to get more output):
>>>>> T1: delete records
>>>>> T1: commit
>>>>> T2: Read next Tuple:(0,0,17)
>>>>> T2: Read next Tuple:(9,9,35)
>>>>> T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
>>>>> T3: DEFRAGMENT
>>>>> ERROR 40XL1: A lock could not be obtained within the time requested
>>>>> .... 
>>>>> org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582) 
>>>>>
>>>>>         at 
>>>>> org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605) 
>>>>>
>>>>>         at 
>>>>> org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359) 
>>>>>
>>>>>         at 
>>>>> org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227) 
>>>>>
>>>>>         at 
>>>>> org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858) 
>>>>>
>>>>> ...
>>>>>        at 
>>>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777) 
>>>>>
>>>>>        at 
>>>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712) 
>>>>>
>>>>> ...
>>>>>
>>>>> T3: got expected exception
>>>>> T1: Read first Tuple:(0,0,17)
>>>>> T1: updateInt(2, 3);
>>>>> T1: updateRow()
>>>>> T1: Read last Tuple:(9,9,35)
>>>>> T1: updateInt(2, 3);
>>>>> T1: updateRow()
>>>>> T1: commit
>>>>> T4: select * from table
>>>>> T4: Read next Tuple:(0,3,17)
>>>>> T4: Read next Tuple:(9,3,35)
>>>>>
>>>>> So to me it seems that our assumptions are correct.. Only a purge 
>>>>> is allowed with row-level locking, online compress and online 
>>>>> defragment seems to be blocked by an open cursor on the table.
>>>>> Maybe that is not how online compress was intended to be ?
>>>>>
>>>>> Andreas
>>>>>
>>>>>
>>>>>>
>>>>>>> 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
>>>>>> following:
>>>>>>     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.
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> What is your definition of a "valid" RowLocation?  Mine is:
>
> 1) A non-deleted RowLocation is guaranteed to point at the intended
>    record until a delete is executed, by a client of the access method.
>    This part requires no locking, and is the current protocol.
> 
> 2) A row lock is required to prevent a delete of the row by another
>    transaction.  There is no way to prevent a delete of the RowLocation
>    by the same transaction.  This is the current protocol.
> 
> 3) I think SUR requires that upon committed delete access to the
>    RowLocation always return some sort of failure, and never access
>    to a different row.  Truncate currently breaks this, for
>    conglomerates that guarantee non-reusable RowLocations.  In current
>    access methods this could be enforced this while holding a
>    easily if the table level intent
>    lock requirement is added.
>    I would be comfortable adding this to store contract.  It
>    seems reasonable and allows coordination through locking.
>
Yes, I agree with you definition, and I would appreciate if you could 
add the table intent lock requirement to the contract.

> Note this does not adress other current client usages of the access
> methods.  But I believe that all those clients could easily agree
> to the table intent lock protocol.  This would mean that any client
> that wanted to break this protol must get an exclusive table lock
> first.  I believe all those clients already do, but for different
> reasons.
> 
> Does this solve your non-holdable case?  The holdable case is a 
> different beast, and should be a different thread.
> 
Yes, as far as I can see, this solves the non-holdable case.

And to be entirely safe, would it be possible to add a requirement that 
truncate/defragment and other compress operations always should run in 
autocommit mode ?

I am thinking of the cases were a user could run these in the same 
transaction as they have an updatable cursor ? If they are in autocommit 
mode, I think the cursor (non-holdable) will be closed.

Andreas





> 
> 
> Andreas Korneliussen wrote:
> 
>> Mike Matrigali wrote:
>>
>>> It looks like I may have been thinking about future directions vs.
>>> current reality.  The question still is what should the contract
>>> be, rather than what a specific implementation currently provides.
>>>
>>
>> I agree. I think the contract could be one of these:
>>
>> 1. A RowLocation is valid as long as the transaction has a table 
>> intent lock
>> 2. A RowLocation is valid as long as the transaction has a row lock 
>> for the row.
>>
>> Both contracts have different tradeoffs, I guess. for store, alt. 2 
>> gives more flexibility in future online compress operations, however I 
>> think it would require SURs to set and hold locks for all isolation 
>> levels.
>>
>> JavaDoc for RowLocation do say:
>>
>>   See the conglomerate implementation specification for
>>   information about the conditions under which a row location
>>   remains valid.
>>
>> So currently, it is implementation defined.
>>
>>
>> Andreas
>>
>>>
>>>
>>> Andreas Korneliussen wrote:
>>>
>>>> Mike Matrigali wrote:
>>>>
>>>>> 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.
>>>>>
>>>> That is great.
>>>>
>>>>> 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:
>>>>> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html 
>>>>>
>>>>>
>>>>> 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.
>>>>>
>>>>
>>>> The online compress seems to require table-exclusive locks when 
>>>> passing the defragment or truncate_end argument.
>>>> There are two testcases in jdbcapi/ConcurrencyTest which test this 
>>>> (testDefragmentDuringScan and testTruncateDuringScan).
>>>>
>>>> The tests first deletes all rows except the first and the last. Then 
>>>> it commits. Then it opens a SUR in read-uncommitted mode. Read all 
>>>> records into the resultset, and position the cursor to afterlast.
>>>> Then it runs the defragment or truncate. The test will hang waiting 
>>>> for a lock. Finally, it updates the rows correctly.
>>>>
>>>> Output from the tests (modified the test slightly to get more output):
>>>> T1: delete records
>>>> T1: commit
>>>> T2: Read next Tuple:(0,0,17)
>>>> T2: Read next Tuple:(9,9,35)
>>>> T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
>>>> T3: DEFRAGMENT
>>>> ERROR 40XL1: A lock could not be obtained within the time requested
>>>> .... 
>>>> org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582) 
>>>>
>>>>         at 
>>>> org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605) 
>>>>
>>>>         at 
>>>> org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359) 
>>>>
>>>>         at 
>>>> org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227) 
>>>>
>>>>         at 
>>>> org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858) 
>>>>
>>>> ...
>>>>        at 
>>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777) 
>>>>
>>>>        at 
>>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712) 
>>>>
>>>> ...
>>>>
>>>> T3: got expected exception
>>>> T1: Read first Tuple:(0,0,17)
>>>> T1: updateInt(2, 3);
>>>> T1: updateRow()
>>>> T1: Read last Tuple:(9,9,35)
>>>> T1: updateInt(2, 3);
>>>> T1: updateRow()
>>>> T1: commit
>>>> T4: select * from table
>>>> T4: Read next Tuple:(0,3,17)
>>>> T4: Read next Tuple:(9,3,35)
>>>>
>>>> So to me it seems that our assumptions are correct.. Only a purge is 
>>>> allowed with row-level locking, online compress and online 
>>>> defragment seems to be blocked by an open cursor on the table.
>>>> Maybe that is not how online compress was intended to be ?
>>>>
>>>> Andreas
>>>>
>>>>
>>>>>
>>>>>> 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
>>>>> following:
>>>>>     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.
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

What is your definition of a "valid" RowLocation?  Mine is:

1) A non-deleted RowLocation is guaranteed to point at the intended
    record until a delete is executed, by a client of the access method.
    This part requires no locking, and is the current protocol.

2) A row lock is required to prevent a delete of the row by another
    transaction.  There is no way to prevent a delete of the RowLocation
    by the same transaction.  This is the current protocol.

3) I think SUR requires that upon committed delete access to the
    RowLocation always return some sort of failure, and never access
    to a different row.  Truncate currently breaks this, for
    conglomerates that guarantee non-reusable RowLocations.  In current
    access methods this could be enforced this while holding a
    easily if the table level intent
    lock requirement is added.
    I would be comfortable adding this to store contract.  It
    seems reasonable and allows coordination through locking.

Note this does not adress other current client usages of the access
methods.  But I believe that all those clients could easily agree
to the table intent lock protocol.  This would mean that any client
that wanted to break this protol must get an exclusive table lock
first.  I believe all those clients already do, but for different
reasons.

Does this solve your non-holdable case?  The holdable case is a 
different beast, and should be a different thread.



Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>> It looks like I may have been thinking about future directions vs.
>> current reality.  The question still is what should the contract
>> be, rather than what a specific implementation currently provides.
>>
> 
> I agree. I think the contract could be one of these:
> 
> 1. A RowLocation is valid as long as the transaction has a table intent 
> lock
> 2. A RowLocation is valid as long as the transaction has a row lock for 
> the row.
> 
> Both contracts have different tradeoffs, I guess. for store, alt. 2 
> gives more flexibility in future online compress operations, however I 
> think it would require SURs to set and hold locks for all isolation levels.
> 
> JavaDoc for RowLocation do say:
> 
>   See the conglomerate implementation specification for
>   information about the conditions under which a row location
>   remains valid.
> 
> So currently, it is implementation defined.
> 
> 
> Andreas
> 
>>
>>
>> Andreas Korneliussen wrote:
>>
>>> Mike Matrigali wrote:
>>>
>>>> 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.
>>>>
>>> That is great.
>>>
>>>> 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:
>>>> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html 
>>>>
>>>>
>>>> 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.
>>>>
>>>
>>> The online compress seems to require table-exclusive locks when 
>>> passing the defragment or truncate_end argument.
>>> There are two testcases in jdbcapi/ConcurrencyTest which test this 
>>> (testDefragmentDuringScan and testTruncateDuringScan).
>>>
>>> The tests first deletes all rows except the first and the last. Then 
>>> it commits. Then it opens a SUR in read-uncommitted mode. Read all 
>>> records into the resultset, and position the cursor to afterlast.
>>> Then it runs the defragment or truncate. The test will hang waiting 
>>> for a lock. Finally, it updates the rows correctly.
>>>
>>> Output from the tests (modified the test slightly to get more output):
>>> T1: delete records
>>> T1: commit
>>> T2: Read next Tuple:(0,0,17)
>>> T2: Read next Tuple:(9,9,35)
>>> T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
>>> T3: DEFRAGMENT
>>> ERROR 40XL1: A lock could not be obtained within the time requested
>>> .... 
>>> org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582) 
>>>
>>>         at 
>>> org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605) 
>>>
>>>         at 
>>> org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359) 
>>>
>>>         at 
>>> org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227) 
>>>
>>>         at 
>>> org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858) 
>>>
>>> ...
>>>        at 
>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777) 
>>>
>>>        at 
>>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712) 
>>>
>>> ...
>>>
>>> T3: got expected exception
>>> T1: Read first Tuple:(0,0,17)
>>> T1: updateInt(2, 3);
>>> T1: updateRow()
>>> T1: Read last Tuple:(9,9,35)
>>> T1: updateInt(2, 3);
>>> T1: updateRow()
>>> T1: commit
>>> T4: select * from table
>>> T4: Read next Tuple:(0,3,17)
>>> T4: Read next Tuple:(9,3,35)
>>>
>>> So to me it seems that our assumptions are correct.. Only a purge is 
>>> allowed with row-level locking, online compress and online defragment 
>>> seems to be blocked by an open cursor on the table.
>>> Maybe that is not how online compress was intended to be ?
>>>
>>> Andreas
>>>
>>>
>>>>
>>>>> 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
>>>> following:
>>>>     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.
>>>>
>>>
>>>
>>>
>>>
>>
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> It looks like I may have been thinking about future directions vs.
> current reality.  The question still is what should the contract
> be, rather than what a specific implementation currently provides.
> 

I agree. I think the contract could be one of these:

1. A RowLocation is valid as long as the transaction has a table intent lock
2. A RowLocation is valid as long as the transaction has a row lock for 
the row.

Both contracts have different tradeoffs, I guess. for store, alt. 2 
gives more flexibility in future online compress operations, however I 
think it would require SURs to set and hold locks for all isolation levels.

JavaDoc for RowLocation do say:

   See the conglomerate implementation specification for
   information about the conditions under which a row location
   remains valid.

So currently, it is implementation defined.


Andreas

> 
> 
> Andreas Korneliussen wrote:
> 
>> Mike Matrigali wrote:
>>
>>> 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.
>>>
>> That is great.
>>
>>> 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:
>>> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html 
>>>
>>>
>>> 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.
>>>
>>
>> The online compress seems to require table-exclusive locks when 
>> passing the defragment or truncate_end argument.
>> There are two testcases in jdbcapi/ConcurrencyTest which test this 
>> (testDefragmentDuringScan and testTruncateDuringScan).
>>
>> The tests first deletes all rows except the first and the last. Then 
>> it commits. Then it opens a SUR in read-uncommitted mode. Read all 
>> records into the resultset, and position the cursor to afterlast.
>> Then it runs the defragment or truncate. The test will hang waiting 
>> for a lock. Finally, it updates the rows correctly.
>>
>> Output from the tests (modified the test slightly to get more output):
>> T1: delete records
>> T1: commit
>> T2: Read next Tuple:(0,0,17)
>> T2: Read next Tuple:(9,9,35)
>> T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
>> T3: DEFRAGMENT
>> ERROR 40XL1: A lock could not be obtained within the time requested
>> .... 
>> org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582) 
>>
>>         at 
>> org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605) 
>>
>>         at 
>> org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359) 
>>
>>         at 
>> org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227) 
>>
>>         at 
>> org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858) 
>>
>> ...
>>        at 
>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777) 
>>
>>        at 
>> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712) 
>>
>> ...
>>
>> T3: got expected exception
>> T1: Read first Tuple:(0,0,17)
>> T1: updateInt(2, 3);
>> T1: updateRow()
>> T1: Read last Tuple:(9,9,35)
>> T1: updateInt(2, 3);
>> T1: updateRow()
>> T1: commit
>> T4: select * from table
>> T4: Read next Tuple:(0,3,17)
>> T4: Read next Tuple:(9,3,35)
>>
>> So to me it seems that our assumptions are correct.. Only a purge is 
>> allowed with row-level locking, online compress and online defragment 
>> seems to be blocked by an open cursor on the table.
>> Maybe that is not how online compress was intended to be ?
>>
>> Andreas
>>
>>
>>>
>>>> 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
>>> following:
>>>     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.
>>>
>>
>>
>>
>>
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

It looks like I may have been thinking about future directions vs.
current reality.  The question still is what should the contract
be, rather than what a specific implementation currently provides.



Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>> 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.
>>
> That is great.
> 
>> 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:
>> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html 
>>
>>
>> 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.
>>
> 
> The online compress seems to require table-exclusive locks when passing 
> the defragment or truncate_end argument.
> There are two testcases in jdbcapi/ConcurrencyTest which test this 
> (testDefragmentDuringScan and testTruncateDuringScan).
> 
> The tests first deletes all rows except the first and the last. Then it 
> commits. Then it opens a SUR in read-uncommitted mode. Read all records 
> into the resultset, and position the cursor to afterlast.
> Then it runs the defragment or truncate. The test will hang waiting for 
> a lock. Finally, it updates the rows correctly.
> 
> Output from the tests (modified the test slightly to get more output):
> T1: delete records
> T1: commit
> T2: Read next Tuple:(0,0,17)
> T2: Read next Tuple:(9,9,35)
> T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
> T3: DEFRAGMENT
> ERROR 40XL1: A lock could not be obtained within the time requested
> .... 
> org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582) 
> 
>         at 
> org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605) 
> 
>         at 
> org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359) 
> 
>         at 
> org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227) 
> 
>         at 
> org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858) 
> 
> ...
>        at 
> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777) 
> 
>        at 
> org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712) 
> 
> ...
> 
> T3: got expected exception
> T1: Read first Tuple:(0,0,17)
> T1: updateInt(2, 3);
> T1: updateRow()
> T1: Read last Tuple:(9,9,35)
> T1: updateInt(2, 3);
> T1: updateRow()
> T1: commit
> T4: select * from table
> T4: Read next Tuple:(0,3,17)
> T4: Read next Tuple:(9,3,35)
> 
> So to me it seems that our assumptions are correct.. Only a purge is 
> allowed with row-level locking, online compress and online defragment 
> seems to be blocked by an open cursor on the table.
> Maybe that is not how online compress was intended to be ?
> 
> Andreas
> 
> 
>>
>>> 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
>> following:
>>     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.
>>
> 
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> 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.
> 
That is great.

> 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:
> http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html
>
> 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.
>

The online compress seems to require table-exclusive locks when passing 
the defragment or truncate_end argument.
There are two testcases in jdbcapi/ConcurrencyTest which test this 
(testDefragmentDuringScan and testTruncateDuringScan).

The tests first deletes all rows except the first and the last. Then it 
commits. Then it opens a SUR in read-uncommitted mode. Read all records 
into the resultset, and position the cursor to afterlast.
Then it runs the defragment or truncate. The test will hang waiting for 
a lock. Finally, it updates the rows correctly.

Output from the tests (modified the test slightly to get more output):
T1: delete records
T1: commit
T2: Read next Tuple:(0,0,17)
T2: Read next Tuple:(9,9,35)
T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
T3: DEFRAGMENT
ERROR 40XL1: A lock could not be obtained within the time requested
.... 
org.apache.derby.impl.store.access.RAMTransaction.openScan(RAMTransaction.java:1582)
         at 
org.apache.derby.iapi.db.OnlineCompress.setup_indexes(OnlineCompress.java:605)
         at 
org.apache.derby.iapi.db.OnlineCompress.defragmentRows(OnlineCompress.java:359)
         at 
org.apache.derby.iapi.db.OnlineCompress.compressTable(OnlineCompress.java:227)
         at 
org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABLE(SystemProcedures.java:858)
...
        at 
org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testCompressDuringScan(ConcurrencyTest.java:777)
        at 
org.apache.derbyTesting.functionTests.tests.jdbcapi.ConcurrencyTest.testDefragmentDuringScan(ConcurrencyTest.java:712)
...

T3: got expected exception
T1: Read first Tuple:(0,0,17)
T1: updateInt(2, 3);
T1: updateRow()
T1: Read last Tuple:(9,9,35)
T1: updateInt(2, 3);
T1: updateRow()
T1: commit
T4: select * from table
T4: Read next Tuple:(0,3,17)
T4: Read next Tuple:(9,3,35)

So to me it seems that our assumptions are correct.. Only a purge is 
allowed with row-level locking, online compress and online defragment 
seems to be blocked by an open cursor on the table.
Maybe that is not how online compress was intended to be ?

Andreas


> 
>>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
> following:
>     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.
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

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:
http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html

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
following:
    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> [<DataColumn>]+
>>>>>
>>>>> 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
>>>>>
>>>
>>>
>>>
>>
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

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.

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.

> 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 ?

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> [<DataColumn>]+
>>>>
>>>> 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
>>>>
>>
>>
>>
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

Dag H. Wanvik wrote:

> Hi,
> 
> Mike> It is true that the current access methods don't reuse row locations
> Mike> until a table level lock is granted.  But your project would be the
> Mike> first dependency on this outside of the access method implementations
> Mike> themselves.  It is very clear the contract that the access methods
> Mike> have with their clients while locks are held on the data they are
> Mike> looking at, what you are proposing is a contract on unlocked data.
> 
> So, is the right approach to always lock all rows seen as part of an
> updatable scrollable insensitive result set? Currently, this would
> seem the only way to know whether an update/delete of a row can safely
> be performed when navigating (back) to a previously seen row, since
> non-locked rows are legal prey for compress, and there is no way(?) to
> tell if a row on a page has changed its "identity" since the previous
> read.

Locking the rows definitely works, and follows the current support. I
look forward to your writeup on what kind of locking you do under the
various isolation levels for insensitive SUR.

> 
> Some kind of notification from store before compress starts, as
> proposed by Andreas, would allow invalidation of the result set to
> take place, offering higher concurrency, at the price of a new
> contract between store and the access layer. It would solve
> rowlocation validity problem for both the "in-place" compress (which
> apparently can happen before the transaction commits) and the off-line
> compress (which is an issue when trying to realize holdability for
> insensitive scrollable updatable result sets).

I just think notification is the wrong approach, as the number of pieces
of code in the future that might have to deal with with the notify may
have to increase.  Controlling this seems like it should be in one
place, at the lowest level rather than having the upper levels talk.
Maybe having in place compress move rows under row locking was a bad
idea - it was the most online decision.

I would like to continue discussion of what is needed.  First I want to
understand what is needed in the non-holdable case.  Holdable is a
harder case as we currently drop all locks, and internally in fact close
and reopen the containers.  Dropping locks and doing something
reasonable for an updatable insensitive result set is strange - I am
sure this is why the standard does not even try to define it.
> 
> The standard doesn't really offer much help as to what should and
> should not work here, since it requires updatable result sets to be
> sensitive. For sensitive cursors, one would navigate back and forth
> and there would be no (or less of a) need to remember and (in)validate
> row locations.  But we are building insensitive.. ;-)
> 
> For the non-holdable case, I guess it boils down to a trade-off
> between the desire to avoid locking all result set rows and the desire
> to avoid new contracts between store and access...?
again to comment on this I want to understand if you don't hold locks
on the rows what you allow:
o do you delete/update a row that has changed since you read it into
your set

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

Re: conflict detection strategies

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

Mike> It is true that the current access methods don't reuse row locations
Mike> until a table level lock is granted.  But your project would be the
Mike> first dependency on this outside of the access method implementations
Mike> themselves.  It is very clear the contract that the access methods
Mike> have with their clients while locks are held on the data they are
Mike> looking at, what you are proposing is a contract on unlocked data.

So, is the right approach to always lock all rows seen as part of an
updatable scrollable insensitive result set? Currently, this would
seem the only way to know whether an update/delete of a row can safely
be performed when navigating (back) to a previously seen row, since
non-locked rows are legal prey for compress, and there is no way(?) to
tell if a row on a page has changed its "identity" since the previous
read.

Some kind of notification from store before compress starts, as
proposed by Andreas, would allow invalidation of the result set to
take place, offering higher concurrency, at the price of a new
contract between store and the access layer. It would solve
rowlocation validity problem for both the "in-place" compress (which
apparently can happen before the transaction commits) and the off-line
compress (which is an issue when trying to realize holdability for
insensitive scrollable updatable result sets).

The standard doesn't really offer much help as to what should and
should not work here, since it requires updatable result sets to be
sensitive. For sensitive cursors, one would navigate back and forth
and there would be no (or less of a) need to remember and (in)validate
row locations.  But we are building insensitive.. ;-)

For the non-holdable case, I guess it boils down to a trade-off
between the desire to avoid locking all result set rows and the desire
to avoid new contracts between store and access...?

For the holdable case, I am not sure I see how we can implement that
without some kind of support from store..

Thanks,
Dag


Mike> 
Mike> Note that the current "in-place" compress will MOVE rows from one
Mike> row location to another if one does not have a row lock on the row.
Mike> This is done in the 2nd phase and only holds an intent lock, and
Mike> exclusive row locks on the rows being moved.
Mike> The off-line compress only does work under an X table lock.
Mike> So the row that you are updating actually will exist in the table,
Mike> but currently you will request the old location and will get back
Mike> a delete row indicator.  I think because of this option 1 does not
Mike> work.
Mike> 
Mike> The state of held cursors across commits is very murky in the standards.
Mike> We looked very carefully at forward only held cursors, and the standards
Mike> there are carefully worded to basically not promise anything about the 
Mike> rows that were viewed that preceded the commit (clearly since the 
Mike> standard says the only thing you can do after the commit is a next to 
Mike> get a new row or close - never can access rows looked at before the
Mike> commit).  What options are legal
Mike> implementations of updatable scrollable result sets for held cursors 
Mike> across commits?  Do the standards guarantee anything about data in the
Mike> cursor looked at before the commit?
Mike> 
Mike> 
Mike> 
Mike> Andreas Korneliussen wrote:
Mike> > Mike Matrigali wrote:
Mike> > ..
Mike> > 
Mike> >> If possible I would like to see a solution that does not require special
Mike> >> messages sent back and forth between modules about state.
Mike> >>
Mike> > 
Mike> > I am not entirely sure what restrictions you want to put on the design, 
Mike> > it is a bit unclear to me.
Mike> > 
Mike> > I have considered some other solutions:
Mike> > 
Mike> > 1. Change the locking behaviour, so that a table intent lock which is 
Mike> > set by an updatable cursor, is kept as long as the cursor is open - this 
Mike> > will ensure that the RowLocations are valid.
Mike> > 
Mike> > 2. After a commit, we could clear all data in the internal table in the 
Mike> > SUR. The problem with this approach is that the resultset would not 
Mike> > necessarily be repopulated with the same data - it would be sensitive 
Mike> > for changes across its own transactions commits, it would be highly 
Mike> > ineffecient.
Mike> > 
Mike> > 3. Let the cursors notify the OnlineCompress module that it should fail 
Mike> > any attempt to compress/defragment or purge the table.
Mike> > 
Mike> > More details on what I suggested yesterday:
Mike> > 
Mike> > The OnlineCompress class could provide an event mechanism, where 
Mike> > subscribers (OnlineCompressListener) register themselves to listen to 
Mike> > OnlineCompressEvents. The ScrollInsensitiveResultSet class could then 
Mike> > implement the OnlineCompressListener interface, and register itself once 
Mike> > it starts populating the table with RowLocations. The OnlineCompress 
Mike> > class then simply notifies all listeners once it is doing defragment / 
Mike> > compress.
Mike> > The listeners should unregister themselves (i.e 
Mike> > ScrollInsensitiveResultSet class could do it once it closes). The 
Mike> > OnlineCompress class could use a WeakHashMap to put the listeners into, 
Mike> > in case they are not well-behaved. I have not checked if derby already 
Mike> > has event manager type of modules, if it does, I would attempt to reuse 
Mike> > them.
Mike> > 
Mike> > Please also let me know if any of the other alternatives seems better.
Mike> > 
Mike> > 
Mike> > Andreas
Mike> > 
Mike> > 
Mike> >> Andreas Korneliussen wrote:
Mike> >>
Mike> >>
Mike> >>> Some context: In scrollable updatable resultsets, we populate an
Mike> >>> internal table with the following data:
Mike> >>>
Mike> >>> <Position> <RowLocation> <RowUpdated> <RowDeleted> [<DataColumn>]+
Mike> >>>
Mike> >>> Example layeout:
Mike> >>>
Mike> >>>  1         <1,10>         false        false        1,"a",3
Mike> >>>  2         <1,11>         false        false        2,"b",2
Mike> >>>  3         <1,12>         false        false        3,"c",9
Mike> >>>
Mike> >>>
Mike> >>> When doing updateRow(), or deleteRow(), we use the RowLocation to
Mike> >>> navigate to the row being updated.
Mike> >>>
Mike> >>> Problem:
Mike> >>> For holdable cursors, we will release the table intent lock when doing
Mike> >>> commit on the transaction for the cursor.
Mike> >>>
Mike> >>> The table intent lock, prevents the system from doing a compress of the
Mike> >>> table, causing all RowLocations to be invalid. In addition, it prevents
Mike> >>> reuse of RowLocation for deleted + purged rows.
Mike> >>>
Mike> >>> In order to support holdable scrollable updatable cursors, we consider
Mike> >>> having a service which allows the system to notify subscribers (i.e
Mike> >>> cursors) that it has executed i.e a compress.
Mike> >>>
Mike> >>> If the user then calls updateRow() or deleteRow(), we can then give an
Mike> >>> exception like:
Mike> >>>
Mike> >>> "The row could not be updated, because its location has been updated by
Mike> >>> the system"
Mike> >>>
Mike> >>> In addition, we consider having a reclaim of locks, so that immediatly
Mike> >>> after a commit, the new transaction with the holdable cursor, may
Mike> >>> reclaim the table intent lock.  This will reduce the time period which
Mike> >>> the system may compress the table, however not completely remove the
Mike> >>> possibility of a compress.
Mike> >>>
Mike> >>> Any comments on implementing such strategy ?
Mike> >>>
Mike> >>> An alternative to this strategy, could be to go the other way: cursors
Mike> >>> notify the system that it should not do compress.
Mike> >>>
Mike> >>> I would appreciate feedback on this topic, especially if you find any
Mike> >>> pitfalls with the proposed strategies, or have better alternatives.
Mike> >>>
Mike> >>> Andreas
Mike> >>>
Mike> > 
Mike> > 
Mike> > 
Mike> 
-- 
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

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.

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.

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.

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?



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> [<DataColumn>]+
>>>
>>> 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
>>>
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

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> [<DataColumn>]+
>>
>>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
>>

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

I have not been following the scrollable updatable result set work,
I had assumed that the work would be similar to the other resultset
work in Derby with no special requirements from the store.  Is there
a proposed design for this project that I should go look at?  I looked
at the doc associated with DERBY-690, but there are a lot of suggested
approaches - but not clear which choices have been made.

There is a lot of discussion about using the current support for
update where current of.  In the current system how does the system
translate a user request for an update where current of, to the
actual update of the row.  Does it currently use RowLocation?

If possible I would like to see a solution that does not require special
messages sent back and forth between modules about state.

Andreas Korneliussen wrote:

> Some context: In scrollable updatable resultsets, we populate an
> internal table with the following data:
> 
> <Position> <RowLocation> <RowUpdated> <RowDeleted> [<DataColumn>]+
> 
> 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
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

I have attached the writeup to the JIRA issue (DERBY-690)
--Andreas


Dag H. Wanvik wrote:
> Hi,
> 
> 
>>>>>>"Daniel" == Daniel John Debrunner <dj...@apache.org> wrote:
> 
> 
> Daniel> Was this posted, the more detailed solution?
> Daniel> 
> Daniel> There was a little more detail on a proposed interaction with online
> Daniel> compress, but I think that is based upon a whole lot of design thinking
> Daniel> and assumptions which has not made it to list.
> 
> We will submit a detailed description of the approach with the first
> patch for DERBY-690 (embedded SUR); it should be ready for upload any
> day now. This should hopefully provide enough context for the
> reviewers.
> 
> Thanks,
> Dag
> 
> Daniel> 
> Daniel> I'd assumed you meant you were going to describe your proposed full
> Daniel> solution to SUR, I'm interested to know your approach works with the
> Daniel> various isolation levels, how you handle deleting the rows, how
> Daniel> holdability is supported, etc. And if during working on this you had to
> Daniel> work out how scrollable read-only cursors are implemented, adding that
> Daniel> as background would be excellent. Great knowledge to add to the
> Daniel> community. Don't assume reviewers know how things work today, provide as
> Daniel> much information as you know.

Re: conflict detection strategies

[Daniel John Debrunner <dj...@apache.org>]

Dag H. Wanvik wrote:

> Hi,
> 
> 
>>>>>>"Daniel" == Daniel John Debrunner <dj...@apache.org> wrote:
> 
> 
> Daniel> Was this posted, the more detailed solution?
> Daniel> 
> Daniel> There was a little more detail on a proposed interaction with online
> Daniel> compress, but I think that is based upon a whole lot of design thinking
> Daniel> and assumptions which has not made it to list.
> 
> We will submit a detailed description of the approach with the first
> patch for DERBY-690 (embedded SUR); it should be ready for upload any
> day now. This should hopefully provide enough context for the
> reviewers.

That's great. I would encourage in the future bringing designs and ideas
to the derby-dev list before undergoing a serious coding effort. If the
design is based upon a wrong assumption then it can mean a lot of wasted
effort for everyone. Everyone should treat the derby-dev list and the
folks on it as a great resource and your team members.

I do have concerns with "We will submit a detailed description", this
implies to me that there is a team of folks working on this design
without talking to the list. I guess you have every right to do this,
but it's not realy open source development, and in my eyes does not
build merit in the Apache way.

Thanks,
Dan.

Re: conflict detection strategies

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

>>>>> "Daniel" == Daniel John Debrunner <dj...@apache.org> wrote:

Daniel> Was this posted, the more detailed solution?
Daniel> 
Daniel> There was a little more detail on a proposed interaction with online
Daniel> compress, but I think that is based upon a whole lot of design thinking
Daniel> and assumptions which has not made it to list.

We will submit a detailed description of the approach with the first
patch for DERBY-690 (embedded SUR); it should be ready for upload any
day now. This should hopefully provide enough context for the
reviewers.

Thanks,
Dag

Daniel> 
Daniel> I'd assumed you meant you were going to describe your proposed full
Daniel> solution to SUR, I'm interested to know your approach works with the
Daniel> various isolation levels, how you handle deleting the rows, how
Daniel> holdability is supported, etc. And if during working on this you had to
Daniel> work out how scrollable read-only cursors are implemented, adding that
Daniel> as background would be excellent. Great knowledge to add to the
Daniel> community. Don't assume reviewers know how things work today, provide as
Daniel> much information as you know.

Re: conflict detection strategies

[Daniel John Debrunner <dj...@apache.org>]

Andreas Korneliussen wrote:

> Hi,
> 
> The implementation of SUR  just builds on the existing scrollable
> resultsets, which collects all rows into a table. We have extended this
> table to also contain RowLocation and some metadata.
> This means we do not need to change the store module to navigate
> backward etc - no changes in the store module.
> 
> Updatable cursors in derby  uses RowLocation, however the row is
> guaranteed to be locked (current row has update lock, I think,
> regardless of isolation level).
> As for holdable cursors, forward only updatable cursors require the user
> to navigate to the next row after a commit, thereby getting a new
> rowlocation, on a row which is locked.
> 
> I will propose a more detailed solution tomorrow, so it becomes more
> clear, and less mysterious, what I really propose :-)
> Any other suggestions are of course welcome.


Was this posted, the more detailed solution?

There was a little more detail on a proposed interaction with online
compress, but I think that is based upon a whole lot of design thinking
and assumptions which has not made it to list.

I'd assumed you meant you were going to describe your proposed full
solution to SUR, I'm interested to know your approach works with the
various isolation levels, how you handle deleting the rows, how
holdability is supported, etc. And if during working on this you had to
work out how scrollable read-only cursors are implemented, adding that
as background would be excellent. Great knowledge to add to the
community. Don't assume reviewers know how things work today, provide as
much information as you know.

Thanks,
Dan.

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Hi,

The implementation of SUR  just builds on the existing scrollable 
resultsets, which collects all rows into a table. We have extended this 
table to also contain RowLocation and some metadata.
This means we do not need to change the store module to navigate 
backward etc - no changes in the store module.

Updatable cursors in derby  uses RowLocation, however the row is 
guaranteed to be locked (current row has update lock, I think, 
regardless of isolation level).
As for holdable cursors, forward only updatable cursors require the user 
to navigate to the next row after a commit, thereby getting a new 
rowlocation, on a row which is locked.

I will propose a more detailed solution tomorrow, so it becomes more 
clear, and less mysterious, what I really propose :-)
Any other suggestions are of course welcome.

--Andreas

---------------------
From: Mike Matrigali <mikem_app@... 
<http://gmane.org/get-address.php?address=mikem%5fapp%2drphTv4pjVZMJGwgDXS7ZQA%40public.gmane.org>>
Subject: Re: conflict detection strategies 
<http://news.gmane.org/find-root.php?message_id=%3c43F22581.7090600%40sbcglobal.net%3e>
Newsgroups: gmane.comp.apache.db.derby.devel 
<http://news.gmane.org/gmane.comp.apache.db.derby.devel>
Date: 2006-02-14 18:46:25 GMT (59 minutes ago)

I have not been following the scrollable updatable result set work,
I had assumed that the work would be similar to the other resultset
work in Derby with no special requirements from the store.  Is there
a proposed design for this project that I should go look at?  I looked
at the doc associated with DERBY-690, but there are a lot of suggested
approaches - but not clear which choices have been made.

There is a lot of discussion about using the current support for
update where current of.  In the current system how does the system
translate a user request for an update where current of, to the
actual update of the row.  Does it currently use RowLocation?

If possible I would like to see a solution that does not require special
messages sent back and forth between modules about state.



Andreas Korneliussen wrote:

> Some context: In scrollable updatable resultsets, we populate an 
> internal table with the following data:
>
> <Position> <RowLocation> <RowUpdated> <RowDeleted> [<DataColumn>]+
>
> 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

Re: conflict detection strategies

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

>>>>> "Mike" == Mike Matrigali <mi...@sbcglobal.net> wrote:
Mike> 
Mike> To understand what is going on with these cursor deletes and updates
Mike> in SUR, could you explain what is done to execute each.  For example
Mike> for a delete there could be:
Mike> 1) position on rowlocation and just do delete
Mike> 2) position on rowlocation and check that all other fields match the
Mike>    row being deleted and do the delete
Mike> 3) execute delete from x where (provide matching columns for all columns).

1) is correct. The same goes for update. We do not try to check if
"others" (in the JDBC sense, although we do treat positioned
update/delete using the same cursor as "own", since it is so closely
related to result set operations in Derby) have modified the row.  If
the row has been deleted we generate a warning; positioned
update/delete will (in addition) return zero row count.

Thanks,
Dag

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

To understand what is going on with these cursor deletes and updates
in SUR, could you explain what is done to execute each.  For example
for a delete there could be:
1) position on rowlocation and just do delete
2) position on rowlocation and check that all other fields match the
   row being deleted and do the delete
3) execute delete from x where (provide matching columns for all columns).

There are similar options for update.

Re: conflict detection strategies

[Daniel John Debrunner <dj...@apache.org>]

Andreas Korneliussen wrote:
> 
>>> I think there are other situations where the RowLocation will become
>>> invalid, such as the transaction deleteing the row.
>>>
>>
>> Yes, however as far as I understood, the RowLocation would not be
>> reused as long as at least some sort of table level intent lock is
>> held, and the store will simply return false if one tries to do update
>> / delete / fetch on a RowLocation which is deleted, or deleted+purged.
>>
> 
> To be clear: we do handle the situation were RowLocation points to a
> deleted row by giving a WARNING if the user tries to do updateRow() or
> deleteRow(). For positioned updates, we will give an update count of 0.
> Therefore we do not really think of those RowLocations as invalid.

The positioned update should also generate a SQLWarning

warning — cursor operation conflict.

(from the SQL spec)

Dan.

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

>> I think there are other situations where the RowLocation will become
>> invalid, such as the transaction deleteing the row.
>>
> 
> Yes, however as far as I understood, the RowLocation would not be reused 
> as long as at least some sort of table level intent lock is held, and 
> the store will simply return false if one tries to do update / delete / 
> fetch on a RowLocation which is deleted, or deleted+purged.
>

To be clear: we do handle the situation were RowLocation points to a 
deleted row by giving a WARNING if the user tries to do updateRow() or 
deleteRow(). For positioned updates, we will give an update count of 0. 
Therefore we do not really think of those RowLocations as invalid.


Andreas


> Andreas

Re: conflict detection strategies

[Øystein Grøvlen <Oy...@Sun.COM>]

Mike Matrigali wrote:
> I was not aware of the alter table dependency check for offline
> compress, this must be outside of store somehow  - maybe something
> tied to all alter table statements.  It may make sense
> to change online compress to hook up with whatever offline compress
> is doing to make this happen.
> 
> Just testing the current system does not mean that future changes
> won't break SUR.  Unless we agree to change the contract on unlocked
> RowLocations, then it still isn't right for code to depend on
> an unlocked RowLocation not ever pointing to the wrong row - because
> of the issue with truncate.  

I would prefer if the store would be able to tell that a RowLocation has 
become invalid instead of trying to prevent it from ever pointing to the 
  wrong row.

--
Øystein

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

That is what I was worried about.  The truncate is the one that will
possibly cause reuse of RowLocations.  Defragment moves rows around,
with new rows getting new row locations.

I am still ok with the direction you propose, but some code work is
going to be necessary to get there.  Store could change it's truncate
implementation to do the truncate in a nested update transaction which
would then block against any other locks, even in the calling 
transaction.  The problem is that at that level store can't tell the
difference between locks held on the table by cursors, or locks held
on the table by compress itself.  It is easy to handle this outside
store in the calling compress code, but I think it would be better
if store was internally consistent.  I think what needs to happen
is the compress code has to give up it's lock in the third phase,
before calling compressConglomerate(), and then compressConglomerate()
uses the nested user transaction to implement the truncate.  This
means that changes have to happen to 1st 2 phases also as there is
no way to give up the lock on the user transaction without committing 
it, so those phases also will need to use nested user update transactions.


Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>> ok, I agree now that it is clear this discussion was concentrating
>> on non-holdable case.  Can you verify that the 3rd phase only of
>> in place compress already meets the proposed contract (args 0, 0, 1).
>>
> 
> This is what I can verify:
> 
> Purge:
> ij>  get cursor c1 as 'select * from t1';
> ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 0,0);
> 0 rows inserted/updated/deleted
> ij> rollback;
> 
> As for purge, I have also verified that after a purge, the user 
> transaction holds the row locks for the purged rows.
> 
> 
> Defragment:
> ij>  get cursor c1 as 'select * from t1';
> ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 0, 1,0);
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij> rollback;
> 
> 
> Truncate:
> ij>  get cursor c1 as 'select * from t1';
> ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 0, 0,1);
> 0 rows inserted/updated/deleted
> ij> rollback;
> 
> So, defagment is the only operation which meets the proposed contract.
> 
> Andreas
> 
> 
>> Andreas Korneliussen wrote:
>>
>>> Mike Matrigali wrote:
>>>
>>>>
>>>>
>>>> Andreas Korneliussen wrote:
>>>>
>>>>> Mike Matrigali wrote:
>>>>>
>>>>>> I was not aware of the alter table dependency check for offline
>>>>>> compress, this must be outside of store somehow  - maybe something
>>>>>> tied to all alter table statements.  It may make sense
>>>>>> to change online compress to hook up with whatever offline compress
>>>>>> is doing to make this happen.
>>>>>>
>>>>>> Just testing the current system does not mean that future changes
>>>>>> won't break SUR.  Unless we agree to change the contract on unlocked
>>>>>> RowLocations, then it still isn't right for code to depend on
>>>>>> an unlocked RowLocation not ever pointing to the wrong row - because
>>>>>> of the issue with truncate.   Some possible issues with your test
>>>>>> in the online compress case:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> I think you previously said:
>>>>> "
>>>>>    In current access methods this could be enforced this while 
>>>>> holding a
>>>>>    easily if the table level intent
>>>>>    lock requirement is added.
>>>>>    I would be comfortable adding this to store contract.  It
>>>>>    seems reasonable and allows coordination through locking. "
>>>>>
>>>>> I therefore think it would be good if the  contract said:
>>>>>  * truncate and compress requires exclusive table locking
>>>>>  * the truncate, purge and compress operations do not share any 
>>>>> locks with user transactions
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> This seems fine, but may require changes to store code and inplace 
>>>> compress to actually support such a contract in store.  The previous
>>>> changes just documented what was already supported.
>>>
>>>
>>>
>>>
>>> Note: I am here discussing the non-holdable case only.
>>>
>>> Yes, I guess you are thinking of the part with not sharing any locks 
>>> with user transactions? This comes from the problem of a user running 
>>> online compress from the same connection as the SUR.
>>>
>>> Truncate should be changed to run in a separate transaction, in order 
>>> for the store to be consistent with the proposed contract.
>>>
>>> A minimal requirement from SUR is that defragment does not share any 
>>> locks with the user transaction.  If the rows cannot be defragmented, 
>>> then none of the pages which we have read RowLocation from can be 
>>> truncated. Defragment currently is in line with what we need, since 
>>> it runs in a separate transaction.
>>>
>>> Purge would only affect committed deleted rows (I guess no user 
>>> transaction could lock these ?).
>>>
>>>> I still don't see how this helps the holdable case, I agree this helps
>>>> the non-holdable case.
>>>>
>>>
>>> Yes, I know this only helps the non-holdable case.
>>>
>>> Andreas
>>>
>>>
>>
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> ok, I agree now that it is clear this discussion was concentrating
> on non-holdable case.  Can you verify that the 3rd phase only of
> in place compress already meets the proposed contract (args 0, 0, 1).
> 

This is what I can verify:

Purge:
ij>  get cursor c1 as 'select * from t1';
ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 0,0);
0 rows inserted/updated/deleted
ij> rollback;

As for purge, I have also verified that after a purge, the user 
transaction holds the row locks for the purged rows.


Defragment:
ij>  get cursor c1 as 'select * from t1';
ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 0, 1,0);
ERROR 40XL1: A lock could not be obtained within the time requested
ij> rollback;


Truncate:
ij>  get cursor c1 as 'select * from t1';
ij>   call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 0, 0,1);
0 rows inserted/updated/deleted
ij> rollback;

So, defagment is the only operation which meets the proposed contract.

Andreas


> Andreas Korneliussen wrote:
> 
>> Mike Matrigali wrote:
>>
>>>
>>>
>>> Andreas Korneliussen wrote:
>>>
>>>> Mike Matrigali wrote:
>>>>
>>>>> I was not aware of the alter table dependency check for offline
>>>>> compress, this must be outside of store somehow  - maybe something
>>>>> tied to all alter table statements.  It may make sense
>>>>> to change online compress to hook up with whatever offline compress
>>>>> is doing to make this happen.
>>>>>
>>>>> Just testing the current system does not mean that future changes
>>>>> won't break SUR.  Unless we agree to change the contract on unlocked
>>>>> RowLocations, then it still isn't right for code to depend on
>>>>> an unlocked RowLocation not ever pointing to the wrong row - because
>>>>> of the issue with truncate.   Some possible issues with your test
>>>>> in the online compress case:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> I think you previously said:
>>>> "
>>>>    In current access methods this could be enforced this while 
>>>> holding a
>>>>    easily if the table level intent
>>>>    lock requirement is added.
>>>>    I would be comfortable adding this to store contract.  It
>>>>    seems reasonable and allows coordination through locking. "
>>>>
>>>> I therefore think it would be good if the  contract said:
>>>>  * truncate and compress requires exclusive table locking
>>>>  * the truncate, purge and compress operations do not share any 
>>>> locks with user transactions
>>>
>>>
>>>
>>>
>>> This seems fine, but may require changes to store code and inplace 
>>> compress to actually support such a contract in store.  The previous
>>> changes just documented what was already supported.
>>
>>
>>
>> Note: I am here discussing the non-holdable case only.
>>
>> Yes, I guess you are thinking of the part with not sharing any locks 
>> with user transactions? This comes from the problem of a user running 
>> online compress from the same connection as the SUR.
>>
>> Truncate should be changed to run in a separate transaction, in order 
>> for the store to be consistent with the proposed contract.
>>
>> A minimal requirement from SUR is that defragment does not share any 
>> locks with the user transaction.  If the rows cannot be defragmented, 
>> then none of the pages which we have read RowLocation from can be 
>> truncated. Defragment currently is in line with what we need, since it 
>> runs in a separate transaction.
>>
>> Purge would only affect committed deleted rows (I guess no user 
>> transaction could lock these ?).
>>
>>> I still don't see how this helps the holdable case, I agree this helps
>>> the non-holdable case.
>>>
>>
>> Yes, I know this only helps the non-holdable case.
>>
>> Andreas
>>
>>
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

ok, I agree now that it is clear this discussion was concentrating
on non-holdable case.  Can you verify that the 3rd phase only of
in place compress already meets the proposed contract (args 0, 0, 1).

Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>>
>>
>> Andreas Korneliussen wrote:
>>
>>> Mike Matrigali wrote:
>>>
>>>> I was not aware of the alter table dependency check for offline
>>>> compress, this must be outside of store somehow  - maybe something
>>>> tied to all alter table statements.  It may make sense
>>>> to change online compress to hook up with whatever offline compress
>>>> is doing to make this happen.
>>>>
>>>> Just testing the current system does not mean that future changes
>>>> won't break SUR.  Unless we agree to change the contract on unlocked
>>>> RowLocations, then it still isn't right for code to depend on
>>>> an unlocked RowLocation not ever pointing to the wrong row - because
>>>> of the issue with truncate.   Some possible issues with your test
>>>> in the online compress case:
>>>
>>>
>>>
>>>
>>>
>>> I think you previously said:
>>> "
>>>    In current access methods this could be enforced this while holding a
>>>    easily if the table level intent
>>>    lock requirement is added.
>>>    I would be comfortable adding this to store contract.  It
>>>    seems reasonable and allows coordination through locking. "
>>>
>>> I therefore think it would be good if the  contract said:
>>>  * truncate and compress requires exclusive table locking
>>>  * the truncate, purge and compress operations do not share any locks 
>>> with user transactions
>>
>>
>>
>> This seems fine, but may require changes to store code and inplace 
>> compress to actually support such a contract in store.  The previous
>> changes just documented what was already supported.
> 
> 
> Note: I am here discussing the non-holdable case only.
> 
> Yes, I guess you are thinking of the part with not sharing any locks 
> with user transactions? This comes from the problem of a user running 
> online compress from the same connection as the SUR.
> 
> Truncate should be changed to run in a separate transaction, in order 
> for the store to be consistent with the proposed contract.
> 
> A minimal requirement from SUR is that defragment does not share any 
> locks with the user transaction.  If the rows cannot be defragmented, 
> then none of the pages which we have read RowLocation from can be 
> truncated. Defragment currently is in line with what we need, since it 
> runs in a separate transaction.
> 
> Purge would only affect committed deleted rows (I guess no user 
> transaction could lock these ?).
> 
>> I still don't see how this helps the holdable case, I agree this helps
>> the non-holdable case.
>>
> 
> Yes, I know this only helps the non-holdable case.
> 
> Andreas
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> 
> 
> Andreas Korneliussen wrote:
> 
>> Mike Matrigali wrote:
>>
>>> I was not aware of the alter table dependency check for offline
>>> compress, this must be outside of store somehow  - maybe something
>>> tied to all alter table statements.  It may make sense
>>> to change online compress to hook up with whatever offline compress
>>> is doing to make this happen.
>>>
>>> Just testing the current system does not mean that future changes
>>> won't break SUR.  Unless we agree to change the contract on unlocked
>>> RowLocations, then it still isn't right for code to depend on
>>> an unlocked RowLocation not ever pointing to the wrong row - because
>>> of the issue with truncate.   Some possible issues with your test
>>> in the online compress case:
>>
>>
>>
>>
>> I think you previously said:
>> "
>>    In current access methods this could be enforced this while holding a
>>    easily if the table level intent
>>    lock requirement is added.
>>    I would be comfortable adding this to store contract.  It
>>    seems reasonable and allows coordination through locking. "
>>
>> I therefore think it would be good if the  contract said:
>>  * truncate and compress requires exclusive table locking
>>  * the truncate, purge and compress operations do not share any locks 
>> with user transactions
> 
> 
> This seems fine, but may require changes to store code and inplace 
> compress to actually support such a contract in store.  The previous
> changes just documented what was already supported.

Note: I am here discussing the non-holdable case only.

Yes, I guess you are thinking of the part with not sharing any locks 
with user transactions? This comes from the problem of a user running 
online compress from the same connection as the SUR.

Truncate should be changed to run in a separate transaction, in order 
for the store to be consistent with the proposed contract.

A minimal requirement from SUR is that defragment does not share any 
locks with the user transaction.  If the rows cannot be defragmented, 
then none of the pages which we have read RowLocation from can be 
truncated. Defragment currently is in line with what we need, since it 
runs in a separate transaction.

Purge would only affect committed deleted rows (I guess no user 
transaction could lock these ?).

> I still don't see how this helps the holdable case, I agree this helps
> the non-holdable case.
> 

Yes, I know this only helps the non-holdable case.

Andreas

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

Andreas Korneliussen wrote:
> Mike Matrigali wrote:
> 
>> I was not aware of the alter table dependency check for offline
>> compress, this must be outside of store somehow  - maybe something
>> tied to all alter table statements.  It may make sense
>> to change online compress to hook up with whatever offline compress
>> is doing to make this happen.
>>
>> Just testing the current system does not mean that future changes
>> won't break SUR.  Unless we agree to change the contract on unlocked
>> RowLocations, then it still isn't right for code to depend on
>> an unlocked RowLocation not ever pointing to the wrong row - because
>> of the issue with truncate.   Some possible issues with your test
>> in the online compress case:
> 
> 
> 
> I think you previously said:
> "
>    In current access methods this could be enforced this while holding a
>    easily if the table level intent
>    lock requirement is added.
>    I would be comfortable adding this to store contract.  It
>    seems reasonable and allows coordination through locking. "
> 
> I therefore think it would be good if the  contract said:
>  * truncate and compress requires exclusive table locking
>  * the truncate, purge and compress operations do not share any locks 
> with user transactions

This seems fine, but may require changes to store code and inplace 
compress to actually support such a contract in store.  The previous
changes just documented what was already supported.

I still don't see how this helps the holdable case, I agree this helps
the non-holdable case.

> 
> Are you ok with adding this to the contract ?
> 
>> 1) online compress has 3 separate phases, all of which do different
>>    types of locking.  Some use internal transactions, which explain
>>    the conflict lock.  I would try the following test:
>>    o autocommit off
>>    o hold cursor as your example, with a next
>>    o commit transaction
>>    o execute in place compress now that hold cursor has released all 
>> it's locks.
>>
> 
> This is exactly the problem for the holdable case: truncate. After 
> truncate, a Page can be recreated, and RowLocations may be reused on the 
> new page. This should not be a problem for the non-holdable case, since 
> we will hold the table intent lock.
> 
> Andreas
> 
>> Andreas Korneliussen wrote:
>>
>>> Andreas Korneliussen wrote:
>>>
>>>> Daniel John Debrunner wrote:
>>>>
>>>>> Andreas Korneliussen wrote:
>>>>>
>>>>>
>>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> I think this last paragraph is an incorrect assuption. The table 
>>>>> intent
>>>>> lock prevents other transactions from doing a compress, but not the
>>>>> transaction holding the lock.
>>>>>
>>>>
>>>
>>> It seems to me that that online compress will not use the same 
>>> transaction:
>>>
>>> ij> autocommit off;
>>> ij>  get cursor c1 as 'select * from t1 for update';
>>> ij>  call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','T1', 1, 1, 1);
>>> ERROR 40XL1: A lock could not be obtained within the time requested
>>> ij> rollback;
>>>
>>>
>>> Offline compress is rejected if executed from the same connection:
>>> ij>   get cursor c1 as 'select * from t1 for update';
>>> ij> next c1;
>>> ID
>>> -----------
>>> 1
>>> ij>  call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 0);
>>> ERROR 38000: The exception 'SQL Exception: Operation 'ALTER TABLE' 
>>> cannot be performed on object 'T1' because there is an open ResultSet 
>>> dependent on that object.' was thrown while evaluating an expression.
>>> ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 
>>> 'T1' because there is an open ResultSet dependent on that object.
>>> ij>
>>>
>>> Are there other user-visible mechanisms to start online compress ?
>>>
>>> If not, I think we could conclude that there are no known issues with 
>>> the use of RowLocation in non-holdable SUR (given the discussions 
>>> about validity of RowLocation in separate threads)
>>>
>>> Andreas
>>>
>>>
>>>> That is a good point.
>>>>
>>>> The main problem would be the system doing a compress, however we 
>>>> should take into account the fact that the user can run compress 
>>>> from the same transaction, and then maybe invalidate the resultset, 
>>>> or prevent the compress from running.
>>>>
>>>>> I think there are other situations where the RowLocation will become
>>>>> invalid, such as the transaction deleteing the row.
>>>>>
>>>>
>>>> Yes, however as far as I understood, the RowLocation would not be 
>>>> reused as long as at least some sort of table level intent lock is 
>>>> held, and the store will simply return false if one tries to do 
>>>> update / delete / fetch on a RowLocation which is deleted, or 
>>>> deleted+purged.
>>>>
>>>> Andreas
>>>
>>>
>>>
>>>
>>>
>>>
>>
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Mike Matrigali wrote:
> I was not aware of the alter table dependency check for offline
> compress, this must be outside of store somehow  - maybe something
> tied to all alter table statements.  It may make sense
> to change online compress to hook up with whatever offline compress
> is doing to make this happen.
> 
> Just testing the current system does not mean that future changes
> won't break SUR.  Unless we agree to change the contract on unlocked
> RowLocations, then it still isn't right for code to depend on
> an unlocked RowLocation not ever pointing to the wrong row - because
> of the issue with truncate.   Some possible issues with your test
> in the online compress case:


I think you previously said:
"
    In current access methods this could be enforced this while holding a
    easily if the table level intent
    lock requirement is added.
    I would be comfortable adding this to store contract.  It
    seems reasonable and allows coordination through locking. "

I therefore think it would be good if the  contract said:
  * truncate and compress requires exclusive table locking
  * the truncate, purge and compress operations do not share any locks 
with user transactions

Are you ok with adding this to the contract ?

> 1) online compress has 3 separate phases, all of which do different
>    types of locking.  Some use internal transactions, which explain
>    the conflict lock.  I would try the following test:
>    o autocommit off
>    o hold cursor as your example, with a next
>    o commit transaction
>    o execute in place compress now that hold cursor has released all 
> it's locks.
> 

This is exactly the problem for the holdable case: truncate. After 
truncate, a Page can be recreated, and RowLocations may be reused on the 
new page. This should not be a problem for the non-holdable case, since 
we will hold the table intent lock.

Andreas

> Andreas Korneliussen wrote:
> 
>> Andreas Korneliussen wrote:
>>
>>> Daniel John Debrunner wrote:
>>>
>>>> Andreas Korneliussen wrote:
>>>>
>>>>
>>>>> 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.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> I think this last paragraph is an incorrect assuption. The table intent
>>>> lock prevents other transactions from doing a compress, but not the
>>>> transaction holding the lock.
>>>>
>>>
>>
>> It seems to me that that online compress will not use the same 
>> transaction:
>>
>> ij> autocommit off;
>> ij>  get cursor c1 as 'select * from t1 for update';
>> ij>  call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','T1', 1, 1, 1);
>> ERROR 40XL1: A lock could not be obtained within the time requested
>> ij> rollback;
>>
>>
>> Offline compress is rejected if executed from the same connection:
>> ij>   get cursor c1 as 'select * from t1 for update';
>> ij> next c1;
>> ID
>> -----------
>> 1
>> ij>  call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 0);
>> ERROR 38000: The exception 'SQL Exception: Operation 'ALTER TABLE' 
>> cannot be performed on object 'T1' because there is an open ResultSet 
>> dependent on that object.' was thrown while evaluating an expression.
>> ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 
>> 'T1' because there is an open ResultSet dependent on that object.
>> ij>
>>
>> Are there other user-visible mechanisms to start online compress ?
>>
>> If not, I think we could conclude that there are no known issues with 
>> the use of RowLocation in non-holdable SUR (given the discussions 
>> about validity of RowLocation in separate threads)
>>
>> Andreas
>>
>>
>>> That is a good point.
>>>
>>> The main problem would be the system doing a compress, however we 
>>> should take into account the fact that the user can run compress from 
>>> the same transaction, and then maybe invalidate the resultset, or 
>>> prevent the compress from running.
>>>
>>>> I think there are other situations where the RowLocation will become
>>>> invalid, such as the transaction deleteing the row.
>>>>
>>>
>>> Yes, however as far as I understood, the RowLocation would not be 
>>> reused as long as at least some sort of table level intent lock is 
>>> held, and the store will simply return false if one tries to do 
>>> update / delete / fetch on a RowLocation which is deleted, or 
>>> deleted+purged.
>>>
>>> Andreas
>>
>>
>>
>>
>>
> 

Re: conflict detection strategies

[Mike Matrigali <mi...@sbcglobal.net>]

I was not aware of the alter table dependency check for offline
compress, this must be outside of store somehow  - maybe something
tied to all alter table statements.  It may make sense
to change online compress to hook up with whatever offline compress
is doing to make this happen.

Just testing the current system does not mean that future changes
won't break SUR.  Unless we agree to change the contract on unlocked
RowLocations, then it still isn't right for code to depend on
an unlocked RowLocation not ever pointing to the wrong row - because
of the issue with truncate.   Some possible issues with your test
in the online compress case:
1) online compress has 3 separate phases, all of which do different
    types of locking.  Some use internal transactions, which explain
    the conflict lock.  I would try the following test:
    o autocommit off
    o hold cursor as your example, with a next
    o commit transaction
    o execute in place compress now that hold cursor has released all 
it's locks.

Andreas Korneliussen wrote:
> Andreas Korneliussen wrote:
> 
>> Daniel John Debrunner wrote:
>>
>>> Andreas Korneliussen wrote:
>>>
>>>
>>>> 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.
>>>
>>>
>>>
>>>
>>> I think this last paragraph is an incorrect assuption. The table intent
>>> lock prevents other transactions from doing a compress, but not the
>>> transaction holding the lock.
>>>
>>
> 
> It seems to me that that online compress will not use the same transaction:
> 
> ij> autocommit off;
> ij>  get cursor c1 as 'select * from t1 for update';
> ij>  call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','T1', 1, 1, 1);
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij> rollback;
> 
> 
> Offline compress is rejected if executed from the same connection:
> ij>   get cursor c1 as 'select * from t1 for update';
> ij> next c1;
> ID
> -----------
> 1
> ij>  call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 0);
> ERROR 38000: The exception 'SQL Exception: Operation 'ALTER TABLE' 
> cannot be performed on object 'T1' because there is an open ResultSet 
> dependent on that object.' was thrown while evaluating an expression.
> ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 'T1' 
> because there is an open ResultSet dependent on that object.
> ij>
> 
> Are there other user-visible mechanisms to start online compress ?
> 
> If not, I think we could conclude that there are no known issues with 
> the use of RowLocation in non-holdable SUR (given the discussions about 
> validity of RowLocation in separate threads)
> 
> Andreas
> 
> 
>> That is a good point.
>>
>> The main problem would be the system doing a compress, however we 
>> should take into account the fact that the user can run compress from 
>> the same transaction, and then maybe invalidate the resultset, or 
>> prevent the compress from running.
>>
>>> I think there are other situations where the RowLocation will become
>>> invalid, such as the transaction deleteing the row.
>>>
>>
>> Yes, however as far as I understood, the RowLocation would not be 
>> reused as long as at least some sort of table level intent lock is 
>> held, and the store will simply return false if one tries to do update 
>> / delete / fetch on a RowLocation which is deleted, or deleted+purged.
>>
>> Andreas
> 
> 
> 
> 

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Andreas Korneliussen wrote:
> Daniel John Debrunner wrote:
> 
>> Andreas Korneliussen wrote:
>>
>>
>>> 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.
>>
>>
>>
>> I think this last paragraph is an incorrect assuption. The table intent
>> lock prevents other transactions from doing a compress, but not the
>> transaction holding the lock.
>>
> 

It seems to me that that online compress will not use the same transaction:

ij> autocommit off;
ij>  get cursor c1 as 'select * from t1 for update';
ij>  call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','T1', 1, 1, 1);
ERROR 40XL1: A lock could not be obtained within the time requested
ij> rollback;


Offline compress is rejected if executed from the same connection:
ij>   get cursor c1 as 'select * from t1 for update';
ij> next c1;
ID
-----------
1
ij>  call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 0);
ERROR 38000: The exception 'SQL Exception: Operation 'ALTER TABLE' 
cannot be performed on object 'T1' because there is an open ResultSet 
dependent on that object.' was thrown while evaluating an expression.
ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 'T1' 
because there is an open ResultSet dependent on that object.
ij>

Are there other user-visible mechanisms to start online compress ?

If not, I think we could conclude that there are no known issues with 
the use of RowLocation in non-holdable SUR (given the discussions about 
validity of RowLocation in separate threads)

Andreas


> That is a good point.
> 
> The main problem would be the system doing a compress, however we should 
> take into account the fact that the user can run compress from the same 
> transaction, and then maybe invalidate the resultset, or prevent the 
> compress from running.
> 
>> I think there are other situations where the RowLocation will become
>> invalid, such as the transaction deleteing the row.
>>
> 
> Yes, however as far as I understood, the RowLocation would not be reused 
> as long as at least some sort of table level intent lock is held, and 
> the store will simply return false if one tries to do update / delete / 
> fetch on a RowLocation which is deleted, or deleted+purged.
> 
> Andreas

Re: conflict detection strategies

[Andreas Korneliussen <An...@Sun.COM>]

Daniel John Debrunner wrote:
> Andreas Korneliussen wrote:
> 
> 
>>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.
> 
> 
> I think this last paragraph is an incorrect assuption. The table intent
> lock prevents other transactions from doing a compress, but not the
> transaction holding the lock.
> 

That is a good point.

The main problem would be the system doing a compress, however we should 
take into account the fact that the user can run compress from the same 
transaction, and then maybe invalidate the resultset, or prevent the 
compress from running.

> I think there are other situations where the RowLocation will become
> invalid, such as the transaction deleteing the row.
> 

Yes, however as far as I understood, the RowLocation would not be reused 
as long as at least some sort of table level intent lock is held, and 
the store will simply return false if one tries to do update / delete / 
fetch on a RowLocation which is deleted, or deleted+purged.

Andreas

Re: conflict detection strategies

[Daniel John Debrunner <dj...@apache.org>]

Andreas Korneliussen wrote:

> 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.

I think this last paragraph is an incorrect assuption. The table intent
lock prevents other transactions from doing a compress, but not the
transaction holding the lock.

I think there are other situations where the RowLocation will become
invalid, such as the transaction deleteing the row.

Dan.