Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocks Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 2:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:22 AM
Points: 47, Visits: 263
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?

Post #1509572
Posted Tuesday, October 29, 2013 2:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:22 AM
Points: 47, Visits: 263
<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>



Post #1509575
Posted Tuesday, October 29, 2013 3:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:04 PM
Points: 4,356, Visits: 6,186
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 at GMail
Post #1509588
Posted Tuesday, October 29, 2013 3:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 5:59 PM
Points: 22, 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.
Post #1509590
Posted Tuesday, October 29, 2013 9:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:22 AM
Points: 47, Visits: 263
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?
Post #1509644
Posted Wednesday, October 30, 2013 4:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:04 PM
Points: 4,356, Visits: 6,186
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 at GMail
Post #1509702
Posted Wednesday, October 30, 2013 4:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 15,725, Visits: 28,130
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1509706
Posted Wednesday, October 30, 2013 5:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:22 AM
Points: 47, Visits: 263
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.
Post #1509719
Posted Wednesday, October 30, 2013 5:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 15,725, Visits: 28,130
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1509732
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse