SQL 2005 and Deadlocks

Wed Feb 4, 2009 by Chris Miller

So I've got a deadlock issue I'm having trouble tracking down.

I have the deadlock tracking trace flag turned on, and this is the part I'm having trouble with:
KEY: 5:72057594325499904

From everything I've found, the layout of that item is supposed to be:
KEY: [Db_ID]:[Object_ID]:[Index_ID]

That 7205… number is way too large to be an object_id (those are all type int, and that number is a bigint).

Any idea what's going on?

Oh, and the line number part of the deadlock message leads me to believe the deadlock is happening in a comment. It says line 9, there's 20 lines of comment block at the top of the proc.

EDIT:

So I figured it out.

That's a partition_ID. You can cross-reference it to an object_id in sys.partitions.

It's the stupid sequencing thing this stupid vendor is using instead of using identities like any sane person.

Legacy Comments


MS SQL DBA 2009-02-04	re: SQL 2005 and Deadlocks Hm... Anyway, how can a comment cause a deadlock especially with partitions? Any more details fo how you are getting it?

rocketscientist 2009-02-04	re: SQL 2005 and Deadlocks That's the point. The comment isn't causing the error, the line numbers are vastly wrong. Actually anything that posits a line number is soemwhat suspect anyway in my experience. It's happening in a table that is used to hold sequence numbers. Back in the bad old days before we had identity columns, that's how you'd sort-of fake an identity column. The solution is fraught with peril because of locking and contention issues in that table (further compounded by page-level locking, so you'd make each row take up a full page to prevent contention...). It's a really really dumb thing to do. So of course this vendor chose this solution to the problem. I think, honestly, that they go out of their way to do things the dumb way.