Spin locks in SQLSERVER

There are many spin locks in SQLSERVER, such as intention sharing locks, exclusive locks, row and page locks. latch is more lightweight than LOCK and only exists in memory, it is generally used to lock data pages to prevent multiple users from modifying a data page in the memory at the same time. The introduced spinlock is more lightweight than the above two locks.

There are many spin locks in SQLSERVER, such as intention sharing locks, exclusive locks, row and page locks. latch is more lightweight than LOCK and only exists in memory, it is generally used to lock data pages to prevent multiple users from simultaneously modifying a data page in the memory. The spin lock introduced today is more lightweight than the above two locks.

Spin locks in SQLSERVER

There are many locks in SQLSERVER, such as intention-sharing locks, exclusive locks, row locks, and page locks.

Latch is more lightweight than lock and only exists in the memory. It is generally used to lock data pages to prevent multiple users from simultaneously modifying a data page in the memory.

The spin lock introduced today is more lightweight than the above two locks.

Introduction to spin lock:

In addition to lock and latch, there is also a more lightweight synchronization Resource: spin lock (spin lock). The first time we see spin lock, this term is inPan aiminWritten by the instructor

Principle and Implementation of Windows KernelAs we can see in this book, it is estimated that SQLSERVER also draws on some synchronization technologies of the Windows operating system.

In SQLSERVER, some spin locks will soon be available and will be released. In general, there will not be a long wait for synchronization of resources,

SQLSERVER choose to let the thread wait for a moment on the CPU (so-called spin), instead of letting the CPU resources out. Because resources can be obtained quickly,

This can reduce the thread switching on the CPU and improve efficiency (Windows also reduces the overhead of thread switching, and frequent thread context switching consumes CPU resources)

Spin locks may cause some bottlenecks:

Like latch, spin lock is a lightweight resource and should not become a bottleneck of SQLSERVER. When you see that many tasks in SQLSERVER are running

Or the runnable status may be the reason for spin lock. Within SQLSERVER, there are many spin locks. There are so many 172 types in SQLSERVER2005

You can run the following statement to obtain the number of times SQL Server waits for all spin locks.

1 DBCC SQLPERF(spinlockstats)