SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks


Deadlocks

Author
Message
thisted
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343
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?


thisted
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343

<deadlock-list>
<deadlock victim="process15cdabe928">
<process-list>
<process id="process15cdabe928" taskpriority="0" logused="0" waitresource="PAGE: 5:3:695694 " waittime="2961" ownerId="592144764" transactionname="SELECT" lasttranstarted="2013-10-28T18:08:06.970" XDES="0x18fbe00d80" lockMode="S" schedulerid="2" kpid="6252" status="suspended" spid="77" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2013-10-28T18:08:06.967" lastbatchcompleted="2013-10-28T18:08:06.967" lastattention="1900-01-01T00:00:00.967" clientapp=".Net SqlClient Data Provider" hostname="MIHQENETPRDWB02" hostpid="1076" isolationlevel="read committed (2)" xactid="592144764" currentdb="5" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">
<executionStack>
<frame procname="DatabaseName.SchemaName.Status_GetByPriorAuthorizationId" line="13" stmtstart="606" stmtend="1594" sqlhandle="0x030005000c700442d4347f0161a2000001000000000000000000000000000000000000000000000000000000">
SELECT StatusId,
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>
<process id="process12bb19d498" taskpriority="0" logused="18912" waitresource="PAGE: 5:3:695693 " waittime="2935" ownerId="592144154" transactionname="user_transaction" lasttranstarted="2013-10-28T18:08:06.860" XDES="0x154264d6a8" lockMode="IX" schedulerid="10" kpid="3616" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2013-10-28T18:08:07.053" lastbatchcompleted="2013-10-28T18:08:07.053" lastattention="1900-01-01T00:00:00.053" clientapp=".Net SqlClient Data Provider" hostname="MIHQENETPRDWB02" hostpid="1076" loginname="userDatabaseName" isolationlevel="read committed (2)" xactid="592144154" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12685 Visits: 8563
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
SQLALX
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 83
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
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12685 Visits: 8563
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
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40777 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
thisted
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343
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
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40777 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search