Phantom Inserts

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

     ....			   insert <some rows) into Table_A
			           where <condition X>
				   COMMIT;

    select <some projection>     
    from Table_A			    .....

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.   Received on Tue Feb 21 1995 - 07:25:50 CET