sql 闩锁 原因_关于SQL Server中的闩锁

sql 闩锁 原因

SQL Server locks, discussed in the article All about locking in SQL Server, which is applied on data for the duration of the logical operation to preserve logical transaction consistency. SQL Server latches, however, are a special type of low-level system locks which are held as long as the physical operation lasts on the memory page in order to protect memory consistency

SQL Server锁，在“ 关于 SQL Server中的锁”一文中进行了讨论，该锁在逻辑操作期间应用于数据，以保持逻辑事务的一致性。 但是，SQL Server闩锁是一种特殊类型的低级系统锁，只要物理操作在内存页上持续进行，它们就会保留下来，以保护内存一致性

SQL Server latches are an internal SQL Server mechanism that serves to protect shared memory resources, like pages and memory data structures inside the buffer pool, in order to coordinate access to those resources and protect them from corruption. Designed as an internal SQL Server mechanism that is not exposed outside the SQL Server Operating System (SQLOS), latches can be managed only by SQL Server, itself, and not by users (unlike locks that can be managed via NO LOCK hints). Every time SQL Server has to read memory, it will impose latches to the page or internal memory structure that cannot be accessed in a proper multi-threaded way. In this way, SQL Server establishes latches as a resource for the coordination of multiple physical thread execution in a SQL Server database

SQL Server闩锁是一种内部SQL Server机制，用于保护共享内存资源，例如缓冲池中的页面和内存数据结构，以协调对这些资源的访问并保护它们免遭损坏。 闩锁被设计为内部SQL Server机制，不会在SQL Server操作系统（SQLOS）外部公开，只能由SQL Server本身而不由用户管理（不同于可以通过NO LOCK提示进行管理的锁）。 每次SQL Server必须读取内存时，它将对无法以适当的多线程方式访问的页面或内部内存结构施加闩锁。 这样，SQL Server将闩锁建立为一种资源，用于协调SQL Server数据库中多个物理线程的执行

In the same manner as locks, SQL Server latches can come in various modes:

以与锁定相同的方式，SQL Server闩锁可以有多种模式：

• Destroy Latch (DT): the most restrictive latch mode, acquired when a latch is destroyed and a buffer is to be removed from the cache. DT latches block even the KP latch 销毁闩锁（DT） ：限制最严格的闩锁模式，在销毁闩锁并从高速缓存中删除缓冲区时获取。 DT闩锁甚至挡住了KP闩锁
• Exclusive Latch (EX): acquires exclusive control of a page being written. Prevents all other latches to be acquired on the page where EX latch exists 排他锁（EX） ：获得对正在写入的页面的排他控制。 防止在存在EX锁存器的页面上获取所有其他锁存器
• Update Latch (UP): restrictive similar to an exclusive latch, with an exception that it allows read operation to access the page, but restricts, explicitly, any write operation更新锁存器（UP）：与独占锁存器类似，但有限制，区别在于它允许读取操作访问页面，但明确限制任何写操作
• Keep Latch (KP): it serves to preserve a latch order record but also to ensure that it stays in the buffer when a new latch is being placed on it 保留锁存器（KP） ：它不仅可以保留锁存器顺序记录，而且还可以确保在将新的锁存器放置在缓冲区上时将其保留在缓冲区中
• Shared Latch (SH): acquired on a page when a read request issued to a page is granted 共享锁存器（SH） ：在授予页面的读取请求时，在页面上获取

Similarly to locks, there is a compatibility or incompatibility component between the various latch modes. The table below gives an insight in compatibility between the variou