Did you know? -- New Deadlock Priorities

re: Did you know? -- New Deadlock Priorities

Hugo Kornelis — Sat, 09 Sep 2006 17:03:06 GMT

Hi Kalen,

The answer to your final question (what will happen if everyone gives themselves the highest priority) is that the end result will be the same as when nobody uses this option. On a deadlock, SQL Server will first compare deadlock priorities; if they are equal, the deadlock victim is the one that's the least expensive to rollback.

So nobody setting a deadlock priority or everybody setting the maximum deadlock priority yields the same end results.

re: Did you know? -- New Deadlock Priorities

Hugo Kornelis — Sat, 09 Sep 2006 17:04:36 GMT

Oops, forgot to add....

If you wish to receive more comments, allowing anonymous comments might help. (Though in all honesty, I have anonymous comments enabled on my site, and I'm not exactly swamped with comments either)

re: Did you know? -- New Deadlock Priorities

Matthew Martin — Sat, 09 Sep 2006 23:26:13 GMT

That's cool, I'll probably use that. I would probably put UPDATES/INSERTS on high priority and SELECTS on low priority, but one would imagine that connections just doing SELECTS are the first candidates for deadlock kills anyhow. Unless deadlock victims are normally chosen at random--

I'm an avid reader of SQL blogs, and even famous sorts like Kim Tripp and Bill Vaugn have bloglines.com subscribers in the single and double digits. (RSS subscriptions being a really rough gauage of readership)

As soon as the book ships, the comments will be numerous to be a hassle.

re: Did you know? -- New Deadlock Priorities

Adam Machanic — Sun, 10 Sep 2006 18:09:14 GMT

Kalen,

Be patient -- you just started last week and not many people know you're here yet! However, you were included in today's Database Daily update from SSC, so that will help a bit. And as Hugo pointed out, you have anonymous comments disabled at the moment. If you enable that option, you may see more comments from people who don't want to sign up. Personally, I have them disabled as I don't want any kind of blog SPAM.

re: Did you know? -- New Deadlock Priorities

Adam Machanic — Sun, 10 Sep 2006 18:10:29 GMT

Looking again after signing out, I guess you do have anonymous comments enabled... get ready for the flood ;-)

re: Did you know? -- New Deadlock Priorities

Kalen Delaney — Sun, 10 Sep 2006 22:20:48 GMT

Hi Adam

Yes, I know I just started ... but there were all these 'views' and no one was saying anything. Besides, I had a smiley there. :-) I just wanted readers to know that comments are more than welcome.

And I've certainly got plenty to do while waiting for all the comments to start rolling in.

--Kalen

re: Did you know? -- New Deadlock Priorities

ACALVETT — Thu, 14 Sep 2006 09:28:28 GMT

I just heard about your blog and it brought a big smile to my face. Looking forward to reading more great articles.

re: Did you know? -- New Deadlock Priorities

Richard — Mon, 18 Sep 2006 07:56:00 GMT

Hi

Slightly nervous about posting in such exalted company! Will be keeping a close eye on your blog now I have seen it.

re: Did you know? -- New Deadlock Priorities

TKnoob — Thu, 21 Sep 2006 16:32:03 GMT

Just saw your blog. Love your books. Please continue to blog away. And thanks.

re: Did you know? -- New Deadlock Priorities

Why 21? — Mon, 25 Sep 2006 08:38:18 GMT

What is the point in having 21 possible values when three is enough?

re: Did you know? -- New Deadlock Priorities

Kevin3NF — Thu, 02 Nov 2006 22:08:00 GMT

Hi Kalen,

I just stumbled across this site today, and this was the second post I read. I've been studying deadlocks recently, so this was quite timely.

Thanks,

Kevin3NF

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio

Kalen Delaney — Tue, 17 Jul 2007 22:16:57 GMT

In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections

re: Did you know? -- New Deadlock Priorities

Rob — Mon, 23 Jul 2007 11:13:29 GMT

This was very helpful Kalen. Very interesting information. I am going to take advantage of this in SQL Server 2005 now.

re: Did you know? -- New Deadlock Priorities

kalen — Fri, 20 Jun 2008 19:43:41 GMT

I have spent 2 hours looking for an answer and you are the only one who gave it to me. Thank you.

re: Did you know? -- New Deadlock Priorities

Jeremy Odom — Mon, 04 Jun 2012 16:00:40 GMT

Is it best to set DEADLOCK_PRIORITY from within your stored procedure or just before the stored proc is called?

re: Did you know? -- New Deadlock Priorities

Kalen Delaney — Mon, 04 Jun 2012 16:54:07 GMT

Jeremy

I recommend setting it only in the scope in which you need it. If you only need it for the proc, set it in the proc, and then when the proc is over, it will be automatically reset to the value it was before.

~Kalen