x

Dead lock due to stored procedure and trigger accessing same table in SQL server 2008

In SQL server 2008, there is a stored procedure (SP1) which will insert one row in table A, There is a trigger (After insert) on same table A, which will call other stored procedure (SP2) which will eventually access some rows of table A, do summation on quantity column and update quantity column in table B.

Now when I am inserting rows in SP1 from multiple sessions, It is going in dead lock. I have took TABLOCKX on insert query in SP1 and resolved the issue, But I have questions if I take a lock on insert statement, does it take a lock on entire trigger or just only that insert statement. Please provide any document or some program which proves this.

My all stored procedures and triggers execute under BEGIN ... END block.
I don't have much db level access, so i can't run admin level queries.

Table A Table B
Column1 Column2 Column3 Quantity Column1 Column2 Column3 Quantity A B C 5 A B C 35 A B C 20 A B F 30 A B C 10
A B F 10
A B F 20

When insert happen in Table A by SP1, trigger on table A will call SP2, which will update table B.

Please let me know the answer with appropriate doc or program.

more ▼

asked Nov 12, 2013 at 12:53 PM in Default

avatar image

Gopu_CS
10 2 2 5

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

1 answer: sort voted first

I can't provide specifics for you without seeing the query and structures in question. Deadlocks are all about access and performance. You need to ensure that you have access set in such a way that you don't have two processes dependent on the same records for updates. It sounds like your trigger is doing exactly that. You also need to ensure that any transaction you open, completes as soon as possible.

One question you have "does it take a look on entire trigger" is a little confusing. Locks are taken on the pages of the data, not on the queries being performed against that data. There are shared locks and there are exclusive locks (along with a bunch of other lock types, but we'll focus down for a moment). Shared locks are taken to read from the data. Exclusive locks are taken to update data. A transaction can have both kinds of locks at the same time. A shared lock will prevent another process from taking an exclusive lock, but not from another process also getting a shared lock. So, when a given process reads data, it will take a shared lock. If it also updates data, if there is no other process locking the page, it'll take an exclusive lock. Then, if it needs to access more data, it will go after more locks.

In the mean time, another process can come along and take out locks on stuff it's reading (which may be pages the other process needs). And it can take out it's own exclusive locks. The deadlock occurs when the two both need access to pages that the other has and neither one can release locks until it's transaction completes.

By setting up a trigger to access data from the same place that you're writing data to, you're potentially looking at deadlocks. Instead, I'd suggest making your code do two statements, one to update table A and another to update table B. Triggers are problematic constructs frequently, but then updates to table B are dependent on data from Table A that is being locked by the process.... you're just going to hit blocking.

Other possible solutions include, changing your isolation level to read committed snapshot. This radically reduces resource contention, especially around shared locks. You may also need to tune the queries.

more ▼

answered Nov 12, 2013 at 01:39 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Isolation level is read committed not read committed snapshot. Actually Dead lock is resolved, by taking table level exclusive lock on insert statement.

But here real question is for e.g

SP1 have insert statement with TABLOCKX on table A. Once row is inserted in table A, trigger is fired which will call SP2 and which will access some of the rows of table A, including newly inserted one and update some table B.

so, my question is if i take a lock on insert statement in SP1, so shall it take lock on all sql statements contained in trigger (which is eventually all sql statement in SP2)

Can you please provide any code or doc for a proof.

Nov 12, 2013 at 02:09 PM Gopu_CS

Thanks for your answer, it helped at some level.

In my query, Isolation level is read committed not read committed snapshot. Actually Dead lock is resolved, by taking table level exclusive lock on insert statement.

But here real question is consider below case

for e.g

SP1 have insert statement with TABLOCKX on table A. Once row is inserted in table A, trigger is fired which will call SP2 and which will access some of the rows of table A, including newly inserted one and update some table B.

so, my question is if i take a lock on insert statement in SP1, so shall it take lock on all sql statements contained in trigger (which is eventually all sql statement in SP2)

Can you please provide any code or doc for a proof.

Nov 12, 2013 at 02:10 PM Gopu_CS

The way to confirm it would be to test it out. You can use extended events to watch for the lock_acquired event and the lock_released event. That will show you what's happening.

Nov 12, 2013 at 02:32 PM Grant Fritchey ♦♦
(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:

x479
x65
x34

asked: Nov 12, 2013 at 12:53 PM

Seen: 3090 times

Last Updated: Nov 13, 2013 at 03:12 AM

Copyright 2018 Redgate Software. Privacy Policy