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
Disable the triggers and keep table2 and table3 updation and table4 checks in the same transaction
answered Nov 13, 2017 at 02:15 PM
OBJECT_NAME(P.object_id) AS TableName,
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. '
answered Nov 14, 2017 at 03:21 PM
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 .
answered Nov 16, 2017 at 03:04 PM