Re: Phantom Inserts

In <pwongD4C773.F15@netcom.com> pwong@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@mctrf.mb.ca | delete(next->bug); | Until 0 = 1;