How to find what prevented in getting a lock?

Question

Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, which I guess is not a problem by itself.

My question is: How do I retrieve what else had already locked the same row or table that made it unavailable to this failed update?

I am able to see all the exceptions related to Error: 1222 through traces but it is not clear to me what made it fail.

Is there a way to retrieve that or do I only have to continuously watch through Activity Monitor? Since it doesn't deadlock, everything happens pretty quick there and hard to see it live.

I need to find that by using the existing traces and any information that sql server 2008 R2 keeps.

Please help.

Answer 1

I recommend zespri's approach. Use profiler to find the query that is taking an inordinate amount of time.

If you've somehow had no joy with that, you can try the following query which will show a process with a particularly large waittime value.

It filters out any system processes and SQL which has contains WAITFOR DELAY '00:00:05' ... etc

SELECT * 
FROM master.dbo.sysprocesses 
WHERE spid > 49 
AND (blocked <> 0 OR (waittype <> 0x0000 AND lastwaittype <> 'WAITFOR'))

You can then dig a little deeper with the spid against the row that looks like it is causing the problem and inspect the SQL code that is running/locking:

DECLARE @Handle varbinary(50), @Spid int = <your spid value/>
SELECT @Handle = sql_handle from master.dbo.sysprocesses where spid = @spid
DBCC INPUTBUFFER (@spid)
SELECT * FROM master.sys.fn_get_sql(@Handle)

And the resulting output will look like this:

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------------------
Language Event 0          SELECT GETDATE()

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbid   objectid    number encrypted text
------ ----------- ------ --------- ---------------------------------------------------
13     NULL        NULL   0         SELECT GETDATE()

This should help you narrow down the problem query.