1

I have some triggers to log changes on a table to Log table.

On insert and delete I add row to the Log table and for update I add two rows.

The log table contain identity column, and I want the 2 update rows to be sequential ( by the the id = identity)

for example: assuming the following table:

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

the log table is:

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

And I have 3 triggers to update the 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

In this solution, when I do update from several sessions, there is a chance to several updates in the same time and it break the update sequence. I can see 2 'update from' rows and then two 'update to' rows and I want to prevent it.

The only solution I can think of it to lock the t1_log table in the update trigger using :

Select * from t1_log with (TABLOCKX)

But what if the t1_log have many rows? I guess select * will be slow, and each update will return the selected *.

So I'm using the following:

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

this works better, but I still wonder if there is a fastest way to lock a table?

Improve this question
3
  • 1
    Have you tried yet? Insert into t1_log WITH (TABLOCK) ([log_action],[name],[value])... Commented Jul 22, 2019 at 9:50
  • @DenisRubashkin Thanks, I'll try it Commented Jul 22, 2019 at 11:43
  • 2
    And what does adding TABLOCK to your logging query do to your high throughput transaction system? It may get the desired result but your are going to slow down the inserts as everyone waits for the other sessions to finish logging. You need to remove/change having them appear in perfect unbroken sequence as a requirement. Use the session_id to resolve ties. Add a GUID and have the trigger generate a GUID so you can relate two rows together. But don't use TABLOCK on a high throughput table. Commented Jul 22, 2019 at 12:38

1 Answer 1

Reset to default
3

To further expand on my comment, here is some sample code for you to look at. I don't like the idea of introducing intentional locking on what appears to be a core table in your system. It will effectively slow everyone down to a single-threaded access.

The ideal solution would remove the need to have the update from and update to logging actions in a specific sequence. You can do this by adding a guid or some other identifier to the log table and use that to group the update from and update to actions together.

This example assumes that [Name] is a constant value and won't be changing. 

/** 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
Improve this answer
1
  • No lock is better, but I have other utility which read the log, and it read the log by the identity order. that is why I prefer the lock. thanks. Commented Jul 22, 2019 at 13:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.