SQL Server 2005 lock per key
I have a table with a clustered primary key index on a uniqueidentifier column. I have a procedure that does 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 as long as the same procedure is not performed concurrently from other sources. Once this happens, one of the executions will slow down and complete each time on the primary key.
Any idea what I can do to prevent this, let alone just "don't run it at the same time"? Transactions are currently running at the READ COMMITTED isolation level.
source to share
-
increase the transaction isolation level as prompted by eulerfx.myopenid.com.
-
use sql "mutexes" to just wait for the procedure to complete before using another to run. http://weblogs.sqlteam.com/mladenp/archive/2008/01/08/Application-Locks-or-Mutexes-in-SQL-Server-2005.aspx
-
Use snapshot isolation level. depends on what your application can do it. however, this leads to other problems in the table. http://msdn.microsoft.com/en-us/library/ms189050.aspx
number 2 requires more code change than 1. but sometimes you can't just increase the isolation level.
source to share