SQL Server中的默认锁定粒度是什么? [英] What's the default lock granularity in SQL Server?

问题描述

我已经仔细阅读了有关表提示的MSDN ，我没有似乎找不到默认的锁定粒度.假设我有以下查询:

I've thoroughly read MSDN about table hints and I don't seem to find the locking granularity default. Suppose I have the following query:

SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC;

您看到的，我指定了UPDLOCK和READPAST提示，但没有指定任何粒度提示，例如TABLOCK或ROWLOCK.

You see, I specified UPDLOCK and READPAST hints, but not any of granularity hints such as TABLOCK or ROWLOCK.

默认情况下使用哪个粒度锁定级别?

Which granularity lock level is used by default?

推荐答案

没有默认值".粒度(行，页面，(分区|对象))是根据对象的允许选项(allow_page_locks/allow_row_locks)，有关操作意图的信息(探针，扫描，插入)，行集的估计大小和其他因素的数量(隔离级别，文件组是只读的，等等).在大多数情况下，对于单例操作，您将获得行级粒度，而对于扫描，则将获得页面级粒度.您发布的查询可能会采用页面级粒度，因为这是一次扫描.

There is no 'default'. The granularity (row, page, (partition | object)) is computed dynamically based on allowed options for the object (allow_page_locks/allow_row_locks), information about the operation intent (probe, scan, insert), the estimated size of the rowset and a number of other factors (isolation level, is filegroup read only etc). In most cases you will get row-level granularity for singleton operations and page-level granularity for scans. The query you posted is probably going to go with page-level granularity because is a scan.

这篇关于SQL Server中的默认锁定粒度是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！