Início / dba / Perguntas / 340824
Accepted
Ross Bush
Asked: 2024-07-09 21:13:47 +0800 CST

Melhor maneira de determinar se uma tabela tem uma transação de longa duração

  • 772

Preciso ser capaz de iterar todas as tabelas e emitir um TRUNCATEcomando, porém, não quero que o loop seja interrompido por bloqueio. Essencialmente, estou procurando a melhor maneira de determinar se um SCH-Mbloqueio pode ser obtido antes de emitir um comando truncado.

Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) Standard Edition (64 bits) no Windows Server 2016 Datacenter 10.0

sql-server

2 respostas

  1. Best Answer

    A melhor maneira de testar se um bloqueio pode ser adquirido é tentar adquiri-lo.

    Use LOCK_TIMEOUTpara desistir após esperar um tempo especificado ou imediatamente se um bloqueio conflitante for encontrado, como preferir. Use TRY...CATCHpara lidar com qualquer erro e tente novamente mais tarde.

    Usar os bloqueios DMV não é confiável, pois é uma visão não transacional das estruturas internas de bloqueio. Um bloqueio conflitante pode aparecer facilmente por trás da sua digitalização. De qualquer forma, há um atraso entre a conclusão do teste e a tentativa de aquisição Sch-M.

    É melhor evitar a varredura das fechaduras do DMV tanto quanto possível para evitar interferir no que muitas vezes já é uma instalação interna altamente disputada.

    • 8

  2. Em teoria, você poderia verificar sys.dm_tran_locksse existe um bloqueio existente e, caso contrário, esperar e fazer um loop novamente.

    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;

    Mas, como observado por outros, isso pode não ser confiável, pois não é garantido que alguém não irá impedi-lo entre a verificação e o truncamento. Também coloca pressão extra no gerenciador de bloqueio.

    Se você tiver uma tabela idêntica a esta, poderá usar ALTER TABLE SWITCHa WAIT_AT_LOW_PRIORITYopção de alternar rapidamente a tabela para outra e truncá- la . Observe que esta opção não requer o loop acima.

    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;

    Supondo que isso esteja relacionado à sua pergunta anterior em uma tabela particionada, você pode fazer

    ALTER TABLE someTable SWITCH PARTITION 10 TO someTableStaging
    • 2

relate perguntas