Deadlock Scenario

Question

Deadlock Scenario

kingscriber

Say Hey Kid

Points: 705
More actions
January 15, 2010 at 8:19 am

#214607

Hello, this is my first post on ssc. I am not even sure if this is the correct forum for this question. We'll see.
I currently have a nasty deadlock scenario that I can reproduce just about every test run that I do. Doing some profiling as well (selected everything in the "Locks" section). That is working well and I can see the Deadlock graph as well.
I also enabled 1204, 1222, and 3605 for tracing. I can see those logs and make 'enough' sense out them as well as narrow down which stored procs and line numbers that are causing the issue.
My biggest question is that I can't find the second resource that is locked by node 2.
Node 1 does a delete on a table that I have a delete trigger on. That trigger does an update on another table (does an exclusive lock on that table). Node 1 is attempting to do while another process or thread (from what it looks like) accesses the same stored proc, and is just trying to do a select on the table that node 1 has an exclusive lock on.
I am initially thinking that this doesn't constitute as a deadlock since msdn doesn't really give a scenario that it is a deadlock here: http://msdn.microsoft.com/en-us/library/ms178104.aspx
I would think that node 2 would need to have a lock on the table that it is selecting, and trying to access a table that has an exlusive lock via node 1.
Am I correct in my thinking ? Does node 2 just need to attempt a table scan (select) while node 1 has a lock on the table it is attempting to update?
[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Viewing 15 posts - 1 through 15 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

The only traceflag you need is 1222. 1204 is the old one from SQL 2000, the info it produces is far less useful than what 1222 outputs. 3605 is used to direct certain outputs (like from DBCC Page) to the error log. It has no use with deadlock scenarios.

Can you post the deadlock graph generated by traceflag 1222 please?

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 2

Sure. Not sure if you can post files. I am using the IFCode for xml coding.

<deadlock-list>

<deadlock victim="process89af28">

<process-list>

<executionStack>

<frame procname="WorkflowHPal.dbo.FindEditionPlateSeparation" line="24" stmtstart="1322" stmtend="1652" sqlhandle="0x0300140037dbf7648cedb400b49b00000100000000000000">

SELECT @EditionPlateSeparationId = EditionPlateSeparationId

FROM EditionPlateSeparations

WHERE EditionPlateId = @EditionPlateId AND ColorId = @ColorId </frame>

<frame procname="WorkflowHPal.dbo.UpdateSchedulePlatesColor" line="32" stmtstart="2222" stmtend="2498" sqlhandle="0x030014005490bc691deeb400b49b00000100000000000000">

EXEC FindEditionPlateSeparation @SchedulePlateId, @ColorId, 1, @PriorityId, @ReleaseTime, @HoldAt, @Deadline, @PlateSeparationId OUTPUT </frame>

<frame procname="WorkflowHPal.dbo.UpdateSchedulePagesColor" line="122" stmtstart="8880" sqlhandle="0x030014001b6cc86806eeb400b49b00000100000000000000">

Exec UpdateSchedulePlatesColor @SchedulePlateId, @Temp </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 20 Object Id = 1757965339] </inputbuf>

</process>

<executionStack>

<frame procname="WorkflowHPal.dbo.SchedulePlateSeparations_Deleted" line="14" stmtstart="786" stmtend="1016" sqlhandle="0x03001400e69707690ba215019d9a00000000000000000000">

UPDATE EditionPlateSeparations SET Scheduled = 0 WHERE EditionPlateSeparationId = @PlateSeparationId </frame>

<frame procname="WorkflowHPal.dbo.UpdateSchedulePlatesColor" line="21" stmtstart="1272" stmtend="1428" sqlhandle="0x030014005490bc691deeb400b49b00000100000000000000">

DELETE FROM SchedulePlateSeparations WHERE SchedulePlateId = @SchedulePlateId </frame>

<frame procname="WorkflowHPal.dbo.UpdateSchedulePagesColor" line="122" stmtstart="8880" sqlhandle="0x030014001b6cc86806eeb400b49b00000100000000000000">

Exec UpdateSchedulePlatesColor @SchedulePlateId, @Temp </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 20 Object Id = 1757965339] </inputbuf>

</process>

</process-list>

<resource-list>

<keylock hobtid="72057595767488512" dbid="20" objectname="WorkflowHPal.dbo.EditionPlateSeparations" indexname="PK_EditionPlateSeparations" id="lockc1920c0" mode="RangeS-S" associatedObjectId="72057595767488512">

<owner-list>

<owner id="process89af28" mode="RangeS-S"/>

</owner-list>

<waiter-list>

<waiter id="process928b68" mode="X" requestType="wait"/>

</waiter-list>

</keylock>

<keylock hobtid="72057595767488512" dbid="20" objectname="WorkflowHPal.dbo.EditionPlateSeparations" indexname="PK_EditionPlateSeparations" id="lock1276ea40" mode="X" associatedObjectId="72057595767488512">

<owner-list>

<owner id="process928b68" mode="X"/>

</owner-list>

<waiter-list>

<waiter id="process89af28" mode="RangeS-S" requestType="wait"/>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

I'm going to stick this in the queue to be looked at over the weekend, cause deadlocks take time to work out.

As for the resources involved in the deadlock:

SPID 62: Running in serializable isolation level (any reason why?) Owned a range lock in shared mode on a key in the table EditionPlateSeparations and wanted a different range lock in shared mode on the same table.

SPID 58: Running in repeatable read isolation level. Owned a key log in exclusive mode. Wanted a different key lock, also in exclusive mode.

It's a fairly standard 2-resource deadlock, even though the 2 resources are different keys in the same table.

Can you post (preferably as text files zipped) the following stored procs?

WorkflowHPal.dbo.FindEditionPlateSeparation

WorkflowHPal.dbo.UpdateSchedulePlatesColor

WorkflowHPal.dbo.UpdateSchedulePagesColor

WorkflowHPal.dbo.SchedulePlateSeparations_Deleted (might be a trigger)

Also, can you post the structure and all indexes on the table EditionPlateSeparations?

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 4

Can't seem to find a way to post a zip file of that information. Maybe because I am a newbie poster?

Edit: Check out my post below

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

kingscriber Say Hey Kid Points: 705 More actions · Answer 5

ok I figured it out...it was all the way at the bottom of the page. Anyways, it is called SPandTableInfo.zip. It has everything you are requesting in there. Thank you in advance !

As for the isolation level access to that table as serializable, there is no real reason why. I am not even sure why it determined that.

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

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

kingscriber (1/15/2010)
As for the isolation level access to that table as serializable, there is no real reason why. I am not even sure why it determined that.

SQL doesn't decide on isolation levels. If it's something other than read committed, that isolation level was explicitly set by the client, or in a stored proc

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 7

GilaMonster (1/15/2010)
SQL doesn't decide on isolation levels. If it's something other than read committed, that isolation level was explicitly set by the client, or in a stored proc

Well, I did some digging through the entire database. I don't specify any type of isolation level in any trigger or sproc, etc. So purely from running sprocs, SQL would be using the default, and in my undestanding, it would be using read committed.

Also, my .Net Data Provider would be using the default there as well. I am getting this error due to the same application with multiple threads calling into a web service with the same method. That method doesn't perform any mechanisms for transactions. My guess the default isolation level for the .Net Data Provider for Data Reads is serializable.

Either way, I am uncertain how in the report that was generated that I am getting two different isolation levels for the same procedfure, same sproc on two different processes.

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

In many cases, you can resolve deadlocks by setting the database to read_committed_snaphot as the default isolation level.

ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT ON;

Using Row Versioning-based Isolation Levels

http://msdn.microsoft.com/en-us/library/ms179599.aspx

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

kingscriber (1/15/2010)
My guess the default isolation level for the .Net Data Provider for Data Reads is serializable.

I'm not a .net dev, so can't answer that one. Can you investigate and see if you can set an explicit isolation level. It should completely prevent this deadlock.

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 10

Yes I can set the isolation level for my .Net Data Provider. It sounds like that might be the way to go anyways.

I did do this:

set transaction isolation level read committed

in the UpdateSchedulePagesColor sproc. That fixed this deadlock, and I had to put it in others as well. However, I am not certain if that IS the fix or not. Here is my best assumption:

Since the one node (the first process) had an isolation level of serializable and it was attempting to do a select, while the other node had an isolation level of read committed attempting to do an update, forcing the sproc to be read committed doesn't allow an exclusive lock during the select. Does my assumption seem correct for this fix?

Another question. If I am calling this sproc from two different processes, using the same .Net Data Provider, how is it that one process is using read committed and one is using serializable?

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

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

kingscriber (1/16/2010)
Forcing the sproc to be read committed doesn't allow an exclusive lock during the select. Does my assumption seem correct for this fix?

Incorrect. Your selects were taking shared range locks, not exclusive locks.

If you look at the deadlock graph that you posted, the process doing the update was the one that had the exclusive locks.

Isolation level will never cause or prevent an exclusive lock. All the serialisable was forcing was the range shared locks which, since they lock entire ranges of the table, tend to cause more blocking and deadlocks than might otherwise occur.

Another question. If I am calling this sproc from two different processes, using the same .Net Data Provider, how is it that one process is using read committed and one is using serializable?

Without seeing the .net code, absolutely no idea. At a guess, one is setting the isolation level somewhere and the other isn't.

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 12

Allow me to try to get this right. I think it would be rude for me to be ignorant of the specifics of what you are trying to say.

SELECT

lockMode="RangeS-S"...isolationlevel="serializable (4)"

UPDATE

lockMode="X"...isolationlevel="read committed (2)"

What I think you are trying to get me to see is that isolation level and lockModes are two different "entities". However they do effect each other, but not always.

Different isolation levels will affect how locks are placed when data is being read, such as the select statement that I have above. If the select statement wasn't serializable, and it was read-committed, then there wouldn't be a ranged shared lock, but just a shared lock.

When you mention that Isolation Level will never cause or prevent an exclusive lock, that makes sense, because from what I am reading from this book, an exclusive lock will only be granted if the data is being modified, and in my case the UPDATE statement has an "X" lockMode.

Also I will throw this in. Isolation levels can minimize deadlocks, but wil not completely cure them. --> I hope I got that one right.

Anyways I am hoping that my thought process above is correct. I do have one more question. When I am using the SQL profiler in 2005, I can select an event to be called "Lock:Timeout". I seem to be getting quite a bit of those in the environment that I am testing. Is that a normal/expected behavior of the concurrency testing that I am performing?

[font="Tahoma"]forgive them, for they do not know what they do.[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 13

kingscriber (1/18/2010)
What I think you are trying to get me to see is that isolation level and lockModes are two different "entities". However they do effect each other, but not always.

Pretty much, yes.

If the select statement wasn't serializable, and it was read-committed, then there wouldn't be a ranged shared lock, but just a shared lock.

100% correct

When you mention that Isolation Level will never cause or prevent an exclusive lock, that makes sense, because from what I am reading from this book, an exclusive lock will only be granted if the data is being modified, and in my case the UPDATE statement has an "X" lockMode.

Or if a locking hint is specified requesting an exclusive lock.

Also I will throw this in. Isolation levels can minimize deadlocks, but wil not completely cure them. --> I hope I got that one right.

Maybe. Rather say that a higher-than-necessary isolation level will cause or worsen deadlocks.

When I am using the SQL profiler in 2005, I can select an event to be called "Lock:Timeout". I seem to be getting quite a bit of those in the environment that I am testing. Is that a normal/expected behavior of the concurrency testing that I am performing?

Excessive locking timeout are not normal. It tells me that you've got the lock timeout set too low or you have excessive blocking. I would suggest, given that and your deadlocks, that you look at doing some performance tuning on that system. If you have no one there that's familiar and experienced in doing so, consider getting an external consultant in to help out and teach.

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

kingscriber Say Hey Kid Points: 705 More actions · Answer 14

GilaMonster (1/18/2010)
Excessive locking timeout are not normal. It tells me that you've got the lock timeout set too low or you have excessive blocking. I would suggest, given that and your deadlocks, that you look at doing some performance tuning on that system. If you have no one there that's familiar and experienced in doing so, consider getting an external consultant in to help out and teach.

Thank you for your comments and your patience. I have learned quite a bit from this post.

I think I may have an idea as to why the isolation level was changed from one node to another.

I know that cursors can run slow. I am thinking that when you open a cursor (still trying to find an exact answer) it sets a different isolation level for anything containing the cursor. I don't know if anyone can verify this.

[font="Tahoma"]forgive them, for they do not know what they do.[/font]