Re: Phantom Inserts

In article <3icup0$pbn@canopus.cc.umanitoba.ca>, umyin@raphael.mctrf.mb.ca says... > >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. How does HOLDLOCK help you in this situation? The first SELECT with HOLDLOCK keeps a shared lock on the rows in the SELECT for the duration of the transaction. That will prevent another transaction from updating or deleting those rows, but I don't see how it will prevent the INSERT from showing up in the second SELECT. Am I missing something? -- Jim Ancona janco@dbsoftware.com jpa@iii.net Opinions expressed are my own, and not those of D&B Software.