question

ns_nataly avatar image
ns_nataly asked

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
transactiondeadlocktriggersconstraints
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you just curious about the locking order and types, or is this causing you an issue?
0 Likes 0 ·
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.
0 Likes 0 ·
ok so what is the problem that it creates? Are you getting any error messages or behaviour?
0 Likes 0 ·
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?
0 Likes 0 ·
sajikumar.vk avatar image
sajikumar.vk answered
Disable the triggers and keep table2 and table3 updation and table4 checks in the same transaction
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, Thanks. That is one of the way... But question is still the same - what will be the Locks order/chain?
0 Likes 0 ·
sajikumar.vk avatar image
sajikumar.vk answered
SELECT OBJECT_NAME(P.object_id) AS TableName, Resource_type, request_session_id FROM 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. '
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sajikumar.vk avatar image
sajikumar.vk answered
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 .
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.