How to get owner and locked object from locks_lock_timeouts_greater_than_0 event in SQL Server?
Asked Answered
P

1

8

I am capturing locks_lock_timeouts_greater_than_0 events using Extended Events in SQL Server. The event session is following:

CREATE EVENT SESSION MyQuery ON SERVER
    ADD EVENT sqlserver.locks_lock_timeouts_greater_than_0
    (
        ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    )
    ADD TARGET package0.ring_buffer (SET max_memory = 4096)
    WITH (max_dispatch_latency = 1 seconds)

How can I determine what object is locked and who is currently locking it?

UPDATE: I need this for SQL Server 2008. How to debug lock timeouts on pre-2012 versions?

Prewitt answered 20/6, 2012 at 14:46 Comment(0)
I
2

It looks like the information you want is not captured by this event.

You can see what fields are available for an event with:

select p.name package_name, o.name event_name, c.name event_field, c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
      on o.package_guid = p.guid
join sys.dm_xe_object_columns c
      on o.name = c.object_name
where o.object_type = 'event'
  AND o.name = 'locks_lock_timeouts_greater_than_0'
order by package_name, event_name

the result is:

sqlserver   locks_lock_timeouts_greater_than_0  ID  uint16  readonly
sqlserver   locks_lock_timeouts_greater_than_0  UUID    guid_ptr    readonly
sqlserver   locks_lock_timeouts_greater_than_0  VERSION uint8   readonly
sqlserver   locks_lock_timeouts_greater_than_0  CHANNEL etw_channel readonly
sqlserver   locks_lock_timeouts_greater_than_0  KEYWORD keyword_map readonly
sqlserver   locks_lock_timeouts_greater_than_0  count   uint64  data
sqlserver   locks_lock_timeouts_greater_than_0  lock_type   uint64  data

However, in SQL 2012 this event was replaced ( http://msdn.microsoft.com/en-us/library/ms144262.aspx ) by lock_timeout_greater_than_0 which has the following set of fields

sqlserver   lock_timeout_greater_than_0 UUID    guid_ptr    readonly
sqlserver   lock_timeout_greater_than_0 VERSION uint8   readonly
sqlserver   lock_timeout_greater_than_0 CHANNEL etw_channel readonly
sqlserver   lock_timeout_greater_than_0 KEYWORD keyword_map readonly
sqlserver   lock_timeout_greater_than_0 collect_resource_description    boolean customizable
sqlserver   lock_timeout_greater_than_0 collect_database_name   boolean customizable
sqlserver   lock_timeout_greater_than_0 resource_type   lock_resource_type  data
sqlserver   lock_timeout_greater_than_0 mode    lock_mode   data
sqlserver   lock_timeout_greater_than_0 owner_type  lock_owner_type data
sqlserver   lock_timeout_greater_than_0 transaction_id  int64   data
sqlserver   lock_timeout_greater_than_0 database_id uint32  data
sqlserver   lock_timeout_greater_than_0 lockspace_workspace_id  ptr data
sqlserver   lock_timeout_greater_than_0 lockspace_sub_id    uint32  data
sqlserver   lock_timeout_greater_than_0 lockspace_nest_id   uint32  data
sqlserver   lock_timeout_greater_than_0 resource_0  uint32  data
sqlserver   lock_timeout_greater_than_0 resource_1  uint32  data
sqlserver   lock_timeout_greater_than_0 resource_2  uint32  data
sqlserver   lock_timeout_greater_than_0 object_id   int32   data
sqlserver   lock_timeout_greater_than_0 associated_object_id    uint64  data
sqlserver   lock_timeout_greater_than_0 duration    uint64  data
sqlserver   lock_timeout_greater_than_0 resource_description    unicode_string  data
sqlserver   lock_timeout_greater_than_0 database_name   unicode_string  data

From this I was able to derive the database (database_id) and in my case table (variously in object_id, associated_object_id, resource_0) from their Ids.

I didn't see an obvious way to find who was locking the object from the data captured by this event.

EDIT - see SQL Server Lock Timeout Exceeded Deleting Records in a Loop for an example of using sp_lock and sp_who2 to debug the cause of a lock timeout event.

Isabelisabelita answered 22/6, 2012 at 23:34 Comment(4)
+1 Thank you for the information about changes in SQL Server 2012. Unfortunately, I need this information on SQL Server 2008. Maybe, someone else will found an workaround for SQL Server 2008.Prewitt
Why have you set a lock timeout, normally it will wait an unlimited amount of time if there is not a deadlock?Isabelisabelita
The application has a user interface.Prewitt
Well, then I suggest from your application you trap the error and capture the information such as from sp_lock and sp_who2 to see what has locks at the point of failure. See link at the end of my post above. What did you set your lock timeout to?Isabelisabelita

© 2022 - 2024 — McMap. All rights reserved.