StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy can't I insert/update data without locking the entire table in SQL Server 2005?
text
Body
copied!<p>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.</p> <p>I have found many solutions online but all of them are causing transaction deadlocks. This is the general pattern I have been using:</p> <pre><code>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 </code></pre> <p>I have tried:</p> <ul> <li><code>WITH XLOCK</code> instead of <code>UPDLOCK</code></li> <li><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE</code> at the beginning with <code>UPDLOCK</code></li> <li><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE</code> and no table hints</li> </ul> <p>I have also tried the following pattern with all the combinations above:</p> <pre><code>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 </code></pre> <p>The only way I can get it to work without deadlocks is to use <code>WITH (TABLOCKX)</code>.</p> <p>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.</p> <p>The server logs look like this:</p> <pre><code>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 </code></pre> <p>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.</p> <p>What am I doing wrong?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload