Indexing and Deadlock in SQL Server 2008?

Question

Indexing and Deadlock in SQL Server 2008?

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

What is holding exclusive locks on the table?

The selects are trying to bet a shared locks on the table but each session is holding earlier exclusive locks within the transaction.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 2

What is the structure of the ENGINE_ENTITIES table?

What does the select look like?

angeshwar SSC Enthusiast Points: 153 More actions · Answer 3

Structure of ENGINE_ENTITIES

CREATE TABLE [dbo].[ENGINE_ENTITIES](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CREATED_DATE] [datetime] NULL,
[ENTITY_ID] [varchar](255) NULL,
[ENTITY_TYPE] [varchar](255) NULL,
[PROCESS_INSTANCE_ID] [bigint] NULL,
[STRING_DATA] [varchar](255) NULL,
[UPDATED_DATE] [datetime] NULL,
[XML_DATA] [text] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_PENGINE_ENTITIES_ONE
ON ENGINE_ENTITIES(ENTITY_ID , ENTITY_TYPE)
INCLUDE (ID, CREATED_DATE, PROCESS_INSTANCE_ID, STRING_DATA, UPDATED_DATE, XML_DATA);
GO

Select Query: 1st Session

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

Select Query: 2nd Session

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

The queries are identical except for the parameters passed into them.

It makes sense - that one of the session during its select holds a lock on all the rows of the table and causing the other session to go into deadlock.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 4

You were quicker than me 🙂

Without seeing actual or estimated execution plan my guess is that nonclustered index beginning with NODE_INSTANCE_ID was not exclusive enough to do index seek or you still have issues with nvarchar vs varchar.

New clustered which has PROCESS_INSTANCE_ID as first key in index is more selective and therefore index seek is used.

In last deadlock XML there is still parameters with type nvarchar. However your table has varchar. Optimizer will not use index seek. It will always make full scan!

Did you made my suggestion to all connection strings?

angeshwar SSC Enthusiast Points: 153 More actions · Answer 5

Yes,

sendStringParametersAsUnicode=false

was added to the connection String.

I think hibernate is passing the arguments as varchar and JTDS driver is converting it to NVARCHAR.

I also tried creating table with datatype NVARCHAR - so there is no type conversion needed but that does not make a difference.

Also tried re arranging the index

CREATE TABLE [dbo].[ENGINE_ENTITIES](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CREATED_DATE] [datetime] NULL,
[ENTITY_ID] [varchar](255) NULL,
[ENTITY_TYPE] [varchar](255) NULL,
[PROCESS_INSTANCE_ID] [bigint] NULL,
[STRING_DATA] [varchar](255) NULL,
[UPDATED_DATE] [datetime] NULL,
[XML_DATA] [text] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_PENGINE_ENTITIES_ONE
ON ENGINE_ENTITIES(PROCESS_INSTANCE_ID, ENTITY_ID , ENTITY_TYPE)
INCLUDE (ID, CREATED_DATE, STRING_DATA, UPDATED_DATE, XML_DATA);
GO

But still getting deadlock.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 6

We are using hibernate and jpa with glassfish with no problems after adding this property to connection string. Varchars are sent as varchars. Previously they were sent as nvarchar.

Could you add PROCESS_ENTITY_ID to later query and changing current non clustered to clustered index and putting PROCESS_ENTITY_ID as first key. This will create huge clustered key, which is not very optimal.

I encourage you still find out if you can get varchar parameters passed as varchar. Otherwise you will have problems with other queries along the way.

I am pretty sure that there is some data modification query still somewhere or some locking hint.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 7

Could you run this query to get cached queries for ENGINE_ENTITIES table.

This will return cached query plan which you can open by clicking it. After that save them in .sqlplan format. I can have a look. I hope to see plans for selects, insert and updates.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @table VARCHAR(200) = 'ENGINE_ENTITIES';

SET @table = '%' + @table + '%';

SELECT TOP 10

[Average CPU used] = total_worker_time / qs.execution_count,

[Total CPU used] = total_worker_time,

[Execution count] = qs.execution_count,

[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

, query_plan

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

WHERE qt.text LIKE @table

ORDER BY total_worker_time DESC;

angeshwar SSC Enthusiast Points: 153 More actions · Answer 8

Hi,

Pls find attached the sqplplan(s) generated by the query you have provided.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 9

Hi. Thank you for the effort.

My query will return queries from cached plans. I think you missed one point. You need to click query_plan column on each row to visualize plan. After this you can save execution plan as sqlplan.

However I was able to find some information from attached zip file.

Try to find queries that are sent from application. Select, insert and update. You might want to set TOP 10 to something little higher to get all queries.

Following query is still making full table scan and I am pretty sure that reason are this implicit conversions from VARCHAR to NVARCHAR.

(

@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 ID34_

,xmlentity0_.CREATED_DATE AS CREATED2_34_

,xmlentity0_.ENTITY_ID AS ENTITY3_34_

,xmlentity0_.ENTITY_TYPE AS ENTITY4_34_

,xmlentity0_.PROCESS_INSTANCE_ID AS PROCESS5_34_

,xmlentity0_.STRING_DATA AS STRING6_34_

,xmlentity0_.UPDATED_DATE AS UPDATED7_34_

,xmlentity0_.XML_DATA AS XML8_34_

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

angeshwar SSC Enthusiast Points: 153 More actions · Answer 10

Hi,

Thanks for pointing that. I ensured my connection String has

sendStringParametersAsUnicode=false

To be double sure I also cleared my current cache with

DBCC FreeProcCache

DBCC DropCleanbuffers

and ran the Application again.

The in the deadlock report I see

<process id="process430d048" taskpriority="0" logused="78752" waitresource="RID: 6:1:581:5" waittime="2933" ownerId="2968067" transactionname="implicit_transaction" lasttranstarted="2014-01-10T17:10:47.210" XDES="0x8ca37950" lockMode="S" schedulerid="4" kpid="46508" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-10T17:10:47.897" lastbatchcompleted="2014-01-10T17:10:47.893" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="2968067" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="290" sqlhandle="0x02000000ffb28a09c53bd1a769ae1a004135abe14f4044e1">
select xmlentity0_.ID as ID34_, xmlentity0_.CREATED_DATE as CREATED2_34_, xmlentity0_.ENTITY_ID as ENTITY3_34_, xmlentity0_.ENTITY_TYPE as ENTITY4_34_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_34_, xmlentity0_.STRING_DATA as STRING6_34_, xmlentity0_.UPDATED_DATE as UPDATED7_34_, xmlentity0_.XML_DATA as XML8_34_ 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 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000))select xmlentity0_.ID as ID34_, xmlentity0_.CREATED_DATE as CREATED2_34_, xmlentity0_.ENTITY_ID as ENTITY3_34_, xmlentity0_.ENTITY_TYPE as ENTITY4_34_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_34_, xmlentity0_.STRING_DATA as STRING6_34_, xmlentity0_.UPDATED_DATE as UPDATED7_34_, xmlentity0_.XML_DATA as XML8_34_ 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="process42f2bc8" taskpriority="0" logused="82768" waitresource="RID: 6:1:581:4" waittime="2975" ownerId="2968073" transactionname="implicit_transaction" lasttranstarted="2014-01-10T17:10:47.240" XDES="0x8a3af950" lockMode="S" schedulerid="3" kpid="44536" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-10T17:10:47.857" lastbatchcompleted="2014-01-10T17:10:47.857" clientapp="jTDS" hostname="LDN-LRM-PC-189" hostpid="123" loginname="username" isolationlevel="read committed (2)" xactid="2968073" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="290" sqlhandle="0x02000000ffb28a09c53bd1a769ae1a004135abe14f4044e1">
select xmlentity0_.ID as ID34_, xmlentity0_.CREATED_DATE as CREATED2_34_, xmlentity0_.ENTITY_ID as ENTITY3_34_, xmlentity0_.ENTITY_TYPE as ENTITY4_34_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_34_, xmlentity0_.STRING_DATA as STRING6_34_, xmlentity0_.UPDATED_DATE as UPDATED7_34_, xmlentity0_.XML_DATA as XML8_34_ 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 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000))select xmlentity0_.ID as ID34_, xmlentity0_.CREATED_DATE as CREATED2_34_, xmlentity0_.ENTITY_ID as ENTITY3_34_, xmlentity0_.ENTITY_TYPE as ENTITY4_34_, xmlentity0_.PROCESS_INSTANCE_ID as PROCESS5_34_, xmlentity0_.STRING_DATA as STRING6_34_, xmlentity0_.UPDATED_DATE as UPDATED7_34_, xmlentity0_.XML_DATA as XML8_34_ 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>

I am also attaching the select query (from the query_plan) which does not show nvarchar any more : Select_Engine_Entities.sqlplan

Also attaching the Top_10 : Top_10_Queries.sqlplan

Rest of the queries - all inserts : SQL-QueryPlan.zip

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 11

angeshwar (1/8/2014)
Structure of ENGINE_ENTITIES
CREATE TABLE [dbo].[ENGINE_ENTITIES](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CREATED_DATE] [datetime] NULL,
[ENTITY_ID] [varchar](255) NULL,
[ENTITY_TYPE] [varchar](255) NULL,
[PROCESS_INSTANCE_ID] [bigint] NULL,
[STRING_DATA] [varchar](255) NULL,
[UPDATED_DATE] [datetime] NULL,
[XML_DATA] [text] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_PENGINE_ENTITIES_ONE
ON ENGINE_ENTITIES(ENTITY_ID , ENTITY_TYPE)
INCLUDE (ID, CREATED_DATE, PROCESS_INSTANCE_ID, STRING_DATA, UPDATED_DATE, XML_DATA);
GO

Is this still valid? My server does not create index IX_PENGINE_ENTITIES_ONE, because of included XML_DATA. Can you check indexes on that table. From posted execution plans it looks like that you do not have clustered or any other index at all.

Lowry Cui Newbie Points: 3 More actions · Answer 12

I'm angeshwar's colleague, I also met deadlock on another table SUMMARY_PROPERTIES. The following are table script and deadlock graph.

/****** Object: Table [dbo].[SUMMARY_PROPERTIES] Script Date: 12/11/2013 11:24:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SUMMARY_PROPERTIES](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[DATE_VALUE] [datetime] NULL,

[NAME] [varchar](255) NOT NULL,

[NUMERIC_VALUE] [numeric](19, 2) NULL,

[PROCESS_INSTANCE_ID] [bigint] NOT NULL,

[ALPHANUMERIC_VALUE] [varchar](255) 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

SET ANSI_PADDING OFF

GO

CREATE NONCLUSTERED INDEX IX_SUMMARY_PROPERTIES_ONE

ON SUMMARY_PROPERTIES(PROCESS_INSTANCE_ID, NAME);

GO

-----------------------DeadLock graph----------------------------------

<deadlock-list>

<process-list>

select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </frame>

</executionStack>

(@P0 bigint,@P1 varchar(8000))select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </inputbuf>

</process>

select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </frame>

</executionStack>

(@P0 bigint,@P1 varchar(8000))select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </inputbuf>

</process>

select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </frame>

</executionStack>

(@P0 bigint,@P1 varchar(8000))select summarypro0_.ID as ID53_, summarypro0_.DATE_VALUE as DATE2_53_, summarypro0_.NAME as NAME53_, summarypro0_.NUMERIC_VALUE as NUMERIC4_53_, summarypro0_.PROCESS_INSTANCE_ID as PROCESS5_53_, summarypro0_.ALPHANUMERIC_VALUE as ALPHANUM6_53_ from SUMMARY_PROPERTIES summarypro0_ where summarypro0_.PROCESS_INSTANCE_ID= @P0 and summarypro0_.NAME= @P1 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list/>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>

Can any one tell me how to come out of deadlock situations.

Regards

Lowry

angeshwar SSC Enthusiast Points: 153 More actions · Answer 13

Hi,

Yes, corrected the index for ENGINE_ENTITIES

CREATE TABLE [dbo].[ENGINE_ENTITIES](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CREATED_DATE] [datetime] NULL,
[ENTITY_ID] [varchar](255) NULL,
[ENTITY_TYPE] [varchar](255) NULL,
[PROCESS_INSTANCE_ID] [bigint] NULL,
[STRING_DATA] [varchar](255) NULL,
[UPDATED_DATE] [datetime] NULL,
[XML_DATA] [text] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_PENGINE_ENTITIES_ONE
ON ENGINE_ENTITIES(PROCESS_INSTANCE_ID, ENTITY_ID,ENTITY_TYPE) INCLUDE (ID, CREATED_DATE, STRING_DATA, UPDATED_DATE);

It was a typo in my earlier reply.

But the deadlock still exists in ENGINE_ENTITIES table (attached results are the same)

As for the SUMMARY_PROPERTIES table - adding the INCLUDE clause resolves the DEADLOCK

CREATE NONCLUSTERED INDEX IX_SUMMARY_PROPERTIES_Index1
ON SUMMARY_PROPERTIES(PROCESS_INSTANCE_ID, NAME) INCLUDE (ID, DATE_VALUE, NUMERIC_VALUE, ALPHANUMERIC_VALUE );

arnipetursson SSCertifiable Points: 6557 More actions · Answer 14

In both cases there is an earlier data modification within the transaction (X lock), then when the batches with the select try to get a shared lock on the resource, they are blocked by the exclusive lock of the other session.

You need to look at the data modification earlier in the transaction. Note the difference in the transaction start timestamp and the last batch start timestamp. Somewhere in that span a data modification occurs that causes the initial exclusive lock.

Does this transaction need to stay open or can it be committed earlier?

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 15

angeshwar (1/13/2014)
Hi,
Yes, corrected the index for ENGINE_ENTITIES
CREATE TABLE [dbo].[ENGINE_ENTITIES](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CREATED_DATE] [datetime] NULL,
[ENTITY_ID] [varchar](255) NULL,
[ENTITY_TYPE] [varchar](255) NULL,
[PROCESS_INSTANCE_ID] [bigint] NULL,
[STRING_DATA] [varchar](255) NULL,
[UPDATED_DATE] [datetime] NULL,
[XML_DATA] [text] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_PENGINE_ENTITIES_ONE
ON ENGINE_ENTITIES(PROCESS_INSTANCE_ID, ENTITY_ID,ENTITY_TYPE) INCLUDE (ID, CREATED_DATE, STRING_DATA, UPDATED_DATE);
It was a typo in my earlier reply.
But the deadlock still exists in ENGINE_ENTITIES table (attached results are the same)
As for the SUMMARY_PROPERTIES table - adding the INCLUDE clause resolves the DEADLOCK
CREATE NONCLUSTERED INDEX IX_SUMMARY_PROPERTIES_Index1
ON SUMMARY_PROPERTIES(PROCESS_INSTANCE_ID, NAME) INCLUDE (ID, DATE_VALUE, NUMERIC_VALUE, ALPHANUMERIC_VALUE );

I think you should read about clustered indexing . It look like your application and database would benefit from having clustered index other than primary key. Dead lock in ENGINE_ENTITIES table happens still because XML_DATA cannot be part of non clustered index. By the way you have ENGINE_ENTITIES table stored twice on disk. Was your intention to make IX_PENGINE_ENTITIES_ONE as clustered index. It does not need those include fields because it contains them anyway.