Latches and Spinlocks - SQL Server 2012 Internals and Troubleshooting - page 187

more about these modes in a minute, and about some of the techniques that the SQL Server engine
uses to efi ciently manage latches.
You may already be thinking that because you can, to a certain extent, avoid locking trouble by sim-
ply setting the appropriate isolation level in your application, you ought to be able to do the same
with latches. You can't.
A latch is an internal object, used by the SQL Server engine. It is not something that you, the data-
base developer, can directly inl uence. If you need to get data from a particular page, the SQL Server
engine needs to acquire a latch. You have no choice over this. Nor can you tell it what kind of latch to
acquire — that's determined by the SQL Server engine. The difference is that this is not just about the
protection of data, it's about the protection of server memory. Although you might be willing to toler-
ate dirty reads, and choose your locking strategy accordingly, you don't have that luxury with latches.
Spinlocks are a similar concept to latches, in that they are also lightweight synchronization
primitives, but they act slightly differently. A lot of the effects can seem similar, and the kinds of
systems that can exhibit spinlock contention are similar to those that can exhibit latch contention.
The main difference between a spinlock and a latch is this: If a thread fails to acquire a latch
immediately, it yields, enabling the CPU to be used for other things. If a thread fails to acquire a
spinlock, the thread starts looping (spinning), checking the resource repeatedly, with the expectation
that it will become available soon. It won't spin forever, though. After a bit of time, it will back off,
at which point it yields to other processes on the CPU.
Because we have no control over latching behavior or spinlocks, it isn't possible to demonstrate the
impact of various latch scenarios using a real system, as shown for locks in Chapter 6. Instead, the
following section presents a simulated example that uses real concepts.
Latching Example
All of the code in this section uses the Ch7Understanding.sql code i le.
To begin, suppose you have a table that contains a single row of data. The following code will set up
such an environment.
CREATE DATABASE LatchInAction;
GO
USE LatchInAction;
CREATE TABLE dbo.LatchTable
( COL1 INT
,COL2 INT
);
INSERT INTO dbo.LatchTable ( COL1, COL2 )
VALUES (1,100);
Running DBCC IND will provide information about the pages that are used in the table. You will use the
PagePID value of the row which has a PageType value of 1 . The PageType column is the tenth column
returned, so you may need to scroll. In my system, the value I'm looking for is 73 , as seen in Figure 7-6.