SQL Server 2005 deadlock on key

Question

I have a table with a clustered primary key index on a uniqueidentifier column. I have a procedure that runs the following psuedo functions:

begin transaction
read from table 1
insert into table 2
update table 1 with pointer to table 2 record
commit transaction

This all works fine until the same procedure is executed concurrently from elsewhere. Once this happens, one of the executions gets deadlocked and terminated every single time on the primary key.

Any idea what I can do to prevent this, short of simply saying "don't run it concurrently"? The transactions are currently running in READ COMMITTED isolation level.

Answer 1

Look here:

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx

and here:

http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx

Answer 2

1. increase the transaction isolation level as eulerfx.myopenid.com is hinting.

2. use sql "mutexes" to simply wait for a procedure to finish before alowing another to run. http://weblogs.sqlteam.com/mladenp/archive/2008/01/08/Application-Locks-or-Mutexes-in-SQL-Server-2005.aspx

3. use snapshot isolation level. dependin on what your app does this can work. however this brings other problems to the table. http://msdn.microsoft.com/en-us/library/ms189050.aspx

number 2 requires more code change than 1 though. but sometimes you can't just increase the isolation level.