What will be the Chain of locks(sql2016):the table has trigger and check constraint to another table

Hi, We have a lot of locks in the system which involved 4 tables but not sure how to resolve it without changing business rules.

Insert into Table1 happened within Begin - Commit transaction.

Table1 has 2 triggers which updates table2 and table3. It also has Check constraint which check table4.

Table 2 also has a trigger which will fire on update and constraint which check Table4.

Can somebody explain the order and types of the locks for above situation.

Thank you. Regards, Natalia

more ▼

asked Nov 13, 2017 at 01:10 PM in Default

avatar image


Are you just curious about the locking order and types, or is this causing you an issue?

Nov 14, 2017 at 04:40 PM Kev Riley ♦♦

That is actually causing problems. Unfortunately, table1 get accessed via another application as well (SSIS and 3rd party app.,) so triggers and constraints have to stay.

Nov 14, 2017 at 05:39 PM ns_nataly

ok so what is the problem that it creates? Are you getting any error messages or behaviour?

Nov 15, 2017 at 09:55 AM Kev Riley ♦♦

Hi, situation is following: Insert into Table1 fired 2 triggers. Trigger 1 updates Table2 and Trigger2 updates Table3. BUT Table2 has also a trigger Table2.trigger1 which Updates the same field in Table3 as Table1.Trigger2 Problem is that data into Table 1 and Table 2 may be inserted form different parts of the system and not always into both at the same time. So Table 3 have to be updated for changes in both Tables. Any advise?

Nov 15, 2017 at 04:59 PM ns_nataly
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Disable the triggers and keep table2 and table3 updation and table4 checks in the same transaction

more ▼

answered Nov 13, 2017 at 02:15 PM

avatar image

1 1

Hi, Thanks. That is one of the way... But question is still the same - what will be the Locks order/chain?

Nov 13, 2017 at 04:24 PM ns_nataly
(comments are locked)
10|1200 characters needed characters left


OBJECT_NAME(P.object_id) AS TableName,




sys.dm_tran_locks L

join sys.partitions P

ON L.resource_associated_entity_id = p.hobt_id

WHERE OBJECT_NAME(P.object_id) in( 'Table1,'Table2','Table3','Table4')

use this query to get the table locks and analyse the issue. '

more ▼

answered Nov 14, 2017 at 03:21 PM

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left

as per your explanation, need to track the table2 changes which are executed outside of the Table1.Trigger1,since table1 changes will always impact table2 .So modify the Table2.trigger1 code to check Table1 insertion happened or not before updating table3 .

more ▼

answered Nov 16, 2017 at 03:04 PM

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 13, 2017 at 01:10 PM

Seen: 56 times

Last Updated: Nov 16, 2017 at 03:04 PM

Copyright 2018 Redgate Software. Privacy Policy