6

I'm using SQL Server 2008 R2 that automatically lock database items based on my queries.

Sometimes this deadlocks - two threads with different queries never ends.

Is there any way to debug how SQL Server locks and unlocks resources? I have tried SQL Trace but it has no information about resources locks.

migrated from stackoverflow.com Sep 27 '12 at 8:37

This question came from our site for professional and enthusiast programmers.

  • Maybe you could try using Activity monitor and sp_who2 to find what are the locking processes. – jrara Sep 26 '12 at 11:33
  • WHat do you mean by "locking processes"? I have only one app that is connected to MS SQL server - it is my app. And 2 threads that execute queries - my queries. It deadlock and i don't know why. – Eye of Hell Sep 26 '12 at 11:35
8

"Sometimes this deadlocks - two threads with different queries never ends"

What you are experiencing is blocking, not deadlock. When a deadlock condition occurs, SQL server automatically detects it and choose one of the queries as a victim and throws error 1205.

If you are really experiencing deadlock errors, turn on trace flag 1222. It will log the deadlock errors to the SQL server log.

If you are experiencing blocking, configure the "block process threshold" (http://msdn.microsoft.com/en-us/library/ms181150(v=SQL.90).aspx)

Also see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/

2

You should be able to see them with SQL Profiler.

See this link http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Also, if you suspect a query causes your deadlock, you can try to add the "WITH NO LOCK" clause. But it should not be a permanent solution.

Ultimately, you should take the different queries and see why they cause a lock. Maybe they can be written in a different way? Or optimised? Or maybe the DB structure is in cause?

  • 1
    I experience deadlock only while test application actually running and race conditions occurs. I can't run individual queries in SQL Profiler - they will not trigger deadlock. – Eye of Hell Sep 26 '12 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.