Dead lock in sql server 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dead lock in sql server 2008

Author

Topic

Gopu_CS
Starting Member

2 Posts

Posted - 2013-11-12 : 08:01:40

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
Column1 Column2 Column3 Quantity
A B C 5
A B C 20
A B C 10
A B F 10
A B F 20

Table B
Column1 Column2 Column3 Quantity
A B C 35
A B F 30

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

Please let me know, 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 documents or some program which proves this.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-12 : 08:44:25

The insert statement and the insert trigger are considered to be part of the same transaction. So SQL Server will acquire an exclusive lock before the insert and hold on to that lock until the trigger code is completed. There are more details to it, but in summary that is what it does.

That said, I didn't follow what you meant by taking a lock on the "entire trigger". SQL Server acquires locks on tables, rows or extents, not on triggers.

Gopu_CS
Starting Member

2 Posts

Posted - 2013-11-12 : 09:01:23

Thanks James for your reply.

I know SQL Server acquires locks on tables, rows or extents, not on triggers.
I mean for taking lock on the "entire trigger" is lock on all sql statements contained in that trigger.

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 all sql statement in SP2)

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-12 : 09:53:04

In your example, because you are acquiring exclusive lock on TableA, that table will remain locked until the end of the transaction, including within the trigger.

Since table B is accessed only in the trigger, a lock on that table (or rows of that table) will be acquired only when SQL server is ready to update that table. So, the answer to your question is negative. Table B may remain unlocked for some duration of your update transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources