Re: Adjacent Key locking

Denham.M@amstr.com wrote in article <5n70ge$53r@cssun.mathcs.emory.edu>... > Nicholas > > As far as adjacent key locking is concerned, forget it. If my memory serves me correctly > this was an issue with V5 and earlier engines. Not V7. > > I cannot of course vouch for the fact that adjacent key locking is not used in V7 Adjacent key locking is not present from version 6.x onwards, which includes 7.x These versions have an extra byte added to index rows to carry a 'deleted' flag. (Which explains why you have to drop and rebuild the indexes on upgrading from 5 to 6 or 7 ) Might help to try to explain adjacent key locking. In v 5 online or earlier, when you deleted a row, but had not issued a commit, the possibility existed that somebody else might insert an identical key ( in a unique index ) and do a commit. Then you change your mind and roll back instead of commit. This would leave two identical unique keys. To avoid this, when a key was deleted, the next highest value in the index was locked (adjecent key locking ) until you did your commit. If you were deleting the last (highest) value in the index, something called the infinity key was locked which prevented any higher values being inserted. During an insert, v5 engines check to see if the next highest value to that being inserted is locked, and if it is, it rejects the insert. Version 6 onwards get round this by flagging the index row as deleted but it is not actually deleted then...this is done later. I suspect that the problems being encountered are more likely due to using page level locking. Setting the isolation level to dirty read on the process doing the insert does not help because any insert locks the row or page being inserted until it is commited. The solution may be to commit each row as soon as possible after the insert.