Select causing deadlock?

Question

Post reply

Select causing deadlock?

san43

Hall of Fame

Points: 3196
More actions
January 4, 2010 at 8:44 pm

#215189

I am experiencing a deadlock scenerio involving DELETE and SELECT. Following are the details from the deadlock graph.
<resource-list>
<pagelock fileid="1" pageid="374777" dbid="30" objectname="DB1.dbo.Table1" id="lock5424c00" mode="X" associatedObjectId="132533339029504">
<owner-list>
<owner id="process3a27588" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process3a13eb8" mode="IS" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="268632" dbid="30" objectname="DB1.dbo.Table1" id="lock5424f00" mode="U" associatedObjectId="695483292450816">
<owner-list>
<owner id="process3a13eb8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process3a27588" mode="X" requestType="convert"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
What does mode ='X' and mode ='U' in <pagelock> field means? do they mean anything?

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What is the utility that you are using to see this deadlock graph?

What are the queries that you suspect are causing the deadlocks?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

X is an exclusive lock and U is an update lock. Neither will be taken by a select unless there is a locking hint.

Can you post the entire deadlock graph?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

san43 Hall of Fame Points: 3196 More actions · Answer 3

Here is the deadlock xml file. My question is why is Update lock(U) showing up for a SELECT query?

That SELECT query is using non-clustered index of Table2 (which is also heap table). Does this mean anything?

Thanks for your response.

<deadlock-list>

<process-list>

SELECT DISTINCT Items.*, Table3.Description AS Description, Table2.Class AS VSClass,

ISNULL(Table4.status,'N/A') AS status

FROM Table1

LEFT OUTER JOIN Table2

ON Table1.SKU = Table2.SKU

LEFT OUTER JOIN Table3

ON Table1.SKU = Table3.SKU

LEFT OUTER JOIN Table4

ON Table1.SKU = Table4.SKU

WHERE (Table1.ID = @crno)

AND (Table1.SKU = @sku)

AND (Table1.Status_Code = @sc) </frame>

EXEC PROCName '123682', 'f70324sv/ma', 'NEWB' </frame>

</executionStack>

EXEC PROCName '123682', 'f70324sv/ma', 'NEWB' </inputbuf>

</process>

delete dbo.Table2 </frame>

</executionStack>

delete dbo.Table2 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It's not. The select has S and IS locks. Look at the owner list for which locks which process has and wants. The select has a page lock in mode S and wants another one in mode IS.

This could be a lookup deadlock. Can you post the structure and indexes on Table2?

Can you also run this?

SELECT object_name(object_id), index_id FROM sys.partitions where partition_id in (132533339029504, 695483292450816)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

san43 Hall of Fame Points: 3196 More actions · Answer 5

Yes I agree. When I look at the deadlock graph in the management studio, it says that the Process running the Proc has owner mode: U on one of the one of the resource and request mode: IS on the other resource. Unless I dwelled into the xml file, then I found that it actually has shared lock not the Update lock.

Thanks Again for your reply.

Following is the info that you asked for.

Table structure:

CREATE TABLE [dbo].[Table2](

[SKU] [varchar](8) NOT NULL,

[Class] [varchar](5) NULL,

[AV] [char](4) NULL,

[Location] [varchar](25) NULL,

[LPV] [varchar](25) NULL

) ON [PRIMARY]

END

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Table2] ON [dbo].[Table2]

(

[Location] ASC,

[SKU] ASC,

[AV] ASC

)

Results for the query:

Table20

Table22

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Why no clustered index on that table?

Try adding Class as an include column for that nonclustered index. See if it helps.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

san43 Hall of Fame Points: 3196 More actions · Answer 7

Why Class column? Its not used in the where clause right?

I have to check whether we can do this as there will be other SQL statements that might be impacted by this change.

Also can I use DELETE dbo.table2 WITH (TABLOCK)?

Thanks,

San

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

san43 (1/5/2010)
Why Class column? Its not used in the where clause right?

No, it's in the select. That's why I said add it as an INCLUDE column

I have to check whether we can do this as there will be other SQL statements that might be impacted by this change.

Nothing will be impacted. It's just adding one column as an include column, it can't affect anything else

Also can I use DELETE dbo.table2 WITH (TABLOCK)?

You can, it may help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Actually, looking at it, I suggest a new nonclustered index.

Index key: SKU. Include column: Class.

The current index you have is not seekable for this query as Location isn't used anywhere in that query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

san43 Hall of Fame Points: 3196 More actions · Answer 10

Do you mean keep the non-clustered index as is and create a new non-clustered with SKU column and INCLUDE class column?

I'll try these options.

Thanks again for your replies.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Yup. A second nonclustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

Here is the Bible for this area:

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 follow-on blog posts for this.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service