Deadlock Question

Question

Deadlock Question

Viewing 14 posts - 16 through 28 (of 28 total)

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

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

Hawkeye_DBA (1/4/2012)
Hi Gail,
I am not aware that I can change that? If so, I'm all ears...

Serialisable's the default for .net, but it can be changed (somewhere) in the definition or properties of one of the data-access classes (I am not a .net programmer)

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

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 2

You don't think the I/O throughput on the db server could cause a bottleneck on the app server do you?

I am not seeing any network queuing or disk queueing on either server that would indicate a problem.

I'll attach the XML trace if you want it, but basically nothing there is showing me any real troubles, just the fact that it is executing the same statement within multiple threads at the same time.

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

Problems, yes. Deadlocks, probably not. Those are mostly code/index problems.

Are you absolutely sure there are no selects been run by this process? Updates don't take shared locks (they take U and then X), but you have shared locks in the deadlock graph.

p.s. What XML trace?

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 4

There's also this:

procname="adhoc" line="2" stmtstart="56"

So there's 56 characters of something before that update statement. Begin Transaction is only 18 characters (if it's been specified and not set by a .net function)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

January 4, 2012 at 10:31 am

#1428517

Gail ~~Shaw~~ Sherlock

😀

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 6

Hi, sorry for some reason my last post didn't go through.

Yeah, it's running a begin tran, commit tran and also the followign select before the update

Select dbo.AutoNumberSettings.* From dbo.AutoNumberSettings

Where dbo.AutoNumberSettings.[ClassName] = @className

the trace isn't an allowed extension so I exported the sql to .txt it does not include the deadlock event

Thanks again for your input,

Sandy

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 7

boy that's a nasty txt file, sorry bout that 🙁

perhaps you are on to something with the serializable isolation level

I am going to talk with the developers at the vendor about this and go that path.

Thanks again for your input, Happy New Year!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Hawkeye_DBA (1/4/2012)
boy that's a nasty txt file, sorry bout that 🙁
perhaps you are on to something with the serializable isolation level
I am going to talk with the developers at the vendor about this and go that path.
Thanks again for your input, Happy New Year!

The file gets processed as html file when you look it via browser. Download it and open it again and it'll be formatted correctly.

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

Ask your vendors to replace the select .. update combo with this:

UPDATE dbo.AutoNumberSettings

SET NextValue = NextValue + 1

OUTPUT DELETED.*

WHERE dbo.AutoNumberSettings.[ClassName] = @className

That will give exactly the same results and it cannot 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

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 10

Thanks Gail, I will email them with your suggestion.

Out of wanting to understand, how is it that this will not cause a deadlock, sorry if that's a loaded question lol

Sandy

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

It's one statement, not 2. The first update to run takes U locks, any other has to wait (U locks are not compatible with each other, S locks are), so there's no way that two queries can both start the transaction and deadlock each other.

Let's look at the original statement and see what happens when two operations on the same classname take place simultaneously (which is what was happening)

Time 1: Operation 1 takes shared lock on Row 5 (for eg)

Time 1: Operation 2 takes shared lock on Row 5 (fine at this point, shared locks are compatible with each other)

Time 2: Operation 1 starts the update and requests that the shared lock on row 5 be converted to X. This must wait, there's another session with a shared lock in place (operation 2)

Time 2: Operation 2 starts the update and requests that the shared lock on row 5 be converted to X. This must wait, there's another session with a shared lock in place (operation 1)

We're now in a deadlock. Each session is waiting for a resource the other has.

With the revised code, it now goes like this:

Time 1: Operation 1 takes a update lock on Row 5

Time 2: Operation 2 requests an update lock on Row 5 and must wait (update locks are not compatible with each other)

Time 3: Operation 1 converts the U lock to X, performs the update and outputs the old values

Time 4: Operation 1 releases the locks

Time 5: Operation 2 obtains its U lock, converts to X, performs the update, outputs the old values and releases the locks

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

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 12

Perfect! Thank you for the explanation, that makes total sense and I don't know why I didnt see it, doh me!

Ok, wish me luck with the vendor..

Thanks again,

Sandy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Hawkeye_DBA (1/4/2012)
Perfect! Thank you for the explanation, that makes total sense and I don't know why I didnt see it, doh me!
Ok, wish me luck with the vendor..
Thanks again,
Sandy

I sell voodoo dolls for those special cases.

How many boxes of those do you want? :hehe:

tfifield SSCrazy Eights Points: 9655 More actions · Answer 14

Hawkeye,

As Gail pointed out the reason for the deadlock is a read followed by an update - 2 queries.

There's another option, not quite as elegant as Gail's that will set a variable and do the update in 1 query, which should avoid any deadlock issues:

DECLARE

@NextValue INT

UPDATE dbo.AutoNumberSettings

SET

@NextValue = NextValue

, NextValue = NextValue + 1

WHERE dbo.AutoNumberSettings.[ClassName] = @className

I've used code like this for some time with no deadlocks.

Todd Fifield