SQL Server Latch Vs Locks

SQL Server Latch VS Lock

Latch
Latches are very lightweight, short-term synchronization objects protecting actions that need not be locked for the life of a transaction. They are primarily used to protect a row when read for a connection.

Lock
Locks in SQL Server protects the tables or data pages currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data across transactions. It is needed in a multi-user environment, since several users may be working with the same data at the same time.

Why do we need Latches/Locks for Database?
Latches were first introduced in SQL Server 7.0, when Microsoft first introduced row-level locking. For row-level locking it was very important to introduce a concept like latching, because otherwise it would give rise to phenomena like Lost Updates in memory.

A page in SQL Server is 8KB and can store multiple rows. To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks which are held for the duration of the logical transaction. Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

When the relational engine is processing a query, each time a row is needed from a base table or index, the relational engine uses the OLE DB API to request that the storage engine return the row. While the storage engine is actively transferring the row to the relational engine, the storage engine must ensure that no other task modifies either the contents of the row or certain page structures such as the page offset table entry locating the row being read. The storage engine does this by acquiring a latch, transferring the row in memory to the relational engine, and then releasing the latch.

SQL Server Performance Monitor has a Latches object that indicates how many times latches could not be granted immediately and the amount of time threads spent waiting for latches to be granted.

Latches are often confused with locks, as their purposes are similar but not the same. A latch can be defined as an object that ensures data integrity on other objects in SQL Server memory, particularly pages. They are a logical construct that ensures controlled access to a resource and isolationism when required for pages in use. In contrast to locks, latches are an internal SQL Server mechanism that isn't exposed outside the SQLOS.

While locks protect data during transactions, another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction. When the engine scans a page, it latches the page, reads the row, gives it back to the relational engine, and then unlatches the page again so another process can reach the same data. Through a process called lazy latching, the storage engine optimizes access to the data pages by releasing latches only when a page is also requested by another ongoing process. If no ongoing process requests the same data page, a single latch remains valid for the entire operation on that page.

Latching
SQL Server uses latches to provide data synchronization. A latch is a user-mode reader-writer lock implemented by SQL Server. Each data page in memory has a buffer (BUF) tracking structure. The BUF structure contains status information (Dirty, On LRU, In I/O) as well as a latch structure.

Locking maintains the appropriate lock activity; latching controls physical access. For example, it is possible for a lock to be held on a page that is not in memory. The latch is only appropriate when the data page is in memory (associated with a BUF).

The following list describes the different types of latches:

• I/O latches:
I/O Latches are used by SQL Server when outstanding I/O operations against pages in the Buffer Pool are done – when you read and write from/to your storage subsystem. For these I/O latches SQL Server reports a wait type that starts with PAGEIOLATCH_.
You can see the waiting times introduced with these types of latches in the DMV sys.dm_os_wait_stats.

• Non-buffer (Non-BUF) latch:
The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrance protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
- SQL Server also reports these latches in the DMV sys.dm_os_wait_stats with wait types starting with LATCH_.

• Buffer (BUF) latch:
The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction.
- SQL Server also reports the waits introduced by these latches with wait types starting with PAGELATCH_*. These wait types are again reported to you through the DMV sys.dm_os_wait_stats.