SQL Server trigger insert causes lock until commit

Question

Having trouble with locks on a related table via triggers.

I'm inserting into table tCatalog (this has a trigger to simply insert a record in another table tSearchQueue). The insert to tCatalog is inside a transaction that has a lot of other functions that sometimes takes several seconds. However, the tSearchQueue table is locked until the transaction can be committed. Is there a way to avoid this?

INSERT INTO [dbo].[tSearchQueue] (Processed, SQL, sys_CreateDate) 
    SELECT      
        0, 'Test ' + cast(CatalogID as varchar(10)), getdate()
    FROM        
        inserted 


BEGIN TRAN t1
    DECLARE @catalogid int

    INSERT INTO tCatalog (ProgramID, sys_CreatedBy, ItemNumber, Description, UOMID) 
    VALUES (233, 1263, 'brian catalog4', 'brian catalog4', 416)

    SELECT @catalogid = SCOPE_IDENTITY()

    INSERT INTO tCustomAttributeCatalog (CatalogID, CustomAttributeID, DefaultValue, DefaultValueRead, sys_CreatedBy) 
    VALUES (@catalogid, 299, 'No', 'No', 1263)

    INSERT INTO tCustomAttributeCatalog (CatalogID, CustomAttributeID, DefaultValue, DefaultValueRead, sys_CreatedBy) 
    VALUES (@catalogid, 300, null, null, 1263)

COMMIT TRAN t1

Why is tSearchQueue locked?? What causes this behavior?? Can you avoid this? — marc_s
@marc_s That's essentially the question I'm asking. It appears to be locked because the transaction for the insert to tCatalog isn't committed yet. I'm trying to avoid that. — btorkelson
Can you show us the insert into TCatalog? That alone however shouldn't lock another table TSearchQueue.... — marc_s
Thanks - but again: no sign of anything being done to the TSearchQueue table ..... without knowing what's happening with that table, there's no way to help, really ... — marc_s

Ben Ben · Accepted Answer · 2016-09-15T12:22:29

It looks like you have a background process which wants to be notified of changes so it can do some sort of re-indexing. If that's the case it's not necessarily wrong that it is blocked, since if the transaction does not commit then it shouldn't index it anyway.

So the sequence is:

Begin transaction
insert tCatalog
- trigger inserts tSearchQueue
Insert some other tables
Perform a long-running operation
Commit transaction.

And the problem is another process wants to read tSearchQueue but cannot since it is locked.

Option 1: Perform the background operation in batches.

If the process is getting behind because it has too few opportunities to read the table, then maybe reading multiple rows at a time would solve the issue. I.e. each time it gets a chance to read the queue it should read many rows, process them all at once, and then mark them all as done together (or delete them as the case may be).

Option 2: Perform the long running operation first, if possible:

begin transaction
perform long-running operation
insert tCatalog
- trigger inserts tSearchQueue
insert some other tables
commit transaction

Other process now finds tSearchQueue is locked only for a short time. Note that if the long-running operation is a file copy, these can be included in the transaction using CopyFileTransacted, or the copy can be rolled back in a "catch" statement if the operation fails.

Option 3: Background process avoids the lock

If the other process is trying primarily to read the table, then snapshot isolation may solve your problem. This will return only committed rows, as they existed at the point in time. Combined with row-level locking this may solve your problem.

Alternatively the background process might read with the NOLOCK hint (dirty reads). This may result in reading data from transactions which are later rolled back. However if the data is being validated in a separate step (e.g. you are just writing the identifier for the object which needs reindexing) this is not necessarily a problem. If the indexing process can cope with entries which no longer exist, or which haven't actually changed, then having spurious reads won't matter.

SQL Server trigger insert causes lock until commit

1 Answers