January 3, 2014 at 7:58 am
All,
I am running into a deadlock in SQL Server 2008. Using a profiler I was able to determine that the deadlock happens as I am trying to select and update row(S) in table in 2 different sessions.
The select query from the profiler is shown below
select
nodeinstan0_.id as id35_,
nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_,
nodeinstan0_.END_DATE as END3_35_,
nodeinstan0_.NODE_ID as NODE4_35_,
nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_,
nodeinstan0_.NODE_NAME as NODE6_35_,
nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_,
nodeinstan0_.START_DATE as START8_35_
from
NODE_INSTANCE_LOG nodeinstan0_ where
nodeinstan0_.NODE_INSTANCE_ID= @P0 and
nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and
(nodeinstan0_.END_DATE is null)
My SQL Scripts for creating the table and index is show below
CREATE TABLE [dbo].[NODE_INSTANCE_LOG](
[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
[BPMN_ELEMENT_ID] [varchar](255) NULL,
[END_DATE] [datetime] NULL,
[NODE_ID] [varchar](255) NOT NULL,
[NODE_INSTANCE_ID] [varchar](255) NOT NULL,
[NODE_NAME] [varchar](2000) NULL,
[PROCESS_INSTANCE_ID] [numeric](19, 0) NOT NULL,
[START_DATE] [datetime] NULL,
PRIMARY KEY CLUSTERED
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_NODE_INSTANCE_LOG_Index1
ON NODE_INSTANCE_LOG(NODE_INSTANCE_ID, PROCESS_INSTANCE_ID, END_DATE)
INCLUDE
( ID, BPMN_ELEMENT_ID, NODE_ID, NODE_NAME, START_DATE);
As I understood from number of other forums and blogs - e.g. http://social.msdn.microsoft.com/Forums/en-US/63b033bb-15fe-401b-9d3c-4edb56244a84/sql-server-non-clustered-index-vs-covering-index I have included the columns under the where clause in the "COVERING index" and the rest of the columns from the select query under non clustered Index. But I am still facing a deadlock.
Would appreciate if some once can point me in the right direction.
regards D
January 3, 2014 at 9:46 am
I think to help you people will need at least the update statement as well, but preferably either the XML from the deadlock graph in profiler or the output from trace flag 1222.
---------------------------------------------------------------------
January 3, 2014 at 9:57 am
The deadlock is probably being caused by multiple slow points, not just the select statement. I'd suggest getting the execution plans for the two queries involved in the deadlock and seeing what tuning opportunities you have. Also, it's pretty likely there's another data modification query involved in this process. You should identify that and check that the access across the various tables involved in all the queries involved are in the same order. A classic cause of deadlocks is reading from tableA and then needing to update tableB while another process reads from tableB and then needs to update TableA.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2014 at 10:25 am
Correct to my earlier observation. Its not a select/update that's causing the deadlock but its a select/select
I am pasting an extract from the Tracer
<deadlock victim="process6a9048">
<process-list>
<process id="process6a9048" taskpriority="0" logused="15888" waitresource="KEY: 6:72057594142588928 (fbd2e857cffa)" waittime="812" ownerId="1927048" transactionname="implicit_transaction" lasttranstarted="2014-01-03T17:01:35.453" XDES="0x91cd7960" lockMode="S" schedulerid="2" kpid="3640" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-03T17:01:35.723" lastbatchcompleted="2014-01-03T17:01:35.723" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="1927048" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="62" sqlhandle="0x02000000e82b4000ebf38fb66978d5f9f2314a6046f957b3">
select nodeinstan0_.id as id35_, nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_, nodeinstan0_.END_DATE as END3_35_, nodeinstan0_.NODE_ID as NODE4_35_, nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_, nodeinstan0_.NODE_NAME as NODE6_35_, nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_, nodeinstan0_.START_DATE as START8_35_ from NODE_INSTANCE_LOG nodeinstan0_ where nodeinstan0_.NODE_INSTANCE_ID= @P0 and nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and (nodeinstan0_.END_DATE is null) </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 bigint)select nodeinstan0_.id as id35_, nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_, nodeinstan0_.END_DATE as END3_35_, nodeinstan0_.NODE_ID as NODE4_35_, nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_, nodeinstan0_.NODE_NAME as NODE6_35_, nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_, nodeinstan0_.START_DATE as START8_35_ from NODE_INSTANCE_LOG nodeinstan0_ where nodeinstan0_.NODE_INSTANCE_ID= @P0 and nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and (nodeinstan0_.END_DATE is null) </inputbuf>
</process>
<process id="process6dddc8" taskpriority="0" logused="114316" waitresource="KEY: 6:72057594142588928 (44ec7e8e62c7)" waittime="602" ownerId="1927022" transactionname="implicit_transaction" lasttranstarted="2014-01-03T17:01:35.227" XDES="0x91d25950" lockMode="S" schedulerid="4" kpid="17412" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-03T17:01:35.937" lastbatchcompleted="2014-01-03T17:01:35.937" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="1927022" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="62" sqlhandle="0x02000000e82b4000ebf38fb66978d5f9f2314a6046f957b3">
select nodeinstan0_.id as id35_, nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_, nodeinstan0_.END_DATE as END3_35_, nodeinstan0_.NODE_ID as NODE4_35_, nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_, nodeinstan0_.NODE_NAME as NODE6_35_, nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_, nodeinstan0_.START_DATE as START8_35_ from NODE_INSTANCE_LOG nodeinstan0_ where nodeinstan0_.NODE_INSTANCE_ID= @P0 and nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and (nodeinstan0_.END_DATE is null) </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 bigint)select nodeinstan0_.id as id35_, nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_, nodeinstan0_.END_DATE as END3_35_, nodeinstan0_.NODE_ID as NODE4_35_, nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_, nodeinstan0_.NODE_NAME as NODE6_35_, nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_, nodeinstan0_.START_DATE as START8_35_ from NODE_INSTANCE_LOG nodeinstan0_ where nodeinstan0_.NODE_INSTANCE_ID= @P0 and nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and (nodeinstan0_.END_DATE is null) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594142588928" dbid="6" objectname="reform.dbo.NODE_INSTANCE_LOG" indexname="IX_NODE_INSTANCE_LOG_Index1" id="lock89f43000" mode="X" associatedObjectId="72057594142588928">
<owner-list>
<owner id="process6dddc8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process6a9048" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594142588928" dbid="6" objectname="reform.dbo.NODE_INSTANCE_LOG" indexname="IX_NODE_INSTANCE_LOG_Index1" id="lock81b22a80" mode="X" associatedObjectId="72057594142588928">
<owner-list>
<owner id="process6a9048" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process6dddc8" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
January 3, 2014 at 11:29 am
I can see from info that you have provided that your queries will not use indexes. Your table has varchar fields and query is called with nvarchar.
You need to alter connection string by setting sendStringParametersAsUnicode=false. See http://technet.microsoft.com/en-us/library/ms378988.aspx for more info.
January 4, 2014 at 3:19 am
SELECT queries alone will not cause deadlocks. There has to be data modification queries built in there somewhere.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2014 at 4:13 am
Hi,
I have read the link
http://technet.microsoft.com/en-us/library/ms378988.aspx
for sendStringParametersAsUnicode=false.
I see no relationship between this parameter and indexes not being used.
Can you pls clarify?
regards
D
January 6, 2014 at 4:19 am
The code looks to me like it will be likely to use that index. Have you checked the execution plan to see what it is doing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2014 at 5:15 am
angeshwar (1/6/2014)
Hi,I have read the link
http://technet.microsoft.com/en-us/library/ms378988.aspx
for sendStringParametersAsUnicode=false.
I see no relationship between this parameter and indexes not being used.
Can you pls clarify?
<inputbuf>
(@P0 nvarchar(4000),@P1 bigint)select nodeinstan0_.id as id35_, nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_, nodeinstan0_.END_DATE as END3_35_, nodeinstan0_.NODE_ID as NODE4_35_, nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_, nodeinstan0_.NODE_NAME as NODE6_35_, nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_, nodeinstan0_.START_DATE as START8_35_ from NODE_INSTANCE_LOG nodeinstan0_ where nodeinstan0_.NODE_INSTANCE_ID= @P0 and nodeinstan0_.PROCESS_INSTANCE_ID= @P1 and (nodeinstan0_.END_DATE is null) </inputbuf>
Here we can see that type of @P0 is NVARCHAR, however table NODE_INSTANCE_ID is VARCHAR
CREATE TABLE [dbo].[NODE_INSTANCE_LOG](
[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
[BPMN_ELEMENT_ID] [varchar](255) NULL,
[END_DATE] [datetime] NULL,
[NODE_ID] [varchar](255) NOT NULL,
[NODE_INSTANCE_ID] [varchar](255) NOT NULL,
[NODE_NAME] [varchar](2000) NULL,
[PROCESS_INSTANCE_ID] [numeric](19, 0) NOT NULL,
[START_DATE] [datetime] NULL,
PRIMARY KEY CLUSTERED
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_NODE_INSTANCE_LOG_Index1
ON NODE_INSTANCE_LOG(NODE_INSTANCE_ID, PROCESS_INSTANCE_ID, END_DATE)
INCLUDE
( ID, BPMN_ELEMENT_ID, NODE_ID, NODE_NAME, START_DATE);
Check following link too http://blog.brianhartsock.com/2008/12/14/nvarchar-vs-varchar-in-sql-server-beware/. I hope it clarifies what I am trying to tell.
January 6, 2014 at 5:23 am
Oh, nice catch, I didn't see that.
Still look at the execution plan. It's a foundational start to everything you do when trying to tune performance problems on queries.
However, I'm still back to my original statement, a SELECT statement alone will absolutely NOT deadlock. So you must have other statements associated with the processes that are deadlocking. I would strongly suggest focusing there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2014 at 5:47 am
I have updated my connection string to
jdbc.url=jdbc:jtds:sqlserver://XXXXX:1433/XXXXX;instance=MSSQLServer;sendStringParametersAsUnicode=false
I am still facing the deadlock.
Can confirm that 2 queries causing deadlock are #select#.
I am not able to find the actual values that are passed into the Query in the deadlock.
Can some one point out how to enable that?
Also I tried checking the execution plan for the query by using some arbitrary values (and not the same values from the deadlock)
select
nodeinstan0_.id as id35_,
nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_,
nodeinstan0_.END_DATE as END3_35_,
nodeinstan0_.NODE_ID as NODE4_35_,
nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_,
nodeinstan0_.NODE_NAME as NODE6_35_,
nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_,
nodeinstan0_.START_DATE as START8_35_
from NODE_INSTANCE_LOG nodeinstan0_
where
nodeinstan0_.NODE_INSTANCE_ID= 7
and
nodeinstan0_.PROCESS_INSTANCE_ID= 90
and
(nodeinstan0_.END_DATE is null)
As per the execution plan - its using a Clustered Index Scan (and not seek).
Also attached the deadlock queries.
regards
D
January 6, 2014 at 9:52 am
angeshwar (1/6/2014)
I have updated my connection string tojdbc.url=jdbc:jtds:sqlserver://XXXXX:1433/XXXXX;instance=MSSQLServer;sendStringParametersAsUnicode=false
I am still facing the deadlock.
Can confirm that 2 queries causing deadlock are #select#.
I am not able to find the actual values that are passed into the Query in the deadlock.
Can some one point out how to enable that?
Also I tried checking the execution plan for the query by using some arbitrary values (and not the same values from the deadlock)
select
nodeinstan0_.id as id35_,
nodeinstan0_.BPMN_ELEMENT_ID as BPMN2_35_,
nodeinstan0_.END_DATE as END3_35_,
nodeinstan0_.NODE_ID as NODE4_35_,
nodeinstan0_.NODE_INSTANCE_ID as NODE5_35_,
nodeinstan0_.NODE_NAME as NODE6_35_,
nodeinstan0_.PROCESS_INSTANCE_ID as PROCESS7_35_,
nodeinstan0_.START_DATE as START8_35_
from NODE_INSTANCE_LOG nodeinstan0_
where
nodeinstan0_.NODE_INSTANCE_ID= 7
and
nodeinstan0_.PROCESS_INSTANCE_ID= 90
and
(nodeinstan0_.END_DATE is null)
As per the execution plan - its using a Clustered Index Scan (and not seek).
Also attached the deadlock queries.
regards
D
Query above will definitely use clustered index scan because there will be implicit conversion at nodeinstan0_.NODE_INSTANCE_ID= 7. It is exactly same thing with varchar vs nvarchar
Parameter values used can be collected from profiler, but that is bit hard. Monitoring tools will be also able to do that.
I second Grant on that there must be also either insert or delete query in process too. I have seen cases where there has been one update query and multiple select queries. So can you post update query too. Also I would like to see execution plans saved as xml. You can find them from DMVs.
January 7, 2014 at 8:06 am
I am attaching the Deadlock report and the XML file with the execution plan (as zip files)
Would appreciate if some one can help me analyse it.
January 7, 2014 at 8:30 am
Transaction count is 1 on both sessions. What else is happening inside these transactions?
is there an exclusive lock on some record(s) in the table that gets hit by the shared lock created by the select (index scan).
January 8, 2014 at 8:42 am
Hi,
yes, there is only one transaction in each of the sessions.
The deadlock I have on table NODE_INSTANCE_LOG is gone now after making the following change
/* Deadlock Fix */
CREATE TABLE [dbo].[NODE_INSTANCE_LOG](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[BPMN_ELEMENT_ID] [varchar](255) NULL,
[NODE_ID] [varchar](255) NOT NULL,
[NODE_INSTANCE_ID] [varchar](255) NOT NULL,
[NODE_NAME] [varchar](2000) NULL,
[PROCESS_INSTANCE_ID] [bigint] NOT NULL,
[START_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_NODE_INSTANCE_LOG_Index1
ON NODE_INSTANCE_LOG(PROCESS_INSTANCE_ID,NODE_INSTANCE_ID, END_DATE) INCLUDE ( ID, BPMN_ELEMENT_ID, NODE_ID, NODE_NAME, START_DATE);
GO
/* Prior to fixing Deadlock*/
CREATE TABLE [dbo].[NODE_INSTANCE_LOG](
[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
[BPMN_ELEMENT_ID] [varchar](255) NULL,
[END_DATE] [datetime] NULL,
[NODE_ID] [varchar](255) NOT NULL,
[NODE_INSTANCE_ID] [varchar](255) NOT NULL,
[NODE_NAME] [varchar](2000) NULL,
[PROCESS_INSTANCE_ID] [numeric](19, 0) NOT NULL,
[START_DATE] [datetime] NULL,
PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_NODE_INSTANCE_LOG_Index1
ON NODE_INSTANCE_LOG(NODE_INSTANCE_ID, BPMN_ELEMENT_ID, PROCESS_INSTANCE_ID, END_DATE, NODE_ID, NODE_NAME, START_DATE) ;
GO
The change(s) I did were
(1) Change datatype from numeric to bigint and changing the index on primary key from CLUSTERED to NONCLUSTERED.
Now the deadlock occurs on a different table (ENGINE_ENTITIES) - again looks like 2 select queries.
The
<deadlock victim="process42f2bc8">
<process-list>
<process id="process42f2bc8" taskpriority="0" logused="76708" waitresource="RID: 6:1:325:8" waittime="3867" ownerId="1432992" transactionname="implicit_transaction" lasttranstarted="2014-01-08T15:15:44.923" XDES="0x87ca5950" lockMode="S" schedulerid="3" kpid="16260" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-08T15:15:46.443" lastbatchcompleted="2014-01-08T15:15:46.443" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="1432992" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="306" sqlhandle="0x02000000c24222241cc10c5c05b369dc18a94aa5dfde2b32">
select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000))select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </inputbuf>
</process>
<process id="process42d9b88" taskpriority="0" logused="81928" waitresource="RID: 6:1:325:9" waittime="3932" ownerId="1433219" transactionname="implicit_transaction" lasttranstarted="2014-01-08T15:15:44.967" XDES="0x8033ee80" lockMode="S" schedulerid="2" kpid="21196" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-08T15:15:46.380" lastbatchcompleted="2014-01-08T15:15:46.380" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="1433219" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="306" sqlhandle="0x02000000c24222241cc10c5c05b369dc18a94aa5dfde2b32">
select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000))select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </inputbuf>
</process>
<process id="process430d288" taskpriority="0" logused="84760" waitresource="RID: 6:1:325:8" waittime="4142" ownerId="1432852" transactionname="implicit_transaction" lasttranstarted="2014-01-08T15:15:44.830" XDES="0x8a31b950" lockMode="S" schedulerid="4" kpid="21280" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-08T15:15:46.170" lastbatchcompleted="2014-01-08T15:15:46.167" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="1432852" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="306" sqlhandle="0x02000000c24222241cc10c5c05b369dc18a94aa5dfde2b32">
select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000))select xmlentity0_.ID as ID55_, xmlentity0_.CREATED_DATE as CREATED2_55_, xmlentity0_.ENTITY_ID as ENTITY3_55_, xmlentity0_.ENTITY_TYPE as ENTITY4_55_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_55_, xmlentity0_.STRING_DATA as STRING6_55_, xmlentity0_.UPDATED_DATE as UPDATED7_55_, xmlentity0_.XML_DATA as XML8_55_ from ENGINE_ENTITIES xmlentity0_ where xmlentity0_.ENTITY_ID= @P0 and (xmlentity0_.ENTITY_TYPE in ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 )) order by xmlentity0_.UPDATED_DATE desc </inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="325" dbid="6" objectname="reform.dbo.ENGINE_ENTITIES" id="lock818dd680" mode="X" associatedObjectId="72057594161070080">
<owner-list/>
<waiter-list>
<waiter id="process42f2bc8" mode="S" requestType="wait"/>
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="325" dbid="6" objectname="reform.dbo.ENGINE_ENTITIES" id="lock800b0d80" mode="X" associatedObjectId="72057594161070080">
<owner-list>
<owner id="process42f2bc8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process42d9b88" mode="S" requestType="wait"/>
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="325" dbid="6" objectname="reform.dbo.ENGINE_ENTITIES" id="lock818dd680" mode="X" associatedObjectId="72057594161070080">
<owner-list>
<owner id="process42d9b88" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process430d288" mode="S" requestType="wait"/>
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
I tried making a similar change: datatype of primary key from numeric to bigint - but the deadlock still persist.
Can some one point out - what caused the deadlock to disappear in the table NODE_INSTANCE_LOG.
I can confirm that the sequence of execution in the transaction is in order, i.e. data insertion in NODE_INSTANCE_LOG follwed by insertion to ENGINE_ENTITIES table.
regards,
D
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply