主页 / dba / 问题 / 243430
Accepted
SHR
Asked: 2019-07-23 00:41:44 +0800 CST

（会话）锁定表的最快方法是什么？

  • 772

我有一些触发器来记录表上的更改Log table.

在插入和删除时，我将行添加到Log table更新中，我添加了两行。

日志表包含标识列，我希望 2 个更新行是连续的（通过 id = identity）

例如：假设下表：

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

日志表是：

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))

我有 3 个触发器来更新日志：

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

在此解决方案中，当我从多个会话进行更新时，有机会同时进行多个更新，这会破坏更新顺序。我可以看到 2 个“更新自”行，然后是两个“更新到”行，我想阻止它。

我能想到的唯一解决方案是使用以下方法将 t1_log 表锁定在更新触发器中：

Select * from t1_log with (TABLOCKX)

但是如果 t1_log 有很多行呢？我猜select *会很慢，每次更新都会返回选中的 *.

所以我正在使用以下内容：

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

这效果更好，但我仍然想知道是否有最快的方法来锁定表？

sql-server trigger

1 个回答

  1. Best Answer

    为了进一步扩展我的评论，这里有一些示例代码供您查看。我不喜欢在系统中看似核心的表上引入有意锁定的想法。它将有效地减慢每个人的单线程访问速度。

    理想的解决方案将消除以特定顺序更新和更新日志操作的需要。您可以通过将 guid 或其他一些标识符添加到日志表中来执行此操作，并使用它将更新从和更新到操作分组在一起。

    此示例假定 [Name] 是一个常量值并且不会更改。 

    /** 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

相关问题