Blocking OR Lock - Dead, Live

DEADLOCK

When two processes are waiting for each other to release locks the other needs. Each process would wait indefinitely for the other to release the lock. SQL Server detects deadlocks and choose one process as a deadlock victim, rolls back the transaction and return error 1205.

How would I face deadlock:

First I will try to find cause of deadlock. I need to know which processes led to the deadlock scenario and what resource and types of locks are involved. In SQL Server 2008 and higher, I will look output of system_health Extended Event session. It's started by default in SQL Server 2008, so if administrator didn't turn it off, we have information about processes, resources and types of locks involved in deadlock scenario. It's excellent way for retroactively finding deadlock information. And in this situation we can immediately start to work on resolving the deadlock problems.

If system_health Extended Event session is stopped I will start it to capture all detected deadlocks in the system. But I need to wait to deadlock happened again to know exactly what is going on. To capture deadlock information in older version I will:

If it's SQL Server 2005 I will turn on trace flag 1222 or will start server side trace to capture Deadlock graph. For SQL Server 2000 trace flag is 1204.

When I find information about processes, statements, resourdes and types of locks that are involved in deadlock I will run queries through DTA (Database tuning advisor). DTA will tell me if some indexes are missing. Usually deadlock can be resolved by adding missing index (adding covered nonclustered index resolved almost all my deadlocks).

If one of the involving statements is SELECT operation, I can add NOLOCK hint or set isolation level to READ UNCOMMITTED (if it’s OK that SELECT operation read uncommited data). If that’s not option, in SQL Server 2005 or higher I will set READ COMMITTED SNAPSHOT or SNAPSHOT isolation level (it will remove shared lock caused by reads). It’s always good that the query is using the minimum necessary transaction isolation level.

Also it’s good to check that transactions involved in deadlock are as short as possible and if they change the same tables, change them in the same order.

BLOCKING

Blocking occurs when one process holds a lock on a specific resource and a second process attempts to acquire a conflicting lock type on the same resource. This is normal behavior and may happen many times with no noticeable effect on system performance.

If blocking increase to the point where it impact on system performance I will first find what cause blocking (need information about queries that are involved in blocking and types of locks). I can use SQL Trace Blocked Process Report or can use DMVs (sys.dmoswaitingtasks, sys.dmtranlocks, sys.dmexecrequest, sysdmexecsql_text) to get that informations.

Solutions of blocking problems are the same as for deadlocking problems:

Identify and add missing indexes, ensure that all transactions are as short as possible, use hints to modify locking behavior, use the lowest isolation level acceptable, rewrite problematic code, ensure that indexes aren’t fragmented and that statistics are up to date.