什么是行锁、页锁和表锁?什么时候被收购? [英] What are row, page and table locks? And when they are acquired?
问题描述
我想了解有关数据库引擎获取的不同类型的锁的更多信息.
I want to know more about different types of locks Database Engine aquires.
- 什么是
- 行锁
- 页面锁定
- 表锁
请帮助我理解这些概念.
Please help me understand these concepts.
推荐答案
Row Lock
行锁是 SQL Server 中可能的最低级别的锁定粒度.这意味着一个或多个特定行将被锁定，并且相邻行仍可用于并发查询锁定.
A row lock is the lowest level of granularity of locking possible in SQL Server. This means one or more specific rows will be locked, and the adjacent rows are still available for locking by concurrent queries.
页面锁定
即使您的查询只需要页面中的 10 个字节，SQL Server 中的页面锁也将锁定价值 8K 的数据.因此，您的查询将锁定您未在查询中请求的其他数据.
A page lock in SQL Server will lock 8K worth of data even when your query only needs 10 bytes from the page. So your query will lock additional data which you do not request in your query.
Hobt Lock
使用SQL Server 表分区"对表进行分区时单个分区可能会被锁定(Hobt 代表 Heap 或 B-Tree)
When a table is partitioned with "SQL Server Table partitioning" it is possible a Single partition will be locked (Hobt stands for Heap or B-Tree)
注意:锁升级到 HOBT 锁默认是禁用的.运行 ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO) 以启用 HOBT 锁升级.
Note: Lock escalation to HOBT locks is disabled by default. run ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO) to enable HOBT lock escalation.
表锁
表锁将锁定整个表.
什么是数据页
Microsoft SQL Server 将其所有数据组织在数据页"中，可容纳 8K 的数据.这意味着对于 SQL Server 中的任何数据访问都会读取 8K 信息.
Microsoft SQL Server organizes all it's data in "Data Pages" which can hold 8K worth of data. This means that for any data access in SQL Server 8K information will be read.
数据页只能包含一个表中的信息，页面的布局是MSDN 上有很好的文档
Data pages can only contain information from one table and the layout of a page is well documented on MSDN
SQL Server 将始终读取完整的数据页这一事实也让您了解为什么它更喜欢使用页级锁.页级锁的含义是您可能锁定的数据比您想象的要多得多.
The fact that SQL Server will always read a complete data page also gives you an idea why it prefers to use page level locks. The implication of page level locks are that you might lock a lot more data than you think.
例如，假设我们有一个总记录大小为 1024 字节的表，在字段 ID 上有一个聚集索引.当我们运行以下查询时:SELECT * from MyTable (xlock) where ID = 123 不仅该记录将被锁定，而且(取决于页面填充)最多 3 个附加记录也将被锁定.
For example, assume we have a table with a total record size of 1024 bytes with a clustered index on the field ID. When we run the following query: SELECT * from MyTable (xlock) where ID = 123 not only that record will be locked, but (depending on the page fill) maximum 3 additional records will be locked as well.
什么时候获取这些锁
查询管理器将解析查询，并确定所需的锁并向锁管理器请求.SQL Server 将尝试在性能和争用之间取得平衡，以确定锁定策略.
A query will be parsed by the query governor and the required locks will be determined and requested from the lock manager. SQL Server will try to make a balance between performance and contention to determine the locking strategy.
SQL Server 还遵循锁升级"系统，当获取超过 5000 个某种类型的锁时，该系统会降低锁的粒度.有关详细信息，请参阅这篇关于锁升级的文章.
SQL Server also follows a "lock escalation" system which will reduce the granularity of locking when more than 5000 locks of a certain type are being acquired. See this article on lock escalation for more information.
可以使用锁定提示来调整这种行为，强调提示，在查询中，您可以为每个表指定您喜欢的锁定类型.SQL Server 将尝试满足您的请求，但它仍会应用锁升级.
This behavior can be tweaked using locking hints with stress on hints, in a query you can specify per table what kind of locks you would prefer. SQL Server will try to honor your request, but it will still apply lock escalation.
这篇关于什么是行锁、页锁和表锁?什么时候被收购?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
