SQL Server misunderstanding 30th about the 8th day about online operation of the index error _mssql

Myth #8: The cue operation does not cause the associated index to be locked

Error!

The online indexing operation is not as good as it might seem.

A short lock on the resource at the start of the operation and at the end of the operation. This can lead to serious blocking problems.

At the start of the cue operation, a shared table lock is added to the sorted resource, which persists when the new index is created and when the old index is scanned.

The problem is that this s lock is arranged in a lock queue with other locks on the table. This means that other locks incompatible with S locks exist in S-locks on the table or when the lock queues on the table contain S locks, which are also required for lock operations that are incompatible with S locks. This also means that the various update operations will be blocked. Similarly, an S lock request can be blocked if an X or IX lock is present on the table.

When the above steps are complete, the S lock is removed, but you can see that this has an impact on the data update. This will also cause the execution plan for all pending update operations to be recompiled

Most of the rest of the time is not required to lock the thread after it has been completed at the beginning. (This most refers to the entire online indexing of most of the time)

When the online indexing operation is complete, the newly established index and the old index need to add a schema modification lock (Sch_m Lock) to complete the final operation. This lock can be imagined as a stronger table-level exclusive lock. No action is allowed on the table during this lock existence, nor can the execution plan for the table be recompiled.

The blocking problem at the end of the cue operation and the blocking problem caused by the S lock at the start of the online indexing operation is very similar-no action is allowed on the table during the Sch_m lock duration or waiting to be granted. Conversely, sch_m locks cannot be granted when there are any read and write operations in the table.

During the final phase of the Sch_m lock, the old index is executed with a deferred drop operation, the allocation structure pointed to by the metadata points to the new index (so the index ID is unchanged), and the version of the table is updated. Congratulations, you've got a whole new index now.

As you can see, there are potentially huge blocking problems at the start and end of the lead operation. So technically the online indexing operation should be called "most of the time in the cue operation", but this name can not be welcomed by the market. If you want to learn more about online indexing operations, read the white paper: Online indexing Operations in SQL Server 2005.

The translator notes: Wang Yang has a very detailed article on the clue operation, interested students can read: Online indexing work, below I excerpt from his article in a picture to make online indexing operation of the steps more clearly.