CODEWITHSUNDEEP
sql-serversql-server-2005transactionsisolation-level
Ryan
Ryan

Reputation: 23

Fixing upsert issue with TRANSACTION ISOLATION LEVEL REPEATABLE READ?

I have a SQL statement that does an update, and then if the @@ROWCOUNT is 0, it will insert. this is basically a MERGE in SQL 2008. We are running into situations where two threads are failing on the update simultaneously. It will attempt to insert the same key twice in a table. We are using the Default Transaction isolation level, Read Committed. Will changing the level to repeatable reads fix this or do I have to go all the way to Serializable to make this work? Here is some code:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;                                            
UPDATE TableA
SET Duration = @duration              
WHERE keyA = @ID
AND keyB = @IDB;

IF @@rowcount = 0
BEGIN

INSERT INTO TableA (keyA,keyB,Duration) 
VALUES (@ID,@IDB,@duration); 

END
COMMIT TRAN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;";

Upvotes: 2

Views: 1587

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

You would need to go all the way up to SERIALIZABLE.

Under REPEATABLE READ if the row does not exist then both UPDATE statements can run concurrently without blocking each other and proceed to do the insert. Under SERIALIZABLE the range where the row would have been is blocked.

But you should also consider leaving the isolation level at default read committed and putting a unique constraint on keyA,keyB so any attempts to insert a dupe fail with an error.

Upvotes: 4

Related Questions