CODEWITHSUNDEEP
sql-serverdatabasemultithreadingconcurrencytriggers
Ryan
Ryan

Reputation: 619

SQL database triggers thread safe?

I have been wondering about the semantics of concurrency with regards to SQL Server database triggers. I have a database trigger that runs after an update to a table. The trigger sets a 'dateModified' column to the current time. This allows me to always know that the most recent update to the table occurred at X time. Below is what it would look like:

ALTER TRIGGER base.TR_myTrigger ON base.myTable
  AFTER INSERT, UPDATE AS
BEGIN
    DECLARE @dateModified AS DATETIMEOFFSET

    SET @dateModified = SYSDATETIMEOFFSET()
    UPDATE base.myTable
    Set dateModified =  @dateModified
    WHERE id in (SELECT DISTINCT(id) FROM inserted)
END

In my scenario, the table can be updated at any time by any number of threads. Is this trigger safe within a multi-threaded context? If thread A updates the table, and thread B reads from it immediately after, will B see a state of the table with A's updates but not trigger's updates too? Or do triggers protect the table from being read from until all actions + their triggers have been performed?

Any insight into what exactly is happening under the hood with SQL database triggers would be appreciated. Thanks!

Upvotes: 6

Views: 3704

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

If thread A updates the table, and thread B reads from it immediately after, will B see a state of the table with A's updates but not trigger's updates too? Or do triggers protect the table from being read from until all actions + their triggers have been performed?

What this boils down to is: are the underlying operation and the trigger operation treated as an atomic unit or are they separate? The answer is: atomic. That is, you'll never see the results of the insert or update (in your case) and not see the dateModified column being dealt with via the trigger. That is, they both commit in one transaction. From the documentation:

The trigger and the statement that fires it are treated as a single transaction...

Upvotes: 3

usr
usr

Reputation: 171178

Triggers do not have any special properties regarding concurrency. They run as if you had manually executed that code.

Your trigger is safe because all rows that you read and write have been X-locked already by the triggering DML.

Upvotes: 0

Related Questions