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