按键锁实际锁定什么资源? [英] What resource does a key lock actually lock?

问题描述

我知道一个键锁将一个键锁定在索引中.但是，键"实际上是什么意思?

I know a key lock locks a key in an index. However, what does "key" actually mean?

例如，如果我在姓氏列上有一个非聚集索引，并尝试在surname ="Jones"的情况下进行更新，我是否会有效地锁定该姓氏为"Jones"的表中的每一行?还是将索引锁定在更高的级别，以防止访问"Jones"以外的姓氏的行?

For example, if I have a non-clustered index on a surname column and attempt an update where surname = "Jones", will I have effectively locked every row in the table where the surname is "Jones"? Or will the index be locked at a higher level, preventing access of rows with surnames other than "Jones"?

我问的原因是《联机丛书》中有关锁粒度和层次结构的注释:

The reason I ask is this note in Books Online about Lock Granularity and Hierarchies:

KEY:索引中的行锁，用于保护可序列化事务中的键范围.

KEY: A row lock within an index used to protect key ranges in serializable transactions.

这表明将锁定一系列键，而不仅仅是一个.

This suggests a range of keys will be locked, not just one.

推荐答案

键锁会影响与给定谓词(种类)匹配的所有行-在您的示例中，所有带有surname = 'Jones'的行都会受到影响.

A keylock affects all rows that match the given predicate (kind of) - in your example all rows with surname = 'Jones' will be affected.

之所以使用范围"一词，是因为该谓词可能会影响行的范围，例如，如果谓词是age > 18，那么年龄大于18的所有行都会受到影响.

The word "range" is used because depending on the predicate a range of rows may be affected, for example if the predicate was age > 18 then all rows where the age is greater than 18 would be affected.

理解键锁并不能简单地单独锁定索引中的每个匹配行，这一点也很重要-您的示例键锁不仅会影响姓氏为"Jones"的索引中的所有现有行，而且还会影响对修改的任何尝试现有行或插入姓氏为"Jones"的新行.

It is also important to understand that a key lock does not simply individually lock each matching row in the index - your example keylock not only affects all existing rows in the index with surname "Jones", it also affects any attempt to modify an existing row or insert a new row with surname "Jones".

以稍微不同的方式考虑锁可能会有所帮助-锁仅在SQL Server尝试获取可能不兼容的另一个锁时才起作用(即，同时拥有两个锁是不合法的)同时).例如，如果您在具有age > 18的行上具有排他键锁，并尝试使用age = 42插入或修改行，则SQL Server将首先尝试获取相关的锁-看到行中存在现有的键锁.使用age > 18的SQL Server确定锁不兼容，并采取相应的措施.

It may help to think about locks in a slightly different way - locks only have an effect when SQL Server attempts to obtain another lock which may be incompatible (i.e. it is not legal to have both locks at the same time). For example if you have an exclusive key-lock on rows with age > 18 and attempt to insert or modify a row with age = 42 then SQL server will first attempt to obtain the relevant locks - seeing that there is an existing key lock for rows with age > 18 SQL Server determines that the locks are incompatible and takes action accordingly.

这篇关于按键锁实际锁定什么资源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！