Re: Phantom Inserts

In article <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?

In Sybase 4.9 and 10, the HOLDLOCK keyword in the SELECT statement can guard against phantom inserts; in 10, you can even set the isolation level to 3 and have the HOLDLOCK automatically generated at the appropriate places for your session.

>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?

Actually, it is not necessary to prevent concurrent inserts in the entire table in order to guard against phantom inserts. In Sybase, it achieves that objective by the following method:

   (1) On the table itself, no concurrent inserts allowed on the

       pages where the rows being read by a transaction are located.

   (2) On the leaf-level of an index (and in the case of a clustered

       index, the leaf-level is the table itself,) no concurrent
       inserts allowed on the (index) pages where the index entries
       of the rows being read by a transaction as well as their
       adjacent index entries are located.

-- 
Paul Chen, Ph.D.
consulting at DEC 
Disclaimer: My opinions only!