asked
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.
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.
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.
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.
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.
15 People are following this question.
