x

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 at 01:10 PM in Default

avatar image

ns_nataly
1

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

6 days ago 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.

6 days ago ns_nataly

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

6 days ago 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?

5 days ago 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 at 02:15 PM

avatar image

sajikumar.vk
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 at 04:24 PM ns_nataly
(comments are locked)
10|1200 characters needed characters left

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. '

more ▼

answered Nov 14 at 03:21 PM

avatar image

sajikumar.vk
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 at 03:04 PM

avatar image

sajikumar.vk
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x77
x64
x34
x7

asked: Nov 13 at 01:10 PM

Seen: 43 times

Last Updated: 4 days ago

Copyright 2017 Redgate Software. Privacy Policy