（會話）鎖定表的最快方法是什麼？

我有一些觸發器來記錄表上的更改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 

這效果更好，但我仍然想知道是否有最快的方法來鎖定表？

為了進一步擴展我的評論，這裡有一些範常式式碼供您查看。我不喜歡在系統中看似核心的表上引入有意鎖定的想法。它將有效地減慢每個人的單執行緒訪問速度。

理想的解決方案將消除以特定順序更新和更新日誌操作的需要。您可以通過將 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

引用自：https://dba.stackexchange.com/questions/243430