I'm trying to find information on the effects of RETURN inside a trigger. The only documentation I can find on it is that it "releases" the trigger.
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017#optimizing-dml-triggers
The reason I ask is that a recently added trigger is causing some deadlocking issues, even when the trigger is empty.
These examples are pretty nonsensical (why retrieve data that was just inserted), but that's just the way it works. The code that generates these statements is pretty damn old.
Body of the "empty" trigger
BEGIN
SET NOCOUNT ON;
ENDEmpty trigger enabled on Table1
BEGIN TRANSACTION
INSERT INTO Table1.... -- Table1 becomes locked until transaction is committed
SELECT * FROM Table1 WHERE ID = X... -- deadlock
INSERT INTO Table2...
COMMIT TRANSACTIONEmpty trigger disabled
BEGIN TRANSACTION
INSERT INTO Table1.... -- Table1 DOES NOT BECOME LOCKED
SELECT * FROM Table1 WHERE ID = X...
INSERT INTO Table2...
COMMIT TRANSACTIONAdding RETURN at the end of the trigger releases the lock, preventing the deadlock.
Is this safe? The only thing I can think of is if the trigger modifies the table its on, releasing the lock may cause a dirty read.
2条答案
按热度按时间vmdwslir1#
To answer your question, yes, it is perfectly safe to use RETURN inside a trigger. It is often used at the start of complex triggers to exit immediately if there are no rows to process. Like this;
However, there must be something else going on to cause a deadlock. See Gail Shaw's excellent blog SQL Server Deadlocks by Example and take a look at the deadlock graph to see if it gives you some more clues to where the problem might be.
rhfm7lfc2#
The lock on the table is placed at the time you do the insert because you also have "begin transaction" before the insert. That is true with or without a trigger. I don't think that the "return" releases the lock. The lock is released at the "commit transaction" below only after the trigger runs, then the select runs, and then the second insert, which locks table2, also succeeds.
If you disable the trigger, it doesn't run, so the whole block of code runs a bit faster and uses less resources. It is likely that that is why you don't see the deadlock with a disabled trigger.
Enabling the trigger to add "return" should just make it exit. It should not be making a difference. If it truly does (and you don't see a difference just because you are doing something different when you run the test again), that is odd and not expected.
Disabling/not having a trigger vs having a trigger changes one thing: if there is no explicit transaction before the insert, the presence of the trigger will cause SQL server to start an implicit transaction at the insert and commit it at the end of the last trigger on that table and operation. The only visible effect should be that there are again more resources used because of the transaction being present, and that you can roll back the changes inside the trigger if you need to. But in your example, there already is a "begin transaction", so the presence of the trigger does not affect the already started transaction.
In general, deadlocks are time-dependent (i.e. depends how many concurrent threads or processes are accessing the database, how quickly each process finishes its database part and the client part, and so on. So, if you are experiencing deadlocks, this means that you have concurrently running code that has a potential deadlock situation (e.g. two locks in a certain order in some code and in the reverse order in other concurrently running code, like insert Table2 then insert Table1), and you should either change it so there is no deadlock situation, or you should expect it and handle it (e.g. by repeating the failed transaction that was the deadlock victim).