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

I assume your INSERT ... WHERE is pseudo code to indicate that the inserted row matches the selection predicate of the other database user.

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

Rdb provides different ISOLATION LEVEL's to allow the user select the type of protection they want from interference. The levels (from SQL-92) are:

    READ COMMITED
    REPEATABLE READ
    SERIALIZABLE What you want is serializable. i.e. concurrent transactions (as you have shown) affect the user as though it was run after the current transaction.

As specified in the SQL-92 ANSI and ISO standards ISOLATION LEVEL SERIALIZABLE is the default for Rdb. It has been since 1984 when the product was first released.

I expect that index locks will prevent the T1 transaction from doing the insert (i.e. a lock wait) until the T0 transaction commits.

|>BTW, how important is the protection against phantom inserts anyway?
|>It doesn't seem common in applications that I have seen.

Many applications do not need serializable, that is why SQL-92 allows selection of various ISOLATION LEVELs to suit the transaction. In fact using lower isolation levels is needed to improve concurrency.

-- 
Ian Smith
Rdb Engineering Group
(Standard disclaimer: These opinions are mine and in no way represent a
		      commitment or opinion of my employer)