Re: Phantom Inserts

In article <3iicrs$7b7@jac.zko.dec.com>, chen_p@perfit.zko.dec.com (Paul Chen) writes... . . . Curiously wondering how others accomplish 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. This statement doesn't make sense to me...I can understand an intention lock on the table, if the access is done through an index. But a 'real table lock' is not necessary unless there are others in the table executing in isolation level 3 and others are not using an index for selection [sequential scan]. > > (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. > So the locking of the index is at the page level. Is there any control over the page size to manage the key entries allowed on a page? [basically to enhance the concurrency to the leaf level of the index]. Do you use any escalation scheme to the table, if the some number of pages end up being locked [trade offs being...reduce the locking overhead since you've locked a sizable key range of the index already | vs | cut off all concurrecy to the table? Cheers, ********************************************************************* *** Jay Feenan *** *** Oracle Rdb Engineering *** *** *** *** opinions expressed here are solely my own and not those of my *** *** employer. *** *** *** *********************************************************************