February 21, 2013 at 10:30 am
We are experiencing deadlocks on SQL 2008R2. I have tracked down to two sp calls, one is doing insert on TableA, the other is doing Update on TableA by PK. Insert typically winner.
The deadlock graph is like somehting I have not seen before, there are only 2 ovals, winner over x-out loser, and both show info like this:
Server process id: 612
Server batch Id: 0
Execution contect Id: 0
Deadlock priority: 0
Log Used: 6353
Owner Id: 59749077
Transaction descriptor: 0x10056ef7a0
How do I track this down? I normally expect an associated objid - but don't get one here
Tia,
Chris
Chris Becker bcsdata.net
February 21, 2013 at 11:24 am
can you provide the table and index definitions?
Is there a trigger on the table?
The probability of survival is inversely proportional to the angle of arrival.
February 21, 2013 at 11:37 am
Trigger yes, but I disabled and deadlocks continue. Table has about 2.7Mill rows, 1 clustered index on a PK column that is a varchar(50) - values are generated with a rowguid fn.
12 nonclustered indexes
Chris Becker bcsdata.net
February 21, 2013 at 12:06 pm
Thanks for the additional info. PK clustered on a varchar() column (a GUID no less) and 12 non-clustered index.
There are probably some FKs in the table as well. Based on what you told me I would put the clustered index on another column to eliminate the page splits caused by the GUID PK inserts. Hopefully one of the other 12 indexes is more appropriate as a clustered index and would also reduce fragmentation issues. Make the GUID a non-clustered PK index.
also... 12 indexes... makes me wonder how wide this table is... I would consider vertical partitioning of this table if possible to make is smaller and reduce the contention between the inserts and updates. You can make it look like one table with a view if you need to.
Without seeing the table definition and the actual update statement that's about all the advice I can give you.
The probability of survival is inversely proportional to the angle of arrival.
February 22, 2013 at 11:48 am
the other is doing Update on TableA by PK
I'm guessing there are joins involved to help determine which row(s) to update.
Try using just SELECT instead of UPDATE to get the pks that need updated, then doing the update with the pre-determine list of pks and new values.
Note that you should still review the clus index and nonclus indexes on TableA, but this may help prevent the deadlock in the meantime.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 22, 2013 at 11:52 am
Sorry should have include. Ins & Upd sps are from code generator (DooDads), inserts 1 row via sp call, update 1 row by PK. 1 row passed into sps
Chris Becker bcsdata.net
February 22, 2013 at 12:02 pm
Holy cripe! A single, stand-alone INSERT and UPDATE are deadlocking? That table has serious performance issues and should be reviewed immediately. In the meantime, if at all possible, rebuild the table. If you have Enterprise Edition 2008, you can rebuild it online and not have to have any down time.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply