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

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