August 26, 2008 at 1:15 am
Hi,
I am trying to find out why we have deadlocks on the SQL Server 2008 test box. I have the deadlock Event Data below. It refers to
waitresource="KEY: 6:72057594061062144 (7b00162552d9)"
but I can not find an object with 72057594061062144 id in the database. From SQL 2000 Experience it would mean DBID = 6 object_id = 72057594061062144 and page number = 7b00162552d9
Does it work differently in SQL 2008? This makes it more difficult to find out what could be causing the deadlocks.
Thanks.
UPDATE [dbo].[Page]
-- WITH (ROWLOCK)
SET
[BundleID] = @BundleID,
[Filename] = @Filename,
[FileTypeCode] = @FileTypeCode,
[FileTypeDate] = @FileTypeDate,
[PageNumber] = @PageNumber,
[PageSetID] = @PageSetID,
[UpdateUser] = @User,
[UpdateDate] = @Date
WHERE
[PageID] = @PageID
-- Store the result of the update statement
Proc [Database Id = 6 Object Id = 2027870291]
UPDATE [dbo].[Page]
-- WITH (ROWLOCK)
SET
[BundleID] = @BundleID,
[Filename] = @Filename,
[FileTypeCode] = @FileTypeCode,
[FileTypeDate] = @FileTypeDate,
[PageNumber] = @PageNumber,
[PageSetID] = @PageSetID,
[UpdateUser] = @User,
[UpdateDate] = @Date
WHERE
[PageID] = @PageID
-- Store the result of the update statement
Proc [Database Id = 6 Object Id = 2027870291]
UPDATE [dbo].[Page]
-- WITH (ROWLOCK)
SET
[BundleID] = @BundleID,
[Filename] = @Filename,
[FileTypeCode] = @FileTypeCode,
[FileTypeDate] = @FileTypeDate,
[PageNumber] = @PageNumber,
[PageSetID] = @PageSetID,
[UpdateUser] = @User,
[UpdateDate] = @Date
WHERE
[PageID] = @PageID
-- Store the result of the update statement
Proc [Database Id = 6 Object Id = 2027870291]
August 26, 2008 at 1:40 am
Yeah, it's different in 2005 and 2008. That number's too large to be an object ID. It's actally a partitionID. To get the objectID back -
select object_id from sys.partitions where partition_id = 72057594061062144
Do you have the deadlock graph as an xml doc? If so, can you zip it and attach? I'm not sure how to interpret what you posted.
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
August 26, 2008 at 2:03 am
Attached the file. Had to change the extension from .xdl to .txt.
I've actually posted it in the first message, but I did not realise that all the tags were removed.
August 26, 2008 at 11:48 pm
GilaMonster (8/26/2008)
Yeah, it's different in 2005 and 2008. That number's too large to be an object ID. It's actally a partitionID. To get the objectID back -
select object_id from sys.partitions where partition_id = 72057594061062144
BTW, for some reason, this query did not return any results.
August 27, 2008 at 1:36 am
Strange. Did you run it in database 6?
Have you dropped/truncated any tables since the deadlock message? It could account for why the partition is no longer there.
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
August 27, 2008 at 5:51 pm
GilaMonster (8/27/2008)
Strange. Did you run it in database 6?Have you dropped/truncated any tables since the deadlock message? It could account for why the partition is no longer there.
Yes I did run it in db 6 and I don't think anything was dropped since the deadlock happened.
What about the deadlock itself? It involves three identical update statements. So I can't blame different SPs locking object in different order. My only thought is that due to the fact it does a page lock it also locks other rows, but this should only cause normal blocking as single row update should not require multiple pages to be locked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply