Início / dba / Perguntas / 243430
Accepted
SHR
Asked: 2019-07-23 00:41:44 +0800 CST

Qual é a maneira mais rápida de bloquear a tabela (sessão)?

  • 772

Eu tenho alguns gatilhos para registrar alterações em uma tabela paraLog table.

Ao inserir e excluir, adiciono linha ao Log tablee, para atualização, adiciono duas linhas.

A tabela de log contém a coluna de identidade e quero que as 2 linhas de atualização sejam sequenciais (pelo id = identity)

por exemplo: assumindo a seguinte tabela:

Create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))

a tabela de logs é:

Create table t1_log 
([log_id] identity(1,1),[log_ts] DateTime default GETDATE(),
  [log_action] varchar(20), log_session_id int default @@SPID,
  [name] nvarchar(40), value nvarchar(max))

E tenho 3 triggers para atualizar o log:

Create trigger t1_ins on t1 After Insert as
begin
    Insert into t1_log([log_action],[name],[value]) select 'insert', [name], [value] from inserted 
end 
Go
create trigger t1_del on t1 After delete as
begin
    Insert into t1_log([log_action],[name],[value]) select 'delete', [name], [value] from deleted 
end 
Go
create trigger t1_upd on t1 After update as
begin
    Insert into t1_log([log_action],[name],[value]) 
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION 
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]
end 
Go

Nesta solução, quando eu atualizo de várias sessões, há a chance de várias atualizações ao mesmo tempo e quebrar a sequência de atualização. Posso ver 2 linhas 'atualizar de' e depois duas linhas 'atualizar para' e quero evitar isso.

A única solução que consigo pensar é bloquear a tabela t1_log no gatilho de atualização usando:

Select * from t1_log with (TABLOCKX)

Mas e se o t1_log tiver muitas linhas? Acho select *que será lento e cada atualização retornará o arquivo *.

Então estou usando o seguinte:

create trigger t1_upd on t1 After update as
begin
    declare @tt
    Begin transaction

    select @tt=1 from t1_log with (TABLOCKX)

    Insert into t1_log([log_action],[name],[value]) 
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION 
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]

     Commit trasaction
end

isso funciona melhor, mas ainda me pergunto se existe uma maneira mais rápida de bloquear uma mesa?

sql-server trigger

1 respostas

  1. Best Answer

    Para expandir ainda mais o meu comentário, aqui está um código de exemplo para você analisar. Não gosto da ideia de introduzir bloqueio intencional no que parece ser uma tabela central em seu sistema. Ele irá efetivamente diminuir a velocidade de todos para um acesso de thread único.

    A solução ideal eliminaria a necessidade de atualizar e atualizar as ações de log em uma sequência específica. Você pode fazer isso adicionando um guid ou algum outro identificador à tabela de log e usá-lo para agrupar as ações de atualização e atualização para.

    Este exemplo assume que [Name] é um valor constante e não será alterado. 

    /** Build up our table and triggers

    Note that I have consolidated the trigger logic into a single trigger
    and the additional column on T1_Log
    **/
CREATE TABLE dbo.T1
    (
    [Name] NVARCHAR(40) PRIMARY KEY NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

CREATE TABLE dbo.T1_Log
    (
    Log_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , Log_ActionGUID UNIQUEIDENTIFIER NOT NULL
    , Log_TS DATETIME DEFAULT GETDATE() NOT NULL
    , Log_Action VARCHAR(20) NOT NULL
    , Log_Session_ID INT DEFAULT @@SPID NOT NULL
    , [Name] NVARCHAR(40) NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

GO

CREATE OR ALTER TRIGGER trg_T1_Log ON dbo.T1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    DECLARE @Log_ActionGUID UNIQUEIDENTIFIER = NEWID()


    ;WITH CTE_Actions AS
        (
        SELECT Log_Action = CASE    WHEN D.[name] IS NULL THEN 'insert'
                                    WHEN I.[name] IS NULL THEN 'delete'
                                    ELSE 'update from'
                                    END
            , Log_Sort = 1
            , [Name] = COALESCE(D.[name], I.[name])
            , [Value] = COALESCE(D.[Value], I.[Value])
        FROM inserted AS I
            FULL OUTER JOIN deleted AS D ON D.[Name] = I.[Name]
        UNION ALL
        SELECT 'update to' AS Log_Action
            , Log_Sort = 2
            , I.[Name]
            , I.[Value]
        FROM inserted AS I
        WHERE EXISTS (SELECT TOP (1) 1 FROM deleted AS D WHERE D.[name] = I.[name])
        )
    INSERT INTO dbo.T1_Log
        (Log_ActionGUID, Log_Action, [Name], [Value])
    SELECT @Log_ActionGUID
        , Log_Action
        , [Name]
        , [Value]
    FROM CTE_Actions AS A
    ORDER BY Log_Sort

END

GO

/** Test Statements **/
INSERT INTO dbo.T1
    ([Name], [Value])
VALUES 
    ('John Smith', 'Smith Value 1')

UPDATE dbo.T1
SET [Value] = 'New Value'
WHERE [Name] = 'John Smith'

DELETE FROM dbo.T1 WHERE [Name] = 'John Smith'

/** Show Log Data **/
SELECT * FROM dbo.T1_Log
ORDER BY Log_TS, Log_ActionGUID

/** Cleanup **/
DROP TABLE IF EXISTS dbo.T1_Log
DROP TABLE IF EXISTS dbo.T1
    • 3

relate perguntas