I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: Get full information about transaction locks

Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...

With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:

 

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

 

Let's look at it one DMV at a time from top to bottom:

sys.dm_tran_locks:

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.

sys.partitions:

Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID'  and 'Key'. With this join we get the object_id of our locked table.

sys.objects:

Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.

sys.dm_exec_sessions:

Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.

sys.dm_tran_session_transactions:

Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.

sys.dm_tran_active_transactions:

Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.

sys.dm_exec_connections:

Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.

sys.dm_exec_sql_text:

Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.

 

By applying the filter in the where clause you get the answers to questions like:

- What SQL Statement is causing the lock?

- Which user has executed the SQL statement that's holding the locks?

- What objects/tables are being locked?

- What kinds of locks are being held and on which pages, keys, RID's?

- etc...

 

kick it on DotNetKicks.com

 

 

Print | posted on Tuesday, April 29, 2008 8:01 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# Great

This is easier to use than ABA_LockInfo and Beta_LockInfo

Thanks.
4/30/2008 9:52 AM | Peso
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

what are those, peter?
4/30/2008 11:42 AM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

Must be run in the database with the problem to get object name. A view like sys.dm_tran_locks seems to work across the server, but sys.objects only works for the database that the query is being run in. I ran into this problem when I tried to create a block monitoring query. I don't always know which database the blocks are in and want to run one query on the server to monitor all blocking. It seems very inefficient to try to run a query inside each database.

Just something to think about. - JJ
5/5/2008 7:33 PM | JJEugene
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

then join to sys.all_objects instead of sys.objects
5/5/2008 7:34 PM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

sys.all_objects. Hmmm. According to books on-line, sys.all_objects: "Shows the UNION of all schema-scoped user-defined objects and system objects." If I understand this text correctly, sys.all_objects is even more limited than sys.objects since sys.all_objects only applies to the schema's scope, though it does return system objects too.

To confirm my understanding, I did a search in sys.all_objects for a table name that I know was in a different database. I got back zero return recordsets. From this experiment, I'm thinking that sys.all_objects does not resolve the issue. Thanks for your thought though. - JJ
5/7/2008 12:47 AM | JJEugene
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

If you could please attach some SCREEN-SHOT for the below listed, that may be a great value add to this BLOG

1. SQL QUERY / Stored Procedure / SQL View... that causes this LOCK
2. Attach Sample result of your SQL QUERY / Stored Procedure / SQL View
3. Attach result of your above SQL QUERY (the one using the DMV)
4. Attach Sample shot, on how to read the result of Point # 3

In short, I'm saying, the above query will help understanding if little more information is added :)
5/7/2008 1:30 AM | PP
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

yes you have to run it in the database you wish to inspect.
5/7/2008 6:34 PM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

ahhh :)
you have to set the compatibility level for your databases to 90 to be able to run this.
cross apply is only available in 90 compatibiliy level.
5/7/2008 10:00 PM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

removing the joins to sys.objects & sys.partitions and resolving name from sys.dm_tran_locks directly seems to get the desired result regardless of db context

...
l.resource_type [LockedResourceType],
case
when resource_type = 'database' then db_name(l.resource_database_id)
when resource_type = 'object' then object_name(l.resource_associated_entity_id, l.resource_database_id)
else 'n/a'
end [LockedObjectName],
...

- N
7/17/2008 10:30 PM | Nathan Skerl
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

indeed... but i had problems with using object_name because some l.resource_associated_entity_id's were bigger than the int that object_name accepts
7/17/2008 10:47 PM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

I get the Below error....Wats wrong with this......

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '.'.
11/20/2008 2:00 PM | Raja
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

are you maybe trying to run this query on sql server 2000 or have a 8.0 compatibility level set on your database?
11/20/2008 2:02 PM | Mladen
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

Well, nobody mentioned this workaround to eliminate the reference to sys.objects - and it has been quite sometime. This can be used to run in the context of any database:

...
OBJECT_NAME(p.object_id, l.resource_database_id) AS LockedObjectID,
...
10/21/2009 7:35 PM | Tony
Gravatar

# re: SQL Server 2005: Get full information about transaction locks

Thanks a lot, it was a great help :)
12/2/2009 11:30 AM | newbie
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET