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.
Table A Table B
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.
asked Nov 12, 2013 at 12:53 PM in Default
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.
answered Nov 12, 2013 at 01:39 PM
Grant Fritchey ♦♦