No, you don't understand how it works. All would-be inserters will hit
the same target page to begin with, ie, the first one that the new key
could legally be inserted on. The lock that protects against this
problem is the lock on that page, regardless of which page the key
actually ends up on.
Consider Time instances T1, T2, T3, T4
T1 : session 1 holds the write lock on page p1 and completes the unique check on p1, p2 and p3.
T2 : session 1 releases the lock on p1 (its waiting to acquire a ex lock on p2)
T3 : session 2 acquires write lock on p1 and completes the unique check on p1, p2 and p3. Here, i believe the Session 2
has a chance of getting the read lock before session 1 gets the write lock. Is it not possible?
T4 : session 1 gets the write lock on p2 and inserts and session 2 gets the write lock on p1 and inserts.
OK. I have stated my assumptions. Is my assumption at time instant T3 wrong/ never happen?
Thanks,
Gokul.