Deadlocks, wiats, deadlocks

Question

Deadlocks, wiats, deadlocks

Viewing 4 posts - 16 through 18 (of 18 total)

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Thanks to all in this discussion.

I had 4 update statements (2 updates and 2 merges ) in one transaction and I isolated them in separate transactions.

On the update statements i put OPTION (MAXDOP 1) to put them execute with serial plans, and for the merge statements i didn't, because i want/need them execute faster.

What I see so far is losing of the keylocks but there are still pagelocks. However, the pagelocks are about 1/5 of the keylocks.

I'll try to experiment with the same option for the merge statements, but the picks come for the weekends and don't know how it'll behave...

I can not do very relevant tests on the test environment, unfortunately.

Igor Micev,My blog: www.igormicev.com

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

Igor Micev (12/30/2014)
I had 4 update statements (2 updates and 2 merges ) in one transaction and I isolated them in separate transactions.

Which means you no longer have atomicity for those statements, if the last one fails, there's no way to roll back the first, if there's a connection failure the first may complete and no others. Is that acceptable?

Usually if there are multiple data modifications in a transaction, it's so that they all behave atomically and all can be rolled back if anything goes wrong.

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

GilaMonster (12/31/2014)
Igor Micev (12/30/2014)
I had 4 update statements (2 updates and 2 merges ) in one transaction and I isolated them in separate transactions.
Which means you no longer have atomicity for those statements, if the last one fails, there's no way to roll back the first, if there's a connection failure the first may complete and no others. Is that acceptable?
Usually if there are multiple data modifications in a transaction, it's so that they all behave atomically and all can be rolled back if anything goes wrong.

I'm aware of that, and that is handled. We have logic when any of them fails.

Thanks

Igor Micev,My blog: www.igormicev.com

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 4

Next time you get one of these it may speed things along if you post the deadlock graph. There are several classes of deadlocks and nobody can determine the cause based off of the limited info you gave.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE: