A brief introduction to the spin lock _mssql in SQL Server

Why do we need spin locks?
Using latches to synchronize data structure access between multiple threads, it is meaningless to place a latch before each shared data structure. The latch is closely associated with this: when you can't get the latch (because someone else already has an incompatible latch to get it), the query forces the wait and enters the suspend (suspended) state. The query waits until the latch has been suspended until it can be reached, and then enters the executable (RUNNABLE) state. For query execution, the query is in an executable (RUNNABLE) state as long as there is no available CPU. Once the CPU is idle, the query enters the running (RUNNING) state, and finally the latch is successfully secured to protect the shared data structures that are accessed. The following figure shows the state machine that the Sqlos implements for coordinated thread scheduling.

There is no point in using latch protection for "busy" data structures because there are too many associated latches. So SQL Server implements the so-called spin lock (Spinlocks). A spin lock is like a latch, a lightweight synchronization object used by the storage engine to synchronize access to shared data structure threads. The main difference between the latches and the latch is that you're actively waiting for the spin lock--without leaving the CPU. The "Wait" in the spin lock always occurs in the running (RUNNING) state of the CPU. Rotate in your closed loop until you get the spin lock. This is called busy waiting (busy wait). The greatest advantage of spin locks is that context switches are not involved when the query is waiting in the spin lock. On the other hand, busy waiting to waste CPU cycles, other queries may be more efficient use of them.

To avoid too much CPU-cycle waste, SQL Server 2008 R2 and subsequent versions implement the so-called exponential compensation mechanism (exponential backoff mechanism), where the thread stops spinning after some time of hibernation on the CPU. When a thread enters hibernation, it increases the timeout for trying to obtain a spin lock. This behavior can reduce the impact on CPU performance.

(Supplemental Note: Spinlock Chinese can be called spin lock.) It is a lightweight, user-State synchronization object, similar to the critical section, but has a much smaller granularity than the former. It is primarily used to protect multithreaded concurrent access to certain memory objects. The spinlock is exclusive. Can only be owned by one thread at a time.

Spinlock's design goal is very fast and efficient. How does spinlock internal work? It first attempts to acquire a lock on an object, where it polls (spin) for a certain time if the target is occupied by another thread. If you don't get the lock, sleep for a little while and then continue to spin. Repeat this process until you get possession of the object. ）

Self-rotating lock and trouble shooting
The main DMV for Spin lock troubleshooting is sys.dm_os_spinlock_stats. Each row returned in this DMV represents a spin lock in SQL Server. SQL Server 2014 implements 262 different spin locks. Let's take a look at the various columns in this DMV:

Name: Spin lock names
Collision: The number of threads blocked by a spin lock when trying to access a protected data structure
spins: Number of spin lock threads in the loop trying to get a spin lock
spins_per_collision: The ratio between rotation and collision
sleep_time: Because of the avoidance of thread hibernation time
backoffs: Thread avoidance times for other threads to continue on the CPU
The most important column in this DMV is backoffs, which tells you how often the avoidance occurs for a particular spin lock type. The avoidance of high frequencies yields to CPU consumption causing the spin lock competition in SQL Server (Spinlock contention). I have seen a 32-core SQL Server server, the CPU running at 100% without any work-typical spin lock competition symptoms.

Troubleshoot the spin lock problem you can use the extended event provided by the Sqlos.spinlock_backoff. When avoidance (Backoff) occurs, this extension event is triggered. If you capture this event, you also have to make sure that you use very good selectivity predicates, because avoidance in SQL Server often occurs. A good predicate can be a specific spin lock type that you have seen through the DMV mentioned just now. The following code shows you how to create such an extended event session.

 --Retrieve The type value for the Lock_hash spinlock.
 --This value is used by the next xevent sessions
 SELECT * from sys.dm_xe_map_values
 WHERE name = ' Spinlock_types ' C4/>and map_value = ' Lock_hash '
 go
 
 --tracks the Spinlock_backoff event
 CREATE event session Spinlockcontention on SERVER 
 ADD EVENT sqlos.spinlock_backoff
(
 ACTION
  package0.callstack
 )
  WHERE
 (
  [Type] = 129--<<< Value from the previous query
 )
) 
ADD TARGET Package0.histo Gram
 (
  SET Source = ' Package0.callstack ', Source_type = 1
 )
 go

As you can see from the code, I use the histogram (histogram) target on the call stack (callstack) to Bucktize. So for a particular spin lock, you can probably go to the highest avoidance (backoffs) code path generated in the SQL serve. You can even identify the call stack by enabling 3656 Trace flags (trace flag). Here you can see the output from this extended session:

sqldk.dll! Xesospkg::spinlock_backoff::P ublish+0x138
sqldk.dll! Spinlockbase::sleep+0xc5
sqlmin.dll! Spinlock<129,7,1>::Spintoacquirewithexponentialbackoff+0x169
sqlmin.dll!lck_lockinternal+0x841
sqlmin.dll! Xactworkspaceimp::Getshareddblockfromlockmanager+0x18d
sqlmin.dll! xactworkspaceimp::getdblocklocal+0x15b
sqlmin.dll! xactworkspaceimp::getdblock+0x5a
SQLMIN.DLL!LOCKDB+0X4A sqlmin.dll! Dbmgr::opendb+0x1ec
Sqlmin.dll!sqlusedb+0xeb
Sqllang.dll!usedb+0xb3
sqllang.dll! Loginusedbhelper::usebymddatabaseid+0x93
sqllang.dll! Loginusedbhelper::fdeterminesessiondb+0x3e1
sqllang.dll! Fredologinimpl+0xa1b
sqllang.dll! Fredologin+0x1c1
Sqllang.dll!process_request+0x3ec
Sqllang.dll!process_commands+0x4a3
sqldk.dll! Sos_task::P aram::execute+0x21e
sqldk.dll! Sos_scheduler::runtask+0xa8
sqldk.dll! Sos_scheduler::P rocesstasks+0x279
sqldk.dll! schedulermanager::workerentrypoint+0x24c
sqldk.dll! systemthread::runworker+0x8f
sqldk.dll! Systemthreaddispatcher::P rocessworker+0x3ab
sqldk.dll! schedulermanager::threadentrypoint+0x226

Using the provide call stack, it's not hard to find where the spin-lock competition takes place. In that specified 笤 figurines the competition takes place in the Lock_hash spin lock type, which is a hash table that protects the lock manager. Each time a lock or unlock is performed in the lock manager, the spin lock must be obtained in the corresponding hash bucket. As you can see, in the call stack, the spin lock is obtained when the Getshareddblockfromlockmanager function is invoked from the XACTWORKSPACELMP class. This means that the shared database lock is attempted to acquire when competing to the database. Finally in the Lock_hash spin lock with high avoidance (backoffs), this yields to spin lock competition.

The above is the entire content of this article, I hope to help you learn.