Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Wed, 22 Feb 2006 11:06:23 +0100
From: Andreas Korneliussen <Andreas.Korneliussen@Sun.COM>
Subject: Re: conflict detection strategies
In-reply-to: <43F347C7.4040501@sun.com>
To: Andreas.Korneliussen@Sun.COM
Cc: derby-dev@db.apache.org
Reply-to: Andreas.Korneliussen@Sun.COM
Message-id: <43FC379F.8080301@sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050930)
References: <43F1BEF6.6020301@sun.com> <43F3388A.9060500@apache.org>
 <43F347C7.4040501@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