Nasty Deadlock Issues
-
I'm trying to get to the bottom of a deadlock and it's driving me nuts. I hope someone can point me in the right direction.
I will try and explain the situation. Firstly, this deadlock is not random and rerunning the process causes this over and over again. Secondly this only happens to a small number of policies, but always happens to those policies. Thirdly this happens on any server, we restore the production db onto our dev server at night and we can replicate this issue again.
The deadlock graph (attached) doesn't show the sql statement concerned, only the objects. I beleive this is due to the fact that the data access layers creates temp stored procs and then executes those ie: CREATE PROCEDURE #jtds00001 ......, EXEC #jtds00001.
Here are the table definitions:
CREATE TABLE [dbo].[TBL_REQUESTVALUES_OBJECT_PROPERTY]([FK_PROPERTY_ID] [numeric](19, 0) NOT NULL,
[PROPERTY_VALUE_CLASS_NAME] [varchar](255) NULL,
[PROPERTY_VALUE] [nvarchar](max) NULL,
CONSTRAINT [PK_TBL_REQUESTVALUES_OBJECT_PROPERTY] PRIMARY KEY CLUSTERED
([FK_PROPERTY_ID] ASC)
)
GO
CREATE TABLE [dbo].[TBL_PROPERTY](
[ID] [numeric](19, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[KIND_ID] [int] NOT NULL,
[FK_STRUCTURED_ACTUAL_ID] [numeric](19, 0) NULL,
[SPECIFICATION_ID] [numeric](19, 0) NOT NULL,
CONSTRAINT [PK_TBLPROPERTY] PRIMARY KEY NONCLUSTERED
([ID] ASC),
CONSTRAINT [UC_TBL_PROPERTY] UNIQUE NONCLUSTERED
([KIND_ID] ASC,[FK_STRUCTURED_ACTUAL_ID] ASC),
CONSTRAINT [UQ_TBL_PROPERTY] UNIQUE CLUSTERED
([FK_STRUCTURED_ACTUAL_ID] ASC,[KIND_ID] ASC,[ID] ASC)
)
GO
I believe I have identified the offending statements as I have had to wade through thousands of lines of a trace due to a busy dev server. I have attached the query plan for these statements.
I hope someone can assist.
Thanks.
-
Having the contents of the temporary procedures and the "offending" statements would help. Also, I am not able to view the SQL plans that are attached; I click the link and get a blank screen. Do you have these in another format?
-
I also get a blank screen when clicking the link, but I can save to disk and they open fine.
Here are the statements:
UPDATETBL_PROPERTYSETKIND_ID = 1234
,FK_STRUCTURED_ACTUAL_ID = 5678
,SPECIFICATION_ID = 910
WHEREID = 1000
SELECTp.FK_STRUCTURED_ACTUAL_ID AS FK3_0_1_,p.ID AS ID1_
,p.KIND_ID AS KIND2_1_
,p.ID AS ID3_0_
,p.KIND_ID AS KIND2_3_0_
,p.FK_STRUCTURED_ACTUAL_ID AS FK3_3_0_
,p.SPECIFICATION_ID AS SPECIFIC4_3_0_
,dtp.PROPERTY_VALUE AS PROPERTY2_4_0_
,sp.PROPERTY_VALUE AS PROPERTY2_5_0_
,blp.PROPERTY_VALUE AS PROPERTY2_6_0_
,ap.PROPERTY_VALUE AS PROPERTY2_7_0_
,bp.PROPERTY_VALUE AS PROPERTY2_8_0_
,dp.PROPERTY_VALUE AS PROPERTY2_9_0_
,ip.PROPERTY_VALUE AS PROPERTY2_10_0_
,lp.PROPERTY_VALUE AS PROPERTY2_11_0_
,cp.AMOUNT_VALUE AS AMOUNT2_12_0_
,cp.CURRENCY_CODE AS CURRENCY3_12_0_
,tp.PROPERTY_VALUE AS PROPERTY2_13_0_
,pp.PROPERTY_VALUE AS PROPERTY2_14_0_
,rvop.PROPERTY_VALUE_CLASS_NAME AS PROPERTY2_15_0_
,rvop.PROPERTY_VALUE AS PROPERTY3_15_0_
,mp.PROPERTY_VALUE AS PROPERTY2_16_0_
,op.OBJREFVALUE_OBJECT_ID AS OBJREFVA2_17_0_
,op.OBJREFVALUE_TYPE_ID AS OBJREFVA3_17_0_
,op.OBJREFVALUE_COMPONENT_ID AS OBJREFVA4_17_0_
,CASE
WHEN dtp.FK_PROPERTY_ID IS NOT NULL THEN 1
WHEN sp.FK_PROPERTY_ID IS NOT NULL THEN 2
WHEN blp.FK_PROPERTY_ID IS NOT NULL THEN 3
WHEN ap.FK_PROPERTY_ID IS NOT NULL THEN 4
WHEN bp.FK_PROPERTY_ID IS NOT NULL THEN 5
WHEN dp.FK_PROPERTY_ID IS NOT NULL THEN 6
WHEN ip.FK_PROPERTY_ID IS NOT NULL THEN 7
WHEN lp.FK_PROPERTY_ID IS NOT NULL THEN 8
WHEN cp.FK_PROPERTY_ID IS NOT NULL THEN 9
WHEN tp.FK_PROPERTY_ID IS NOT NULL THEN 10
WHEN pp.FK_PROPERTY_ID IS NOT NULL THEN 11
WHEN rvop.FK_PROPERTY_ID IS NOT NULL THEN 12
WHEN mp.FK_PROPERTY_ID IS NOT NULL THEN 13
WHEN op.FK_PROPERTY_ID IS NOT NULL THEN 14
WHEN p.ID IS NOT NULL THEN 0
END AS clazz_0_
FROMTBL_PROPERTY p
LEFT OUTER JOINTBL_DATE_PROPERTY dtp
ON p.ID=dtp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_STRING_PROPERTY sp
ON p.ID=sp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_BOOLEAN_PROPERTY blp
ON p.ID=blp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_AMOUNT_PROPERTY ap
ON p.ID=ap.FK_PROPERTY_ID
LEFT OUTER JOINTBL_BIGDECIMAL_PROPERTY bp
ON p.ID=bp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_DOUBLE_PROPERTY dp
ON p.ID=dp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_INTEGER_PROPERTY ip
ON p.ID=ip.FK_PROPERTY_ID
LEFT OUTER JOINTBL_LONG_PROPERTY lp
ON p.ID=lp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_CURRENCYAMOUNT_PROPERTY cp
ON p.ID=cp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_TIMESTAMP_PROPERTY tp
ON p.ID=tp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_PERCENTAGE_PROPERTY pp
ON p.ID=pp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_REQUESTVALUES_OBJECT_PROPERTY rvop
ON p.ID=rvop.FK_PROPERTY_ID
LEFT OUTER JOINTBL_MAP_PROPERTY mp
ON p.ID=mp.FK_PROPERTY_ID
LEFT OUTER JOINTBL_OBJECTREFERENCE_PROPERTY op
ON p.ID=op.FK_PROPERTY_ID
WHEREp.FK_STRUCTURED_ACTUAL_ID IN (1234,5678)
-
For anyone else trying to read the SQL plan files, when I downloaded them, they showed up with an "xml" extension so I had to rename them with a "sqlplan" extension to be able to open them.
To minimize the impact of the read on the TBL_PROPERTY table, you might consider selecting those records first into a temp table, then using the temp table in the bigger query.
SELECT*INTO#PROPERTY_TEMP
FROMTBL_PROPERTY
WHEREFK_STRUCTURED_ACTUAL_ID IN (1234,5678)
Then replce TBL_PROPERTY with #PROPERTY_TEMP in the main query.
-
fahey.jonathan (1/25/2012)
For anyone else trying to read the SQL plan files, when I downloaded them, they showed up with an "xml" extension so I had to rename them with a "sqlplan" extension to be able to open them.To minimize the impact of the read on the TBL_PROPERTY table, you might consider selecting those records first into a temp table, then using the temp table in the bigger query.
SELECT*INTO#PROPERTY_TEMP
FROMTBL_PROPERTY
WHEREFK_STRUCTURED_ACTUAL_ID IN (1234,5678)
Then replce TBL_PROPERTY with #PROPERTY_TEMP in the main query.
Unfortunately I have no control of the code coming in, or the database design for that matter.
-
Artoo22 (1/26/2012)
fahey.jonathan (1/25/2012)
For anyone else trying to read the SQL plan files, when I downloaded them, they showed up with an "xml" extension so I had to rename them with a "sqlplan" extension to be able to open them.To minimize the impact of the read on the TBL_PROPERTY table, you might consider selecting those records first into a temp table, then using the temp table in the bigger query.
SELECT*INTO#PROPERTY_TEMP
FROMTBL_PROPERTY
WHEREFK_STRUCTURED_ACTUAL_ID IN (1234,5678)
Then replce TBL_PROPERTY with #PROPERTY_TEMP in the main query.
Unfortunately I have no control of the code coming in, or the database design for that matter.
So...what can you change?
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Can you provide the DDL for the Foreign Key Constraints? I'm trying to figure out why there would need to be an Exclusive lock taken on PK_TBL_REQUESTVALUES_OBJECT_PROPERTY. That is not being updated unless the FK is on one of the values being updated and the FK has ON UPDATE CASCADE defined.
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 -
Jack Corbett (1/26/2012)
Can you provide the DDL for the Foreign Key Constraints? I'm trying to figure out why there would need to be an Exclusive lock taken on PK_TBL_REQUESTVALUES_OBJECT_PROPERTY. That is not being updated unless the FK is on one of the values being updated and the FK has ON UPDATE CASCADE defined.There are no foreign key constraints on either of those tables.
There were actually two update statements and it's the second that was causing the deadlock. It is:
UPDATE TBL_REQUESTVALUES_OBJECT_PROPERTYSET PROPERTY_VALUE_CLASS_NAME = NULL
,PROPERTY_VALUE = NULL
WHERE FK_PROPERTY_ID = @i
-
Here is a go-to blog post on deadlock troubleshooting:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Note there are 2 additional parts to this blog series.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Doesn't seem like those statements should deadlock unless you have 2 transactions where the access path is reversed like this:
Transaction 1
UPDATE TBL_REQUESTVALUES_OBJECT_PROPERTY
SET PROPERTY_VALUE_CLASS_NAME = NULL
,PROPERTY_VALUE = NULL
WHERE FK_PROPERTY_ID = @i
UPDATETBL_PROPERTY
SETKIND_ID = 1234
,FK_STRUCTURED_ACTUAL_ID = 5678
,SPECIFICATION_ID = 910
WHEREID = 1000
Transaction 2:
UPDATETBL_PROPERTY
SETKIND_ID = 1234
,FK_STRUCTURED_ACTUAL_ID = 5678
,SPECIFICATION_ID = 910
WHEREID = 1000
UPDATE TBL_REQUESTVALUES_OBJECT_PROPERTY
SET PROPERTY_VALUE_CLASS_NAME = NULL
,PROPERTY_VALUE = NULL
WHERE FK_PROPERTY_ID = @i
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply