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
