7

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.

grigoryvp
  • 173
  • 1
  • 1
  • 5

2 Answers2

9

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

Community
  • 1
Roji P Thomas
  • 286
  • 1
  • 3
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?

Danielle Paquette-Harvey
  • 2,099
  • 1
  • 17
  • 31