主页 / dba / 问题 / 311719
Accepted
variable
Asked: 2022-05-06 05:42:01 +0800 CST

是否使用 nolock 块写入器从堆（没有聚集索引的表）中选择数据？

  • 772

我尝试复制这里给出的场景：https ://www.sqlskills.com/blogs/paul/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan/

创建了 2 个表 - 带有聚集索引的 table1，没有任何索引的 table2。

当我在 table1 上编写选择查询with (lock)时，应用了 Sch-S 锁。同样，当我在 table2 上编写相同的查询时，它也有一个 Sch-S 锁。

两种情况都没有应用 S 锁，如上面的链接所示。

要检查锁，我使用以下查询：

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');

是否使用 nolock 块写入器从堆（没有聚集索引的表）中选择数据？

sql-server locking

2 个回答

  1. 它对我有用。如果您在表扫描之前刷新页面缓存以减慢速度，这会有所帮助。例如

    dbcc dropcleanbuffers
go
select avg(somecol) ap
from someheap with (nolock)

    并检查查询计划以确保您获得无序表扫描。

    观察快速事物的另一个技巧是引入一个浪费时间的标量值函数，例如：

    create or alter function dbo.delay(@ms int)
returns int as
begin
   declare @start datetime2 = sysdatetime()
   while datediff(ms,@start,sysdatetime()) < @ms
   begin
     declare @h bigint = checksum(0x29374190273401923470912384709123)
   end
   return @ms
end

    因此，只要您有流媒体计划，您就可以尽可能慢地进行扫描。

    在 AdventureWorksDW 中，我创建了一个像这样的大堆

    select top 25000000 s.* 
into someheap 
from FactInternetSales s,  FactInternetSales s2

    然后跑

    dbcc dropcleanbuffers
go
select avg(UnitPrice) ap
from someheap with (nolock)

    这花了足够长的时间来切换和运行

    SELECT
    [resource_type],
    [resource_subtype],
    [resource_associated_entity_id],
    [request_mode]
FROM sys.dm_tran_locks
WHERE
    [resource_type] != N'DATABASE';

    并查看 BULK_OPERATION 锁

    在此处输入图像描述

    • 5
  2. Best Answer

    是否使用 nolock 块写入器从堆（没有聚集索引的表）中选择数据？

    正如 Paul Randal 在提供的链接中所解释的那样，只有执行批量加载的编写器*：

    BULK_OPERATION 锁可防止在 NOLOCK 扫描（或版本化扫描）发生时发生批量加载。它是在 S 模式下获取的，因此可能会发生多个并发扫描。大容量加载将在 IX 模式下获取锁，防止任何 NOLOCK 或版本化扫描开始，直到大容量加载完成。

    *这里的bulk load是指在使用bcp, BULK INSERT, 或SSIS时允许多个并发bulk load到没有索引的堆表的特殊机制。在这些情况下，使用BU表锁而不是排他锁。有关详细信息，请参阅数据加载性能指南。

    特殊BULK_OPERATION锁与“普通”插入/更新/删除所采用的“普通”表、页或行锁不冲突。

    批量加载聚簇表是可能的，但在BULK_OPERATION不需要锁的情况下使用不同的机制。

    • 3

相关问题