Re: Phantom Inserts

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.

-- 

Q Vincent Yin                   |       Repeat
umyin_at_mctrf.mb.ca               |          delete(next->bug);
                                |       Until 0 = 1;