sql server 锁定_关于锁定SQL Server的全部
sql server 锁定
Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.
锁定对于成功处理SQL Server事务至关重要，它旨在使SQL Server在多用户环境中无缝工作。 锁定是SQL Server管理事务并发的方式。 本质上，锁是内存中的结构，具有所有者，类型和应保护的资源的哈希。 作为内存结构的锁大小为96个字节。
To understand better the locking in SQL Server, it is important to understand that locking is designed to ensure the integrity of the data in the database, as it forces every SQL Server transaction to pass the ACID test.
为了更好地理解SQL Server中的锁定，重要的是要了解锁定的目的是确保数据库中数据的完整性，因为锁定会强制每个SQL Server事务通过ACID测试。
ACID test consists of 4 requirements that every transaction have to pass successfully:
ACID测试包含4项要求，每项交易必须成功通过：
- Atomicity – requires that a transaction that involves two or more discrete parts of information must commit all parts or none 原子性 –要求涉及两个或多个离散信息部分的事务必须提交所有部分或不提交任何部分
- Consistency – requires that a transaction must create a valid state of new data, or it must roll back all data to the state that existed before the transaction was executed 一致性 –要求事务必须创建新数据的有效状态，或者必须将所有数据回滚到执行该事务之前存在的状态
- Isolation – requires that a transaction that is still running and did not commit all data yet, must stay isolated from all other transactions 隔离 –要求仍在运行且尚未提交所有数据的事务必须与所有其他事务保持隔离
- Durability – requires that committed data must be stored using method that will preserve all data in correct state and available to a user, even in case of a failure 耐用性 –要求提交的数据必须使用一种方法存储，即使在发生故障的情况下，该方法也可以将所有数据保持在正确的状态并可供用户使用
SQL Server locking is the essential part of the isolation requirement and it serves to lock the objects affected by a transaction. While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock. Once the lock is released by committing the changes or by rolling back changes to initial state, other transactions will be allowed to make required data changes.
SQL Server锁定是隔离要求的重要组成部分，它用于锁定受事务影响的对象。 当对象被锁定时，SQL Server将阻止其他事务更改存储在受强加锁影响的对象中的数据。 一旦通过提交更改或通过将更改回滚到初始状态来释放锁，将允许其他事务进行所需的数据更改。
Translated into the SQL Server language, this means that when a transaction imposes the lock on an object, all other transactions that require the access to that object will be forced to wait until the lock is released and that wait will be registered with the adequate wait type
转换为SQL Server语言后，这意味着当事务在对象上施加锁时，所有需要访问该对象的其他事务都将*等待，直到释放该锁为止，并且该等待将通过适当的等待进行注册。类型
SQL Server locks can be specified via the lock modes and lock granularity
可以通过锁定模式和锁定粒度指定SQL Server锁定
锁定模式 (Lock modes)
Lock mode considers various lock types that can be applied to a resource that has to be locked:
锁定模式考虑了可以应用于必须锁定的资源的各种锁定类型：
- Exclusive (X) 独家（X）
- Shared (S) 共享的（S）
- Update (U) 更新（U）
- Intent (I) 意向（I）
- Schema (Sch) 架构（Sch）
- Bulk update (BU) 批量更新（BU）
Exclusive lock (X) – This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock.
排他锁（X） –施加此锁类型后，只要事务持有该锁，它将确保页面或行将专门为施加排他锁的事务保留。
The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. An exclusive lock can be imposed to a page or row only if there is no other shared or exclusive lock imposed already on the target. This practically means that only one exclusive lock can be imposed to a page or row, and once imposed no other lock can be imposed on locked resources
当事务要修改页或行数据时（例如DML语句DELETE，INSERT和UPDATE），排他锁将由事务施加。 仅当目标上没有其他共享或互斥锁时，才可以将互斥锁施加到页面或行。 实际上，这意味着只能对页面或行施加一个排他锁，并且一旦施加其他锁就不能对锁定的资源施加其他锁。
Shared lock (S) – this lock type, when imposed, will reserve a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active. However, a shared lock can be imposed by several transactions at the same time over the same page or row and in that way several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data. In addition, a shared lock will allow write operations, but no DDL changes will be allowed
共享锁（S） –强制使用此锁类型后，将保留页面或行以供读取，这意味着只要该锁处于活动状态，就将阻止其他任何事务修改已锁定的记录。 但是，共享锁可以由同一页面或行上的多个事务同时施加，因此，由于读取过程本身不会影响实际的页面或行数据，因此多个事务可以共享数据读取的能力。 此外，共享锁将允许写入操作，但不允许DDL更改
Update lock (U) – this lock is similar to an exclusive lock but is designed to be more flexible in a way. An update lock can be imposed on a record that already has a shared lock. In such a case, the update lock will impose another shared lock on the target row. Once the transaction that holds the update lock is ready to change the data, the update lock (U) will be transformed to an exclusive lock (X). It is important to understand that update lock is asymmetrical in regards of shared locks. While the update lock can be imposed on a record that has the shared lock, the shared lock cannot be imposed on the record that already has the update lock
更新锁（U） –此锁类似于独占锁，但在某种程度上被设计为更加灵活。 可以对已具有共享锁的记录强加更新锁。 在这种情况下，更新锁将在目标行上施加另一个共享锁。 一旦拥有更新锁的事务已准备好更改数据，更新锁（U）将转换为互斥锁（X）。 重要的是要了解，更新锁在共享锁方面是不对称的。 虽然可以将更新锁强加到具有共享锁的记录上，但是不能将共享锁强加到已经具有更新锁的记录上
Intent locks (I) – this lock is a means used by a transaction to inform another transaction about its intention to acquire a lock. The purpose of such lock is to ensure data modification to be executed properly by preventing another transaction to acquire a lock on the next in hierarchy object. In practice, when a transaction wants to acquire a lock on the row, it will acquire an intent lock on a table, which is a higher hierarchy object. By acquiring the intent lock, the transaction will not allow other transactions to acquire the exclusive lock on that table (otherwise, exclusive lock imposed by some other transaction would cancel the row lock).
意向锁（I） –此锁是一种事务使用的方法，用于通知另一笔事务其打算获取锁的意图 。 这种锁定的目的是通过防止另一个事务获取对下一个层次结构对象的锁定来确保正确执行数据修改。 在实践中，当事务要获取行上的锁时，它将获取表上的意图锁，该表是更高层次的对象。 通过获取意图锁，该事务将不允许其他事务获取该表上的排他锁（否则，其他事务施加的排他锁将取消该行锁）。
This is an important lock type from the performance aspect as the SQL Server database engine will inspect intent locks only at the table level to check if it is possible for transaction to acquire a lock in a safe manner in that table, and therefore intent lock eliminates need to inspect each row/page lock in a table to make sure that transaction can acquire lock on entire table
从性能方面来说，这是一种重要的锁类型，因为SQL Server数据库引擎将仅在表级别检查意图锁，以检查事务是否有可能以安全的方式在该表中获取锁，因此意图锁消除了需要检查表中的每行/页锁，以确保事务可以获取整个表的锁
There are three regular intent locks and three so-called conversion locks:
有三个常规的意图锁和三个所谓的转换锁：
常规意图锁： (Regular intent locks:)
Intent exclusive (IX) – when an intent exclusive lock (IX) is acquired it indicates to SQL Server that the transaction has the intention to modify some of lower hierarchy resources by acquiring exclusive (X) locks individually on those lower hierarchy resources
意图专用（IX） – 当获得意图互斥锁（IX）时，它向SQL Server指示该事务打算通过分别获取这些较低层次结构资源上的互斥（X）锁来修改某些较低层次结构资源
Intent shared (IS) – when an intent shared lock (IS) is acquired it indicates to SQL Server that the transaction has the intention to read some lower hierarchy resources by acquiring shared locks (S) individually on those resources lower in the hierarchy
意向共享（IS） – 当获取意图共享锁（IS）时，它向SQL Server指示该事务打算通过在层次结构中较低的那些资源上分别获取共享锁（S）来读取某些较低层次结构的资源。
Intent update (IU) – when an intent shared lock (IS) is acquired it indicates to SQL Server that the transaction has the intention to read some of lower hierarchy resources by acquiring shared locks (S) individually on those resources lower in the hierarchy. The intent update lock (IU) can be acquired only at the page level and as soon as the update operation takes place, it converts to the intent exclusive lock (IX)
意图更新（IU） –当获取意图共享锁（IS）时，它向SQL Server指示该事务打算通过分别获取层次结构中较低级别的那些资源上的共享锁（S）来读取某些较低层次结构的资源。 意向更新锁（IU）只能在页面级别获取，并且一旦进行更新操作，它就会转换为意向排他锁（IX）
转换锁： (Conversion locks:)
Shared with intent exclusive (SIX) – when acquired, this lock indicates that the transaction intends to read all resources at a lower hierarchy and thus acquire the shared lock on all resources that are lower in hierarchy, and in turn, to modify part of those, but not all. In doing so, it will acquire an intent exclusive (IX) lock on those lower hierarchy resources that should be modified. In practice, this means that once the transaction acquires a SIX lock on the table, it will acquire intent exclusive lock (IX) on the modified pages and exclusive lock (X) on the modified rows.
与意图专用共享（SIX）共享 –获得此锁后，该锁指示该事务打算读取较低层次结构上的所有资源，从而获取对较低层次结构上所有资源的共享锁，进而修改其中的一部分， 但不是所有的。 这样，它将在那些应修改的较低层级资源上获得一个意图排他（IX）锁。 实际上，这意味着一旦事务在表上获取了SIX锁，它将在修改后的页面上获取意图互斥锁（IX），并在修改后的行上获取意图互斥锁（X）。
Only one shared with intent exclusive lock (SIX) can be acquired on a table at a time and it will block other transactions from making updates, but it will not prevent other transactions to read the lower hierarchy resources they can acquire the intent shared (IS) lock on the table
一次只能在一个表上获取一个共享有意图专用锁（SIX）的共享，它将阻止其他事务进行更新，但是这不会阻止其他事务读取他们可以获取该意图共享的较低层级资源（IS ）锁在桌子上
Shared with intent update (SIU) – this is a bit more specific lock as it is a combination of the shared (S) and intent update (IU) locks. A typical example of this lock is when a transaction is using a query executed with the PAGELOCK hint and query, then the update query. After the transaction acquires an SIU lock on the table, the query with the PAGELOCK hint will acquire the shared (S) lock while the update query will acquire intent update (IU) lock
与意图更新（SIU）共享 –这是更具体的锁定，因为它是共享（S）和意图更新（IU）锁定的组合。 此锁的典型示例是，当事务使用的查询使用PAGELOCK提示和查询，然后使用更新查询时。 事务获取表上的SIU锁后，带有PAGELOCK提示的查询将获取共享（S）锁，而更新查询将获取意图更新（IU）锁
Update with intent exclusive (UIX) – when update lock (U) and intent exclusive (IX) locks are acquired at lower hierarchy resources in the table simultaneously, the update with intent exclusive lock will be acquired at the table level as a consequence
使用意向排他锁（UIX）更新–当在表中较低层级的资源上同时获取更新锁（U）和意向排他（IX）锁时，结果将在表级获取具有意向排他锁的更新
Schema locks (Sch) – The SQL Server database engine recognizes two types of the schema locks: Schema modification lock (Sch-M) and Schema stability lock (Sch-S)
架构锁（Sch） – SQL Server数据库引擎识别两种类型的架构锁： 架构修改锁（Sch-M）和架构稳定性锁（Sch-S）
- A Schema modification lock (Sch-M) will be acquired when a DDL statement is executed, and it will prevent access to the locked object data as the structure of the object is being changed. SQL Server allows a single schema modification lock (Sch-M) lock on any locked object. In order to modify a table, a transaction must wait to acquire a Sch-M lock on the target object. Once it acquires the schema modification lock (Sch-M), the transaction can modify the object and after the modification is completed and the lock will be released. A typical example of the Sch-M lock is an index rebuild, as an index rebuild is table modification process. Once the index rebuild ID is issued, a schema modification lock (Sch-M) will be acquired on that table and will be released only after the index rebuild process is completed (when used with ONLINE option, index rebuild will acquire Sch-M lock shortly at the end of the process) 执行DDL语句时将获取模式修改锁（Sch-M） ，它将防止在更改对象的结构时访问锁定的对象数据。 SQL Server允许在任何锁定的对象上使用单个架构修改锁（Sch-M）锁。 为了修改表，事务必须等待以获取对目标对象的Sch-M锁。 一旦它获得了模式修改锁（Sch-M），事务就可以修改该对象，并且在修改完成之后，该锁将被释放。 Sch-M锁的典型示例是索引重建，因为索引重建是表修改过程。 发出索引重建ID后，将在该表上获取模式修改锁（Sch-M），并且仅在索引重建过程完成后才释放该锁（与ONLINE选项一起使用时，索引重建将获取Sch-M锁）在流程结束时不久）
- A Schema stability lock (Sch-S) will be acquired while a schema-dependent query is being compiled and executed and execution plan is generated. This particular lock will not block other transactions to access the object data and it is compatible with all lock modes except with the schema modification lock (Sch-M). Essentially, Schema stability locks will be acquired by every DML and select query to ensure the integrity of the table structure (ensure that table doesn’t change while queries are running). 在编译和执行与模式相关的查询并生成执行计划时，将获取模式稳定性锁（Sch-S） 。 该特定的锁不会阻止其他事务访问对象数据，并且与除模式修改锁（Sch-M）之外的所有锁模式兼容。 本质上，每个DML都会选择Schema稳定性锁，并选择查询来确保表结构的完整性（确保查询运行时表不发生变化）。
Bulk Update locks (BU) – this lock is designed to be used by bulk import operations when issued with a TABLOCK argument/hint. When a bulk update lock is acquired, other processes will not be able to access a table during the bulk load execution. However, a bulk update lock will not prevent another bulk load to be processed in parallel. But keep in mind that using TABLOCK on a clustered index table will not allow parallel bulk importing. More details about this is available in Guidelines for Optimizing Bulk Import
批量更新锁（BU） –设计为由TABLOCK参数/提示发出时，批量导入操作可以使用此锁。 获取批量更新锁定后，其他进程将无法在批量加载执行期间访问表。 但是，批量更新锁定不会阻止并行处理另一个批量加载。 但是请记住，在聚集索引表上使用TABLOCK将不允许并行批量导入。 有关此内容的更多详细信息，请参见《优化批量导入的准则》
锁定等级 (Locking hierarchy)
SQL Server has introduced the locking hierarchy that is applied when reading or changing of data is performed. The lock hierarchy starts with the database at the highest hierarchy level and down via table and page to the row at the lowest level
SQL Server引入了在执行读取或更改数据时应用的锁定层次结构。 锁定层次结构从最高层次结构的数据库开始，然后通过表和页面向下到达最低层次结构的行
Essentially, there is always a shared lock on the database level that is imposed whenever a transaction is connected to a database. The shared lock on a database level is imposed to prevent dropping of the database or restoring a database backup over the database in use. For example, when a SELECT statement is issued to read some data, a shared lock (S) will be imposed on the database level, an intent shared lock (IS) will be imposed on the table and on the page level, and a shared lock (S) on the row itself
本质上，每当事务连接到数据库时，就始终在数据库级别上存在一个共享锁。 在数据库级别施加共享锁是为了防止删除数据库或在使用中的数据库上恢复数据库备份。 例如，当发出SELECT语句以读取一些数据时， 共享锁（S）将施加在数据库级别，意图共享锁（IS）将施加在表和页面级，共享锁（S）施加在行本身
In case of a DML statement (i.e. insert, update, delete) a shared lock (S) will be imposed on the database level, an intent exclusive lock (IX) or intent update lock (IU) will be imposed on the table and on the page level, and an exclusive or update lock (X or U) on the row
如果使用DML语句（即，插入，更新，删除），则将在数据库级别上施加共享锁（S），将在表和表上施加意图排他锁（IX）或意图更新锁（IU）。页面级别，并在该行上使用排他或更新锁（X或U）
Locks will always be acquired from the top to the bottom as in that way SQL Server is preventing a so-called Race condition to occur.
始终将始终从上到下获取锁，因为这样SQL Server可以防止发生所谓的竞态条件 。
Now that lock modes and lock hierarchy have been explained, let’s further elaborate on lock modes and how those translate to a lock hierarchy.
既然已经解释了锁定模式和锁定层次结构，那么让我们进一步阐述锁定模式以及它们如何转换为锁定层次结构。
Not all lock modes can be applied at all levels.
并非所有锁定模式都可以应用于所有级别。
At the row level, the following three lock modes can be applied:
在行级别，可以应用以下三种锁定模式：
- Exclusive (X) 独家（X）
- Shared (S) 共享的（S）
- Update (U) 更新（U）
To understand the compatibility of those modes, please refer to the following table:
要了解这些模式的兼容性，请参考下表：
| Exclusive (X) | Shared (S) | Update (U) | |
| Exclusive (X) | ✗ | ✗ | ✗ |
| Shared (S) | ✗ | ✓ | ✓ |
| Update (U) | ✗ | ✓ | ✗ |
| 独家（X） | 共享的（S） | 更新（U） | |
| 独家（X） | ✗ | ✗ | ✗ |
| 共享的（S） | ✗ | ✓ | ✓ |
| 更新（U） | ✗ | ✓ | ✗ |
✓ – Compatible ✗ – Incompatible
✓–兼容✗–不兼容
At the table level, there are five different types of locks:
在表级别，有五种不同类型的锁：
- Exclusive (X) 独家（X）
- Shared (S) 共享的（S）
- Intent exclusive (IX) 意图专用（IX）
- Intent shared (IS) 意向共享（IS）
- Shared with intent exclusive (SIX) 与Intent Exclusive（SIX）共享
Compatibility of these modes can be seen in the table below
这些模式的兼容性可以在下表中看到
| (X) | (S) | (IX) | (IS) | (SIX) | |
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ |
| (IX) | ✗ | ✗ | ✓ | ✓ | ✗ |
| (IS) | ✗ | ✓ | ✓ | ✓ | ✓ |
| (SIX) | ✗ | ✗ | ✗ | ✓ | ✗ |
| （X） | （S） | （九） | （IS） | （六） | |
| （X） | ✗ | ✗ | ✗ | ✗ | ✗ |
| （S） | ✗ | ✓ | ✗ | ✓ | ✗ |
| （九） | ✗ | ✗ | ✓ | ✓ | ✗ |
| （IS） | ✗ | ✓ | ✓ | ✓ | ✓ |
| （六） | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Compatible ✗ – Incompatible
A Schema lock (Sch) is also a table level lock as well, but it is not a data related lock
模式锁（Sch）也是表级锁，但它不是与数据相关的锁
To better understand the compatibility between these lock types, please refer to this table:
为了更好地理解这些锁类型之间的兼容性，请参考下表：
锁升级 (Lock escalation)
In order to prevent a situation where locking is using too many resources, SQL Server has introduced the lock escalation feature.
为了防止锁定使用过多资源的情况，SQL Server引入了锁定升级功能。
Without escalation, locks could require a significant amount of memory resources. Let’s take an example where a lock should be imposed on the 30,000 rows of data, where each row is 500 bytes in size, to perform the delete operation. Without escalation, a shared lock (S) will be imposed on the database, 1 intent exclusive lock (IX) on the table, 1,875 intent exclusive locks (IX) on the pages (8KB page hold 16 rows of 500 bytes, which makes 1,875 pages that hold 30,000 rows) and 30,000 exclusive locks (X) on the rows itself. As each lock is 96 bytes in size, 31,877 locks will take about 3 MB of memory for a single delete operation. Running large number of operations in parallel could require some significant resources just to ensure that locking manager can perform the operation smoothly
如果不进行升级，锁可能需要大量的内存资源。 让我们举一个例子，其中应该对30,000行数据（每行大小为500字节）施加锁以执行删除操作。 如果不升级，将对数据库施加共享锁（S），在表上施加1个意向排他锁（IX），在页面上施加1875个意向排他锁（IX）（8KB页面包含16行500字节，这使1875拥有30,000行的页面）和行本身的30,000个独占锁（X）。 由于每个锁的大小为96个字节，因此一次删除操作将需要31,877个锁，占用大约3 MB的内存。 并行运行大量操作可能需要大量资源，以确保锁定管理器可以顺利执行该操作
To prevent such a situation, SQL Server uses lock escalation. This means that in a situation where more than 5,000 locks are acquired on a single level, SQL Server will escalate those locks to a single table level lock. By default, SQL Server will always escalate to the table level directly, which mean that escalation to the page level never occurs. Instead of acquiring numerous rows and pages lock, SQL Server will escalate to the exclusive lock (X) on a table level
为避免这种情况，SQL Server使用锁升级。 这意味着，在单个级别上获得超过5,000个锁的情况下，SQL Server会将这些锁升级为单个表级别的锁。 默认情况下，SQL Server将始终直接直接升级到表级别，这意味着永远不会升级到页面级别。 而不是获取大量的行和页锁，SQL Server将升级为表级的排他锁（X）。
While this will reduce the need for resources, exclusive locks (X) in a table mean that no other transaction will be able to access locked table and all queries trying to access that table will be blocked. Therefore, this will reduce system overhead but will increase the probability of concurrency contention
虽然这将减少对资源的需求，但表中的排他锁（X）意味着没有其他事务将能够访问锁定的表，并且所有试图访问该表的查询都将被阻止。 因此，这将减少系统开销，但会增加并发争用的可能性。
In order to provide control over the escalation, starting with SQL Server 2008 R2, the LOCK_EXCALATION option is introduced as part of the ALTER TABLE statement
为了提供对升级的控制，从SQL Server 2008 R2开始，将LOCK_EXCALATION选项作为ALTER TABLE语句的一部分引入
|
USE AdventureWorks2014
GO
ALTER TABLE Table_name
SET (LOCK_ESCALATION = < TABLE | AUTO | DISABLE > –One of those options)
GO
|
使用 AdventureWorks2014 GO ALTER TABLE Table_name SET （ LOCK_ESCALATION = < TABLE | AUTO | DISABLE > –其中一个选项） GO |
Each of these options is defined to allow specific control over the lock escalation process:
这些选项中的每一个都被定义为允许对锁升级过程进行特定控制：
Table – This is the default option for any newly created table, as by default SQL Server will always execute lock escalation to the table level, which also includes partitioned tables
表 –这是任何新创建表的默认选项，因为默认情况下，SQL Server始终将锁升级到表级别，该级别也包括分区表
Auto – This option allows the lock escalation to a partition level when a table is partitioned. When 5,000 locks are acquired in a single partition, lock escalation will acquire an exclusive lock (X) on that partition while the table will acquire intent exclusive lock (IX). In case that table is not partitioned, lock escalation will acquire the lock on the table level (equal to the Table option).
自动 –此选项允许在对表进行分区时将锁升级到分区级别。 当在单个分区中获得5,000个锁时，锁升级将在该分区上获得排他锁（X），而表将获得意图排他锁（IX）。 如果未对表进行分区，则锁升级将获得表级别的锁（等于Table选项）。
Although this looks like a very useful option, it has to be used very carefully as it can easily cause a deadlock. In a situation where we have two transactions on two partitions where the exclusive lock (X) is acquired, and transactions tries to access the date from partition used by other transaction, a deadlock will be encountered
尽管这似乎是一个非常有用的选项，但必须非常小心地使用它，因为它很容易导致死锁。 如果我们在两个分区上有两个事务，而这两个分区上已获得了排他锁（X），并且事务试图从其他事务使用的分区中访问日期，则会遇到死锁
So, it is very important to carefully control the data access pattern, if this option is enabled, which is not easy to achieve, and this is why this option is not the default settings in SQL Server
因此，仔细控制数据访问模式非常重要，如果启用了此选项，则很难实现，因此这不是SQL Server中的默认设置的原因
Disable – This option will completely disable lock escalation for a table. Again, this option must be used carefully to avoid the SQL Server lock manager to be forced to use an excessive amount of memory
禁用 –此选项将完全禁用表的锁升级。 同样，必须谨慎使用此选项，以避免SQL Server锁管理器*使用过多的内存
As it can be seen, lock escalation could be a challenge for DBAs. If the application design requires deleting or updating more than 5,000 rows at once, a solution to avoid lock escalation, and the resulting effects, is splitting the single transaction into a two or more transaction where each will handle less than 5,000 rows, as in this way the lock escalation could be evaded
可以看出，锁升级对于DBA可能是一个挑战。 如果应用程序设计要求一次删除或更新超过5,000行，则为避免锁升级以及由此产生的影响，一种解决方案是将单个事务拆分为两个或多个事务，每个事务将处理少于5,000行，如下所示可以避免锁升级的方法
Get info about active SQL Server locks
获取有关活动SQL Server锁的信息
SQL Server provides the Dynamics Management View (DMV) sys.dm_tran_locks that returns information about lock manager resources that are currently in use, which means that it will display all “live” locks acquired by transactions. More details about this DMV can be found in the sys.dm_tran_locks (Transact-SQL) article.
SQL Server提供了动态管理视图（DMV）sys.dm_tran_locks，它返回有关当前正在使用的锁管理器资源的信息，这意味着它将显示事务获取的所有“活动”锁。 有关此DMV的更多详细信息，请参见sys.dm_tran_locks（Transact-SQL）文章。
The most important column used for the identification of the lock are resource_type, request_mode, and resource_description. If needed, more columns as additional resource for information info can be included during troubleshooting
用于标识锁的最重要的列是resource_type，request_mode和resource_description。 如果需要，在故障排除期间可以包括更多列作为信息资源的附加资源
Here is the example of the query
这是查询的示例
|
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> ‘DATABASE’
|
从 sys中选择 resource_type ， request_mode ， resource_description 。 dm_tran_locks WHERE resource_type <> 'DATABASE' |
The where clause in this query is used as the filter on the resource_type to eliminate. from the results, those generally shared locks acquired on the database since these are always present at the database level
此查询中的where子句用作要消除的resource_type的过滤器。 从结果中可以看出，那些通常在数据库上获取的共享锁，因为这些共享锁始终存在于数据库级别
A brief explanation of the three columns presented here:
此处介绍的三列的简要说明：
resource_type – Displays a database resource where the locks are being acquired. The column can display one of the following values: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
resource_type –显示正在获取锁的数据库资源。 该列可以显示以下值之一：ALLOCATION_UNIT，APPLICATION，DATABASE，EXTENT，FILE，HOBT，METADATA，OBJECT，PAGE，KEY，RID
request_mode – displays the lock mode that is acquired on resource
request_mode –显示在资源上获取的锁定模式
resource_description – displays a short resource description and is not populated for all lock modes. Most often the column contains the id of the row, page, object, file, etc
resource_description –显示简短的资源描述，并且未针对所有锁定模式进行填充。 通常，列包含行，页面，对象，文件等的ID
sql server 锁定