SQL Blocking & Deadlock

What is blocking and how would you troubleshoot it?
           
               Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.
 

Troubleshooting blocking:
•    SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server
•    The common blocking scenarios must be identified and resolved. 
•    The scripts output must be checked constantly,
•    The SQL profilers data must be examined regularly to detect blocking.

How to identify blocking in SQL Server 2005 and 2008

There are many ways to find out the details of the Blocking . Please see below query to check blocking in sql database

1)   select * from sys.sysprocessess where blocked<>0
2)   using SP_who

           
            USE Master
            GO
            EXEC sp_who2
            GO

3) using DMVs

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

USE Master
GO
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

and 

USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0
GO

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
A deadlock occurs when two or more processes waits for a resource that is acquired by or is under the control of another process. A live lock is similar to a deadlock except the process states keeps changing. The result of such state is that none of the process will be complete.

Deadlock detection finds and resolves deadlocks. A WFG strategy is followed. WFG is wait for graph. In WFG, processes are represented by nodes while dependencies are represented by edges. Thus, if process A is waiting for a resource held by process B, there is an edge in the WFG from the node for process A to the node for process B. a cycle is this graph is a deadlock. WFG constantly checks for cycles or when a process is blocked and adds a new edge to the WFG.   When a cycle is found, a victim is selected and aborted. 

We can find the deadlock enable the trace flag 1222, 1204