Re: Phantom Inserts

In article <3icup0$pbn_at_canopus.cc.umanitoba.ca>, umyin_at_raphael.mctrf.mb.ca says...
>
>In <pwongD4C773.F15_at_netcom.com> pwong_at_netcom.com (Philip S. Wong) writes:
>
>>I was wondering how different databases/vendors implement protection
>>against phantom inserts. In particular, how can they achieve phantom
>>protection while maintaining a good degree of concurrency?
 

>>Example:
 

>> Transaction T0 Transaction T1
 

>> select <some projection>
>> from Table_A .....
>> where <condition X>
 

>> .... insert <some rows) into Table_A
>> where <condition X>
>> COMMIT;
 
>> select <some projection>
>> from Table_A .....
>> where <condition X>
>
>
>
>>If protection against phantom updates is enforced, both selects in
>>transaction T0 should get the same set of rows. A very interesting
>>case will be when the insert in transaction T1 also has to update
>>some indexes on Table A.
 

>>It seems to me that complete phantom protection would prevent concurrent
>>inserts in any table that is read by a transaction. Any thoughts?
>>How do different databases handle this?
 

>>BTW, how important is the protection against phantom inserts anyway?
>>It doesn't seem common in applications that I have seen.
>
>I have to have that protection once in a while. It's called "inconsistent
>read" in Sybase doc. It's prevented by HOLD LOCK keyword in a select
>statement. See the manual for detail.

How does HOLDLOCK help you in this situation? The first SELECT with HOLDLOCK keeps a shared lock on the rows in the SELECT for the duration of the transaction. That will prevent another transaction from updating or deleting those rows, but I don't see how it will prevent the INSERT from showing up in the second SELECT. Am I missing something?

--
Jim Ancona               janco_at_dbsoftware.com               jpa_at_iii.net
Opinions expressed are my own, and not those of D&B Software.