Hi Adam,

Initially get the dead lock information like what object causes the deadlock and what type of lock it has.

Have a look at below article.

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

Hi Adam,

sp_getapplock effectively serializes modifications, because only one connection can hold an exclusive application lock on the same resource. Other modifications to the same data will be forced to wait for that lock to be released, so there will be no collisions whatsoever.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
BEGIN TRANSACTION ; 
DECLARE @ret INT ;
SET @ret = NULL ;
-- The @LockTimeout setting makes sp_getapplock
-- wait for 10 seconds for other connections 
-- to release the lock on col1 row value 100
EXEC @ret = sp_getapplock @Resource = 'col1=100' ,
    @LockMode = 'Exclusive', @LockTimeout = 10000 ;
-- sp_getapplock return code values are: 
-- >= 0 (success), or < 0 (failure)
IF @ret < 0 
    BEGIN ;
        RAISERROR('Failed to acquire lock', 16, 1) ;
        ROLLBACK ;
    END ;
ELSE 
    BEGIN ;
        DELETE FROM table WHERE col1=100 AND col2 NOT IN (-1) AND col3 =200 ;
        IF @@ROWCOUNT = 0 
            BEGIN ;
                RAISERROR('Record not found', 16, 1) ;
            END ;     
        COMMIT ;
    END ;

Hi Adam

Here you get an idea about your problem

http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

To Avoid problem use

sp_getapplock