SQL Server Dead Locks - How to Resolve?

I will be updating this post on the details which I know about SQL Server Dead locks...

What is a deadlock?
A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource.

How SQL Server will handle DeadlockThe lock manager’s thread checks for deadlocks. When a lock manager’s deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim (This will be the SPID which consumes least resources). The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID. Killing the SPID frees the resources and allows the other SPID to continue. Killing the SPID that is the deadlock victim is what causes the broken connection that the Visual Basic front-end application experiences.

In a well designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction.

Although deadlocks can be minimized, they cannot be completely avoided. That is why the front-end application should be designed to handle deadlocks.

How to identify a deadlock
Enable below trace commands which will help capturing details about deadlock

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

Explanation:
The "-1" indicates all SPIDs.
Also instead of 1204 you can also use 1205. Below are the difference between them

The 1204 parameter collects information about the process and the resources when the deadlock detection algorithm encounters a deadlock. The 3605 parameter writes this information to the SQL Server error logs.

The 1205 startup parameter collects information every time that the deadlock algorithm checks for a deadlock, not when a deadlock is encountered.

Now run the SQL Profiler by selecting lock event for "deadlock" and for "deadlock chain." "Deadlock" corresponds to the -T1204 flag, and "deadlock chain" corresponds to the -T1205 flag which will give the complete SQL Statements creating DeadLock.

How to set priority for SPID so that SQL Server will KILL it accordingly
SET DEADLOCK_PRIORITY { LOW NORMAL @deadlock_var }

Explanation:
Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection

What will be the Error Code?
1205 will be error message!

Some Links
1. http://www.sql-server-performance.com/tips/deadlocks_p1.aspx (Brad McGehee)
2. http://support.microsoft.com/kb/832524 (Microsoft KB)
3. sqlservercentral.com/tracingdeadlocks/1324/ (Forum Artcile)