SQL Server - When it comes to lock index while update-2

Locking behaviour of Indexes while updating rows

Hello all

The theory I am presenting you is from Reading in database systems and I find it valuable

Today I will discuss about locking mechanism at index level, I had used extended events to show a demo. Whenever we update on Heap pages or the table which doesn’t have any index than, it’s easy to put locks on data pages but in case of Indexes we have pages other than data pages like pages on intermediate level and pages on root level.

So the first question arises here, why do we require lock on root and intermediate pages while updating any single row on leaf level?

If a transaction is updating leaf level, it can also update intermediate level pages or root page depending on transaction, but we can’t use normal locking algorithm. It’s because in normal read committed isolation level Lock would be acquired on root and other level till the transaction commits.

What I want to say is as on my previous blog I had shared lock on index page and shared lock is incompatible with exclusive lock, it means no other transaction can modify the pages pointed by that index page, so from concurrency point of view it’s bad. We normally use different algorithm for index locking to avoid such problems. There are three schemes present below

Conservative Scheme-which allow multiple transactions to access the same pages only if they can be guaranteed not to conflict in their use of a page’s content. One such conflict is that a reading transaction wants to traverse a fullypacked internal page of the tree, and a concurrent inserting transaction is operating below that page, and hence might need to split it [4]. These conservative schemes sacrifice too much concurrency compared with the more recent ideas below.

Latch coupling scheme, in which the tree traversal logic latches each node before it is visited, only unlatching a node when the next node to be visited has been successfully latched. This scheme is sometimes called latch “crabbing”, because of the crablike movement of “holding” a node in the tree, “grabbing” its child, releasing the parent, and repeating. Latch coupling is used in some commercial systems; IBM’s ARIES-IM version is well described [46]. ARIES-IM includes some fairly intricate details and corner cases – on occasion it has to restart traversals after splits, and even set (very short-term) tree-wide latches.

Right link scheme, which add some simple additional structure to the B+-tree to minimize the requirement for latches and retraversals. In particular, a link is added from each node to its right-hand neighbour. During traversal, right-link schemes do no latch coupling – each node is latched, read, and unlatched. The main intuition in right-link schemes is that if a traversing transaction follows a pointer to a node n and finds that n was split in the interim, the traversing transaction can detect this fact, and “move right” via the right links to find the new correct location in the tree.