Sql-server – the fastest way to (session) lock table

lockingprimary-keysql servertriggerupdate

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?

Best Answer

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
Related Question