Home Dashboard Directory Help
Search

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


Status: 

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)
view
0
User(s) can reproduce this bug

Description

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
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