Deadlocks are no fun. Creating deadlocks for demonstration purposes is easy. To see how they work, you're going to need to connections to your server and the following code:

CREATE TABLE DeadlockTest (id int IDENTITY(1,1), SomeData CHAR(36))
go
CREATE TABLE DeadlockTest2 (id int IDENTITY(1,1), SomeData CHAR(36))
go
INSERT INTO DeadlockTest VALUES(newid())
INSERT INTO DeadlockTest2 VALUES(newid())

BEGIN TRANSACTION
UPDATE DeadlockTest SET SomeData=newid() WHERE id = 1

-- Run the following after starting the code to run in Connection 2
-- UPDATE DeadlockTest2 SET SomeData=newid() WHERE id = 1

/*-- Run in other connection
BEGIN TRANSACTION
UPDATE DeadlockTest2 SET SomeData=newid() WHERE id = 1
UPDATE DeadlockTest SET SomeData=newid() WHERE id = 1

-- ROLLBACK -- If this transaction didn't deadlock
*/

/*
ROLLBACK
DROP TABLE DeadlockTest
DROP TABLE DeadlockTest2
*/

Paste all of this code into the first connection and execute it. This will leave you with a pending transaction. Now paste the code indicated in the comment block marked Run in Connection 2, which will also create a pending transaction. Finally, execute the single commented out line to update DeadlockTest2 in the first connection. This sets up the deadlock and one or the other of the connections will fail with the message:

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

Now, there are a lot of ways you can fix deadlocks, but I recently encountered a case that wasn't so easy because it required triaging rather than repairing. You see, we were going to start re-architecting the application to move from the 2^nd generation framework to our 4^th generation framework so it wasn't worth the time to invest in tracking down the fault issues since they'd be going away under the new framework. But this was causing some data inconsistencies and UI problems that needed to be taken care of. Fortunately, this database was running on SQL 2005 so it was TRY-CATCH to the rescue.

We actually had two types of processes failing because of deadlocks. The first was on reads. Several of those I could fix by adding SET TRANSACTION LEVEL READ UNCOMMITTED to them, but some required reading committed data, so that was where we added error handling and logging. The logging would tell us how successful our TRY-CATCH is by storing information in a table:

CREATE TABLE [dbo].[DeadlockLogging](
    [DeadlockLogID] [int] IDENTITY(1,1) NOT NULL,
    [DeadlockID] [char](36) NOT NULL,
    [Description] [nvarchar](256) NOT NULL,
    [Resolution] [char](1) NOT NULL,
    [DateLogged] [datetime] NOT NULL,
CONSTRAINT [PK_DeadlockLogging] PRIMARY KEY CLUSTERED
(
    [DeadlockLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'R=Retried, S=Succeeded on Retry, F=Failed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DeadlockLogging', @level2type=N'COLUMN',@level2name=N'Resolution'
GO

ALTER TABLE [dbo].[DeadlockLogging] ADD  CONSTRAINT [DF_DeadlockLogging_DateLogged]  DEFAULT (getdate()) FOR [DateLogged]
GO

We'll update this table in our TRY-CATCH block. Now we'll begin modifying stored procedures.  The first step was to set the deadlock level to low, this would generally cause a read to fail in a deadlock situation before a write would.

SET DEADLOCK_PRIORITY LOW

Next, we set up some variables and our TRY-CATCH block:

-- Added try catch logic to try to handle deadlocks
DECLARE @Err INTEGER
DECLARE @dlCounter int
SET @dlCounter = 0
DECLARE @DeadlockID char(36)
SET @DeadLockID = newid()
DECLARE @DeadlockDescription nvarchar(256)
SET @DeadlockDescription = 'stored procedure name ' + @SignificantParameter
RETRY:
BEGIN TRY
-- INSERT REAL CODE HERE!
if @dlCounter>1
    insert into dbo.DeadlockLogging values(@DeadlockID, @DeadlockDescription, 'S', getdate())
END TRY
BEGIN CATCH
  SET @Err = @@ERROR
  IF @Err = 1205
  begin
    set @dlCounter = @dlCounter + 1
    if @dlCounter < 3
    begin
      insert into dbo.DeadlockLogging values(@DeadlockID, @DeadlockDescription, 'R', getdate())
      WAITFOR DELAY '00:00:01'
      GOTO RETRY
    end
    ELSE
     insert into dbo.DeadlockLogging values(@DeadlockID, @DeadlockDescription, 'F', getdate())
   end
END CATCH

If we successfully make it through the TRY portion, we check to see if @dlCounter has been incremented. If it has then we know we encountered a deadlock and need to record that we did finally make it through the process successfully. In our CATCH portion we check to see what the error was. If it was 1205 (Deadlock) then we increment @dlCounter. If it's less than 3 then we insert a record in the log file with a Resolution of R (Retry) and attempt to read the data again. If we have already reached our maximum tries, then we log that we failed in our retry and go on.

The only difference between the procedures that read and those that write is that the writing sprocs didn't contain the DEADLOCK_PRIORITY and they retried more times.

With this implementation, the number of writes that failed was reduced to 0, providing time for the successful rearchitecture and move.