Detecting if a row is locked

dghundt · Post by **dghundt** » Fri 20 Mar 2009 01:45

I have two separate applications that can both access my mssql database at the same time.
One program is proprietary, the other is my own. If proprietary program pulls up information from the database to view, it locks the row until we close the window.

If my application tries to update this locked row, right now I just get a timeout error for my sqlexception.

Code: Select all

Index #0
Class: 11
Number: -2
State: 0
Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This row could be locked for several minutes, so a long timeout is not a good choice for me.
How can I detect that the error is actually due to a locked row so I can handle it? Or better yet, can I detect ahead of time if the row is locked?
Thank you.

Shalex · Post by **Shalex** » Mon 23 Mar 2009 16:57

Here is a general information about locks in SQL Server: http://msdn.microsoft.com/en-us/library/ms186396.aspx . The following article can help you to get the list of database objects that are locked at the moment: http://msdn.microsoft.com/en-us/library ... L.80).aspx .

dghundt · Post by **dghundt** » Mon 23 Mar 2009 19:58

Appreciate your help. It took a lot of digging to find what you already knew!
I was able to find the spid and from it the sqlhandle which is supposed to contain the actual query text. Nothing I could see gave me the table name or row number locked (see long query). I assumed from the sqlhandle text I could parse the table or row being used. As you recall, I am trying to get info on locks created by closed/proprietary application.

Instead when I enter the sqlhandle into the query below,

Code: Select all

SELECT * FROM 
sys.dm_exec_sql_text(0x010009007A116D21781E15040000000000000000)

it simply returns fetch api_cursor....

Code: Select all

FETCH API_CURSOR000000000000033B

Here is the large query I used to find the sql handle for the spid. I cannot find any columns in all the datatables in this query that actually refer to the table and row locked. That is why I pulled the sqlhandle and looked for the query text associated with it.

Code: Select all

SELECT DISTINCT 
                      SP.spid, SP.hostname, SP.program_name, SP.loginame, 
                      (CASE A.REQUEST_MODE WHEN 'SCH-S' THEN 'SCHEMA STABILITY' WHEN 'SCH-M' THEN 'SCHEMA MODIFICATION' WHEN 'S' THEN 'SHARED' WHEN
                       'U' THEN 'UPDATE' WHEN 'X' THEN 'EXCLUSIVE' WHEN 'IS' THEN 'Intent Shared' WHEN 'IU' THEN 'Intent Update' WHEN 'IX' THEN 'Intent Exclusive' WHEN
                       'SIU' THEN 'Shared Intent Update' WHEN 'SIX' THEN 'Shared Intent Exclusive' WHEN 'UIX' THEN 'Update Intent Exclusive' WHEN 'BU' THEN 'Bulk Update'
                       WHEN 'RangeS_S' THEN 'Shared Key-Range and Shared Resourcelock' WHEN 'RangeS_U' THEN 'Shared Key-Range and Update Resource lock' WHEN
                       'RangeI_N' THEN 'Insert Key-Range and Null Resourcelock' WHEN 'RangeI_S' THEN 'Key-Range Conversion lock, created by an overlap of RangeI_N and S locks'
                       WHEN 'RangeI_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and U locks' WHEN 'RangeI_X' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and X locks'
                       WHEN 'RangeX_S' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_S locks' WHEN 'RangeX_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_U locks'
                       WHEN 'RangeX_X' THEN 'Exclusive Key-Range and ExclusiveResource lock' ELSE NULL END) AS REQUEST_LOCK_MODE, DB_NAME(SP.dbid) 
                      AS DATABASE_NAME, SP.sql_handle
FROM         sys.dm_tran_locks AS A INNER JOIN
                      sys.sysprocesses AS SP ON A.request_session_id = SP.spid LEFT OUTER JOIN
                      sys.dm_exec_requests AS B ON A.request_request_id = B.request_id LEFT OUTER JOIN
                      sys.dm_tran_active_transactions AS C ON A.request_owner_id = C.transaction_id
WHERE     (SP.spid > 50) AND (SP.program_name NOT LIKE 'SQLAGENT%') AND 
                      ((CASE A.REQUEST_MODE WHEN 'SCH-S' THEN 'SCHEMA STABILITY' WHEN 'SCH-M' THEN 'SCHEMA MODIFICATION' WHEN 'S' THEN 'SHARED' WHEN
                       'U' THEN 'UPDATE' WHEN 'X' THEN 'EXCLUSIVE' WHEN 'IS' THEN 'Intent Shared' WHEN 'IU' THEN 'Intent Update' WHEN 'IX' THEN 'Intent Exclusive' WHEN
                       'SIU' THEN 'Shared Intent Update' WHEN 'SIX' THEN 'Shared Intent Exclusive' WHEN 'UIX' THEN 'Update Intent Exclusive' WHEN 'BU' THEN 'Bulk Update'
                       WHEN 'RangeS_S' THEN 'Shared Key-Range and Shared Resourcelock' WHEN 'RangeS_U' THEN 'Shared Key-Range and Update Resource lock' WHEN
                       'RangeI_N' THEN 'Insert Key-Range and Null Resourcelock' WHEN 'RangeI_S' THEN 'Key-Range Conversion lock, created by an overlap of RangeI_N and S locks'
                       WHEN 'RangeI_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and U locks' WHEN 'RangeI_X' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and X locks'
                       WHEN 'RangeX_S' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_S locks' WHEN 'RangeX_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_U locks'
                       WHEN 'RangeX_X' THEN 'Exclusive Key-Range and ExclusiveResource lock' ELSE NULL END) = 'UPDATE')
ORDER BY SP.spid

Shalex · Post by **Shalex** » Wed 25 Mar 2009 07:48

You can find out the name of the locked object in the following way. Execute sp_lock - you will get the result set of the locked objects. Execute select object_name(ObjId) for every ObjId from the result set after the sp_lock execution - it will return the name of the locked object. Please note the returning value of the object_name function depends on the current database.

dghundt · Post by **dghundt** » Fri 27 Mar 2009 12:37

Thank you.
However, this unfortunately only gives me the table name for the row that is locked. I know the whole table is not locked since I can make changes to other rows in the same table. It appears that sp_lock is deprecated and sys.dm_tran_locks is now recommended for sql2005

Is there a way to find the key or row in the table that is locked? Then I will be able to test for locking and defer my query. Using sql server 2005.

Shalex · Post by **Shalex** » Mon 30 Mar 2009 08:51

It seems that there is no way to determine what particular row is locked. Only additional information like the text of the query can be received.

dghundt · Post by **dghundt** » Mon 30 Mar 2009 15:06

Thank you. If you ever find out something I'd appreciate hearing.

Devart Forums

Detecting if a row is locked

Detecting if a row is locked

almost...