Causing a Deadlock in SQL Server

SQL Server has a background process that is continually looking for deadlock chains.  If it finds a deadlock chain, it will rollback the transaction that has done the least amount of work, which should be the fastest to rollback.  That's unless one of the transactions has volunteered as a deadlock victim  by setting their deadlock_priority to low.
If you want to cause a deadlock, it is quite easy.  Most DBAs will be able to do it, and it can be useful in determining what profiler and logging information is available in the event of an unplanned deadlock.

Here are some instructions to cause a deadlock between 3 transactions.  Most deadlocks are between 2 transactions, but a deadlock chain can have any number of transactions.  By the way, there are many ways to set up a deadlock, the only common characteristic is that there are a chain of processes that are waiting on each other for locks.  So in a 2 process chain, Process A is waiting on a resource held by Process B and Process B is waiting on a resource held by Process A.  As you can imagine, the only solution is for one of the transactions to be rolled back.  They can't both go forward.

In my experience, a common class of deadlocks is the one caused by update (update, insert or delete) transactions that update so many rows that the lock must be escalated.  For example, escalate several page locks to a table lock.  When there are two transactions like this starting about the same time, they both start updating, and taking locks, until one of them escalates to a table lock.  At this moment that transaction waits on the other.  But if the other is also destined to escalate to a table lock on the same table, there will be a deadlock.

Now, to cause a deadlock between 3 tasks follow these instructions.
You might like to start SQL Profiler and enable a trace for Lock:Deadlock graph, Lock:Deadlock Chain and Lock:Deadlock, which will give you considerable information on the deadlock chain participants and resource locks.
Create 3 tables

• create table t1 (c1 int, c2 varchar(50))
• create table t2 (c1 int, c2 varchar(50))
• create table t3 (c1 int, c2 varchar(50))

Create 3 query windows in SQL Server Enterprise Manager.
In the first query window execute
begin tran
insert into t1 select 1, 'abc'

In the second query window execute
begin tran
insert into t2 select 2, 'xyz'
Select * from t1

The second query window will be waiting on the first query.
In the third query window execute
begin tran
insert into t3 select 3, 'mno'
Select * from t2

The third query window will be waiting on the second query.  At this point in time there is no deadlock.  We just have a locking chain, with query 1 at the head.  You can see the locking chain if you execute.
sp_who2

In the first query window execute
--begin tran
--insert into t1 select 1, 'abc'
Select * from t3
Now, query 1 will be waiting on query 3, which is waiting on query 2, which is waiting on query 1.  We have a deadlock.  You should notice that within a couple of seconds, one of the queries is cancelled and the transaction rolled back.

Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, have a look at the Profiler output, and you should see your deadlock chart with the three processes and the locked resources.  The transaction that was selected as the victim, and rolled back, has a cross through it.  If you hover over the transactions, you will see the last batch to be submitted.  Note, the last batch is not necessarily the entire transaction.  There may have been an explicit BEGIN TRAN and many statements within the transaction that aren't included in the chart.  If you manage to capture a blocking transaction in action, you can see the locks it has (and ones its waiting on) with the sp_lock command. 

Remember to COMMIT or ROLLBACK the two queries that weren't rolled back.  There is nothing worse that a query holding locks while the user has gone off to do something else.