On 14 March 2018 at 04:36, Pavan Deolasee <pavan.deolasee@gmail.com> wrote: > I wonder if the shortened code path actually leads to > heavier contention for EXCLUSIVE lock on the rightmost page, which in turn > causes the slowdown. But that's just a theory. Any ideas how to prove or > disprove that theory or any other pointers?
Certainly: dropping performance with higher sessions means increased contention is responsible. Your guess is likely correct.
Suggest making the patch attempt a ConditionalLock on the target block, so if its contended we try from top of index. So basically only attempt the optimization if uncontended. This makes sense because in many cases if the block is locked it is filling up and the RHS block is more likely to change anyway.
Hmm. I can try that. It's quite puzzling though that slowing down things actually make them better.
I can also try to reduce the amount of time EX lock is held by doing some checks with a SHARE lock and then trade it for EX lock if we conclude that fast path can be taken. We can do page lsn check to confirm nothing changed when the lock was traded. Will check both approaches.