|
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. |
|||||
|
|
"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/ |
|||
|
|
|
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? |
|||
|
