Deadlocks

Question

Post reply

Deadlocks

thisted

SSCrazy

Points: 2131
More actions
October 29, 2013 at 2:45 pm

#300596

I'm getting deadlocks, but I'm confused why when I see shared locks? Basically I have a number of different stored procedures that get details from a status table, based on provided parameters, and then I have a few update/merge stored procedures that obviously add new or update existing data. My confusion is why these select queries are causing deadlocks?

Viewing 9 posts - 1 through 8 (of 8 total)

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

thisted SSCrazy Points: 2131 More actions · Answer 1

<deadlock-list>

<deadlock victim="process15cdabe928">

<process-list>

<executionStack>

<frame procname="DatabaseName.SchemaName.Status_GetByPriorAuthorizationId" line="13" stmtstart="606" stmtend="1594" sqlhandle="0x030005000c700442d4347f0161a2000001000000000000000000000000000000000000000000000000000000">

SELECTStatusId,

StatusTypeId,

EffectiveDttm,

TerminationDttm,

ActivityId,

PartyRoleId,

--RXOrderId,

OrderItemId,

PrescriptionId,

PriorAuthorizationId,

--ShipmentId,

StatusReasonTypeId,

CreatedById,

CreatedDttm,

LastEditedById,

LastEditedDttm,

DeleteInd,

RowVersionId

FROM [Status]

WHERE PriorAuthorizationId = @PriorAuthorizationId

AND DeleteInd = 0

AND (TerminationDttm IS NULL OR TerminationDttm > GETDATE()) </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 5 Object Id = 1107587084] </inputbuf>

</process>

<executionStack>

<frame procname="DatabaseName.SchemaName.Status_Merge" line="41" stmtstart="2178" stmtend="3718" sqlhandle="0x030005003df20516d6347f0161a2000001000000000000000000000000000000000000000000000000000000">

UPDATE [SchemaName].[Status] SET

StatusTypeId = @StatusTypeId,

StatusTypeText = @StatusTypeText,

StatusReasonTypeText = @StatusReasonTypeText,

StatusChildReasonTypeText = @StatusChildReasonTypeText,

StatusNoteText = @StatusNoteText,

TerminationDttm = @TerminationDttm,

ActivityId = @ActivityId,

ActivityWorkId = @ActivityWorkId,

PartyRoleId = @PartyRoleId,

OrderItemId = @OrderItemId,

PrescriptionId = @PrescriptionId,

PriorAuthorizationId = @PriorAuthorizationId,

StatusReasonTypeId = @StatusReasonTypeId,

LastEditedById = @LastEditedById,

LastEditedDttm = @LastEditedDttm,

DeleteInd = @DeleteInd

OUTPUT inserted.StatusId, inserted.RowVersionId INTO #output

WHERE StatusId = @StatusId </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 5 Object Id = 369488445] </inputbuf>

</process>

</process-list>

<resource-list>

<pagelock fileid="3" pageid="695694" dbid="5" subresource="FULL" objectname="DatabaseName.SchemaName.Status" id="lock136b5ede00" mode="IX" associatedObjectId="72057600471728128">

<owner-list>

<owner id="process12bb19d498" mode="IX"/>

</owner-list>

<waiter-list>

<waiter id="process15cdabe928" mode="S" requestType="wait"/>

</waiter-list>

</pagelock>

<pagelock fileid="3" pageid="695693" dbid="5" subresource="FULL" objectname="DatabaseName.SchemaName.Status" id="lock1394b32400" mode="S" associatedObjectId="72057600471728128">

<owner-list>

<owner id="process15cdabe928" mode="S"/>

</owner-list>

<waiter-list>

<waiter id="process12bb19d498" mode="IX" requestType="wait"/>

</waiter-list>

</pagelock>

</resource-list>

</deadlock>

</deadlock-list>

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

A SELECT statement requests a lock on one or more items, just like an UPDATE/DELETE/INSERT does. It is a SHARED type of lock, but a lock nonetheless. So when one SPID HAS a lock on an object and requests a lock on another object and ANOTHER SPID is doing the exact opposite, a deadlock can occur if the lock types are incompatible. There is a matrix in Books Online that explains the various types of locks and their compatibilities.

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

SQLALX SSC Eights! Points: 956 More actions · Answer 3

SPID 69 is updating Status table while SPID 77 is selecting the same table.

SPID 69 is holding IX lock on page 5:3:695694 and trying to lock (IX) another page (5:3:695693).

In the meantime, SPID 77 is holding S lock on 5:3:695693 and trying to put S lock on next page 5:3:695694.

Hence the deadlock.

thisted SSCrazy Points: 2131 More actions · Answer 4

So, possible solutions. I identified an index That is on the table, but isn't being used that I plan to remove. I also am looking into changing the select queries to use no lock.

What other solutions would you suggest?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

thisted (10/29/2013)
So, possible solutions. I identified an index That is on the table, but isn't being used that I plan to remove. I also am looking into changing the select queries to use no lock.
What other solutions would you suggest?

1) you say the index isn't being used - yet it was being locked by the SELECT statement.

2) NOLOCK should NOT be your first approach to resolving deadlocks. See here, and follow the entire thread, for some help with resolving deadlocks. There are some other more recent blog posts on this topic too. http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspx

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Tune the queries should always be the first option. Deadlocks are primarily a performance related issue.

Another option is to look at using one of the snapshot isolation levels, read_committed_snapshot for example. This adds additional load to the tempdb, but radically reduces locking without getting into all the issues around NOLOCK.

Listen to Kevin. Don't use NOLOCK as a solution. It's a dangerous possibility for your business data.

"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

thisted SSCrazy Points: 2131 More actions · Answer 7

There are multiple indexes on the table. There is one that hasn't had a seek, scan or lookup, but has been updated 223,000,000 times. There's another index that only has a few seeks that I was also considering remove just to alleviate overhead.

I'll look into tuning and the snapshot isolation. Thanks Grant. BTW Grant, it was good to see you at PASS. I only managed to catch one of your sessions, but nice to put a face with the name.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

thisted (10/30/2013)
There are multiple indexes on the table. There is one that hasn't had a seek, scan or lookup, but has been updated 223,000,000 times. There's another index that only has a few seeks that I was also considering remove just to alleviate overhead.
I'll look into tuning and the snapshot isolation. Thanks Grant. BTW Grant, it was good to see you at PASS. I only managed to catch one of your sessions, but nice to put a face with the name.

Thanks.

And you're right. If the indexes are not used, removing them is a part of tuning the performance on the system.

"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