主页 / dba / 问题 / 340824
Accepted
Ross Bush
Asked: 2024-07-09 21:13:47 +0800 CST

确定表是否具有长期运行事务的最佳方法

  • 772

我需要能够迭代所有表并发出TRUNCATE命令，但是我不希望循环因阻塞而停止。本质上，我正在寻找最佳方法来确定SCH-M在发出截断命令之前是否可以获得锁定。

Windows Server 2016 Datacenter 10.0 上的 Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) 标准版（64 位）

sql-server

2 个回答

  1. Best Answer

    测试是否可以获取锁的最佳方法是尝试获取它。

    用于LOCK_TIMEOUT在等待指定时间后放弃，或者在遇到冲突锁时立即放弃（如您喜欢）。用于TRY...CATCH处理任何错误并稍后重试。

    使用锁 DMV 是不可靠的，因为它是内部锁结构的非事务视图。冲突的锁很容易出现在扫描后面。无论如何，完成测试和尝试获取之间存在延迟Sch-M。

    最好尽可能避免扫描 DMV 锁，以免干扰通常已经高度竞争的内部设施。

    • 8

  2. 理论上你可以检查sys.dm_tran_locks是否存在现有锁，否则等待并再次循环。

    DECLARE @startLoop datetime2 = SYSUTCDATETIME();
WHILE 1=1
BEGIN
    IF NOT EXISTS (SELECT 1
        FROM sys.dm_tran_locks l
        JOIN sys.tables t ON t.object_id = l.resource_associated_entity_id
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE l.resource_database_id = DB_ID()
          AND t.name = @tableName
          AND s.name = @schemaName
    )
    BEGIN
        EXEC sp_executesql @truncateCommand;
        BREAK;
    END;

    IF SYSUTCDATETIME() > DATEADD(second, 10, @startLoop)
    BEGIN
        PRINT 'Could not acquire lock for table ' + @tableName;
        BREAK;
    END;

    WAITFOR DELAY '00:00:02';
END;

    但正如其他人所指出的，这种方法并不可靠，因为无法保证在检查和实际截断之间不会有人抢先执行。这还会给锁管理器带来额外的压力。

    如果您有一个与此相同的表，那么您可以使用ALTER TABLE SWITCH和WAIT_AT_LOW_PRIORITY选项快速将表切换到另一个表，然后截断该表。请注意，此选项不需要上述循环。

    ALTER TABLE someTable SWITCH TO someTableStaging
    WITH (WAIT_AT_LOW_PRIORITY (
      MAX_DURATION = 1 MINUTES,
        ABORT_AFTER_WAIT = SELF        -- BLOCKERS is another option
    ) );

TRUNCATE TABLE someTableStaging;

    假设这与您之前关于分区表的问题有关，您可以这样做

    ALTER TABLE someTable SWITCH PARTITION 10 TO someTableStaging
    • 2

相关问题