Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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
add comment

2 Answers 2

up vote 4 down vote accepted

"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. (see http://sqlindian.com/2012/07/06/troubleshooting-deadlocks/)

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/

share|improve this answer
add comment

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?

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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