Search

Certain DMVs causing arithmetic overflow during "deadlock" by Brandon Tucker

Closed
as By Design Help for as By Design

2
0
Sign in
 to vote
Type: Bug
ID: 276695
Opened: 5/10/2007 6:43:46 PM
Access Restriction: Public
1
Workaround(s)
0
User(s) can reproduce this bug
During a deadlock-ish server state, a query being used to examine the problem threw an 8115/arithmetic overflow error. Three views being INNER JOINed in the query were sys.dm_tran_locks, sys.sysprocesses, and sys.databases. A CROSS APPLY with sys.dm_exec_sql_text was then being used. The query executed as expected before the deadlock then threw the error during it. sys.dm_tran_locks and sys.dm_exec_sql_text seemed to work correctly when being run on their own.

SELECT
    db.[name]
    , l.resource_type
    , COALESCE(
        OBJECT_NAME(l.resource_associated_entity_id),
        OBJECT_NAME(qt.[objectid])
    ) AS [object_name]
    , l.request_mode
    , l.request_type
    , l.request_status
    , sp.blocked
    , sp.lastwaittype
    , sp.status
    , sp.hostname
    , sp.program_name
    , sp.cmd
    , sp.loginame
    , qt.[text]
FROM sys.dm_tran_locks l
    JOIN sys.databases db
        ON l.resource_database_id = db.database_id
    JOIN sys.sysprocesses sp
        ON l.request_session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt
Details (expand)
Product Language
English

Version

SQL Server 2005 - Developer Edition

Category

SQL Engine

Operating System

Windows Vista
Operating System Language
US English
Steps to Reproduce
-Create Transaction T1.
-Delete a row from a table using a WITH (ROWLOCK) hint (or similar operation to acquire an X-lock). Do not commit the transaction.
-Create Transaction T2.
-Using T2, acquire an X-lock on a different table, attempting to update the row being locked by T1. Do not release the lock.
***deadlock state***
-Attempt to run a query joining the aforementioned views/procs.
Actual Results
Error 8115, arithmetic overflow error when trying to convert to int.
Expected Results
Beautiful, flowing information regarding the state of the database locks, who's acquiring them, and what statement is causing a block. =)

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 8/9/2007 at 11:00 AM
Hi Sleip,
Thanks for using our product and reporting the problem. We've looked at the scenario and I think the problem is in the query, specifically OBJECT_NAME(l.resource_associated_entity_id) .

sys.dm_tran_locks.resource_associated_entity_id is bigint where OBJECT_NAME takes an int. Where l.resource_type is not OBJECT, l.resource_associated_entity_id can be greater than INT_MAX and cause overflow. You can revise the queries like Razvan sugguested in the workaround (https://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=276695), or adding a filter such as (WHERE l.resource_type = 'OBJECT') to avoid the overflow.

Hope this helps,

-Haitao
Sign in to post a workaround.
Posted by Razvan Socol on 5/13/2007 at 9:43 AM
Use one of the following queries:

SELECT
    db.[name]
    , l.resource_type,
    , OBJECT_NAME(
        CASE WHEN l.resource_associated_entity_id<=2147483647
        THEN l.resource_associated_entity_id ELSE qt.[objectid] END
    ) AS [object_name]
    , l.request_mode
    , l.request_type
    , l.request_status
    , sp.blocked
    , sp.lastwaittype
    , sp.status
    , sp.hostname
    , sp.program_name
    , sp.cmd
    , sp.loginame
    , qt.[text]
FROM sys.dm_tran_locks l
    JOIN sys.databases db
        ON l.resource_database_id = db.database_id
    JOIN sys.sysprocesses sp
        ON l.request_session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt

SELECT
    db.[name]
    , l.resource_type,
    , OBJECT_NAME(
        CASE WHEN l.resource_associated_entity_id<=2147483647
        THEN l.resource_associated_entity_id
        ELSE COALESCE(p.object_id,qt.[objectid]) END
    ) AS [object_name]
    , l.request_mode
    , l.request_type
    , l.request_status
    , sp.blocked
    , sp.lastwaittype
    , sp.status
    , sp.hostname
    , sp.program_name
    , sp.cmd
    , sp.loginame
    , qt.[text]
FROM sys.dm_tran_locks l
    JOIN sys.databases db
        ON l.resource_database_id = db.database_id
    JOIN sys.sysprocesses sp
        ON l.request_session_id = sp.spid
    LEFT JOIN sys.partitions p ON p.partition_id=resource_associated_entity_id
    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt

Razvan
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement