Sql-server – How to find what prevented in getting a lock

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – What are the main causes of deadlocks and can they be prevented

tracking deadlocks is the easier of the two:

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605)

Turn it off: DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.

Sql-server – Merge replication fails on executing query

"It looks like something is updating the same row at both servers with different content and made merge agent crash?" This is handled by merge conflict tables, and would not cause the issues you are describing. These conflict tables are located on the publisher database, and are named like: MSMerge_conflict__.

To answer your question about what reinitialization does, by default, reinitialization will take a snapshot of your published articles, drop the articles on the subscriber side, recreate the articles on the subscriber side, and then bulk load data from the snapshot into the subscriber articles. Since this is a production environment, and those articles need to be available on the subscriber side, this should only be used as a last resort.

What you can do is query the MSrepl_errors table on the Distribution database. This will provide you with a command_id and an xact_seqno. You can use these values as inputs into the sys.sp_browsereplcmds stored procedure. This will provide you with command text that is actually failing. Using this information, you can better understand the nature of the failure. If a particular row cannot be inserted or deleted at the subscriber, you may have to either delete the existing row (to allow the insert) or insert a dummy row (to allow the delete), respectively.

I hope this information helps,

Matt

Best Answer

Related Solutions

Sql-server – What are the main causes of deadlocks and can they be prevented

Sql-server – Merge replication fails on executing query

Related Question