Sql-Server
(會話)鎖定表的最快方法是什麼?
我有一些觸發器來記錄表上的更改
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