April 6, 2009 at 1:12 pm
I am trying to track down a deadlock issue and have used SQL Profiler to capture a deadlock.xdl file. What is puzzling to me is that it appears the same resource is locked two different processes with an X lock. I would think this is not possible. Any thoughts on what is going on? Below shows the element from the deadlock file. Both keylocks seem to be holding an X lock of type ROW on object "72057594129547264" in an index page. I have attached a JPG showing the deadlock graph, and the complete .xdl file as well.
Thank you for any help you can provide.
<resource-list>
<keylock hobtid="72057594129547264" dbid="5" objectname="fff.dbo.fetch_primary_results" indexname="PK_fetch_primary_results" id="locka256240" mode="X" associatedObjectId="72057594129547264">
<owner-list>
<owner id="process85b2e8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process85b6a8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594129547264" dbid="5" objectname="fff.dbo.fetch_primary_results" indexname="PK_fetch_primary_results" id="locka3bfb80" mode="X" associatedObjectId="72057594129547264">
<owner-list>
<owner id="process85b6a8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process85b2e8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
April 6, 2009 at 2:20 pm
Okay, I think I understand what is going on. The X locks are actually on different objects because they are Key-range locks on the index. The range is shown by the data between the parenthesis. So each of the inserts is taking an X lock on a range within the PK. The inserts are working fine, but the trigger that is doing the Update is causing a key lookup (bookmark lookup) on the same resource as the X locks taken earlier causing the deadlock.
So you are inserting a row, then using the Primary Key to update the row.
This article[/url] actually has good explanation of what is happening.
I have to ask.
Why you are doing an insert into a table and then using a trigger to update columns in the row you just inserted? Why not just include the columns in the Insert Statement?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 6, 2009 at 5:11 pm
Thank you very much for your quick and detailed response.
So you are suggesting that the X locks are for different parts of the PK index of the table I am inserting for? Since these are INSERTs, not UPDATEs, I guess they are locking areas to put the data into, not rows that already exist? That part makes sense.
But the UPDATE is locking the same part of another resource, probably because the WHERE clause is written poorly and instead of pointing just to the row being inserted, it is pointing to a range of rows, which happens to include the row created by the other INSERT. Since the other INSERT row is already locked because the transaction has not yet been committed, deadlock ultimately results.
So one solution (purely hypothetical as you will see below) would be to alter the WHERE clause to limit the the UPDATE only to the row inserted, which was the original intent.
You ask why an INSERT followed by an UPDATE in a trigger? Unfortunately, this was not my idea. I coded the original INSERT SQL in the app. I then got a call from a user complaining about the deadlock situation. When I went to investigate, I found that they wanted to have a "date_written" field for each row inserted, so they thought they could get this without a code change simply by adding a trigger. Thanks to your response, the trigger will be removed and we will provide a cleaner solution.
Once again, thanks for your time.
April 7, 2009 at 12:45 am
Ummm... it may not be necessary to remove the trigger and there is some great value in having an automated "modified date" or whatever you want to call it. Might be able to work it out but we'd need to see the INSERT and the Trigger code.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 1:03 am
I could possibly see the value if multiple apps were writing to the database. But we have a single app in control of this table, and it can easily add the current time to the SQL it is executing. Also, we strive to be database-agnostic (we support 3 different databases), so we try to keep as much logic in the application code as possible and avoid triggers and other stored procedures. Keeping the logic in the code also makes maintenance easier -- we only have to look at the application code to know what is happening.
I know that there are different philosophies with regard to this issue -- some would argue to put as much logic in the database as possible to avoid different client apps modifying the data in different ways. For our app, we felt code-only works the best. (One of the downsides, however, is that there is not as much awareness of database issues by the programming staff as there could be.)
By the way, I am pretty sure the UPDATE query is updating a range of values. Would have to refresh my SQL Server knowledge of triggers to know if could be modified to just update the affected row. If the primary key is an identity column, is the value of that column available in the INSERT trigger? Would an UPDATE statement in the trigger "see" the inserted row if it used this value in the WHERE clause?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply