Why can't I insert/update data without locking the entire table in SQL Server 2005?

Question

I am trying to insert/update rows in a SQL Server table (depending on whether it exists or not). I am executing the SQL from multiple threads on multiple machines and I want to avoid getting duplicate key errors.

I have found many solutions online but all of them are causing transaction deadlocks. This is the general pattern I have been using:

BEGIN TRANSACTION

UPDATE TestTable WITH (UPDLOCK, SERIALIZABLE)
SET Data = @Data 
WHERE Key = @Key

IF(@@ROWCOUNT = 0)
BEGIN
     INSERT INTO TestTable (Key, Data)
     VALUES (@Key, @Data)
END

COMMIT TRANSACTION

I have tried:

• WITH XLOCK instead of UPDLOCK
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE at the beginning with UPDLOCK
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE and no table hints

I have also tried the following pattern with all the combinations above:

BEGIN TRANSACTION

IF EXISTS (SELECT 1 FROM TestTable WITH (UPDLOCK, SERIALIZABLE) WHERE Key=@Key) 
BEGIN
    UPDATE TestTable
    SET Data = @Data 
    WHERE Key = @Key
END
ELSE
BEGIN
    INSERT INTO TestTable (Key, Data)
    VALUES (@Key, @Data)
END

COMMIT TRANSACTION

The only way I can get it to work without deadlocks is to use WITH (TABLOCKX).

I am using SQL Server 2005, the SQL is generated at runtime and so it is not in a stored procedure and some of the tables use composite keys rather than primary keys but I can reproduce it on a table with an integer primary key.

The server logs look like this:

waiter id=processe35978 mode=RangeS-U requestType=wait
waiter-list
owner id=process2ae346b8 mode=RangeS-U
owner-list
keylock hobtid=72057594039566336 dbid=28 objectname=TestDb.dbo.TestTable indexname=PK_TestTable id=lock4f4fb980 mode=RangeS-U associatedObjectId=72057594039566336
waiter id=process2ae346b8 mode=RangeS-U requestType=wait
waiter-list
owner id=processe35978 mode=RangeS-U
owner-list
keylock hobtid=72057594039566336 dbid=28 objectname=TestDb.dbo.TestTable indexname=PK_TestTable id=lock2e8cbc00 mode=RangeS-U associatedObjectId=72057594039566336

The mode is obviously different depending on the table hint used (but the processes are always waiting for the mode they already own). I have seen RangeS-U, RangeX-X and U.

What am I doing wrong?

Answer 1

I looked at this again today and found that I had been a bit of a numpty. I was actually running:

BEGIN TRANSACTION

IF EXISTS (SELECT 1 FROM TestTable WITH (UPDLOCK, SERIALIZABLE) WHERE Key=@Key) 
BEGIN
    UPDATE TestTable
    SET Data = @Data, Key = @Key -- This is the problem
    WHERE Key = @Key
END
ELSE
BEGIN
    INSERT INTO TestTable (Key, Data)
    VALUES (@Key, @Data)
END

COMMIT TRANSACTION

I was locking the key myself. Duh!