RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

SQL 2005 and Deadlocks

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.


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.

Print | posted on Wednesday, February 04, 2009 10:42 AM |



# re: SQL 2005 and Deadlocks

Anyway, how can a comment cause a deadlock especially with partitions? Any more details fo how you are getting it?
2/4/2009 2:00 PM | MS SQL DBA

# 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.
2/4/2009 2:06 PM | rocketscientist
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET