0

I have a C# code that uses EntityFramework to execute a bunch of SQL statements:

using (var context = new MyDbContext())
{
    context.Database.ExecuteSqlCommand(preparedQuery.QueryText);
}

The query is intended for updating a table with constantly chinging data, and to avoid race condition issues it uses Serializable transaction isolation level and UPDLOCK table hint. preparedQuery.QueryText is a string that looks as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN T11;
DECLARE @Flag int;
SET @Flag = (SELECT Count(*)  FROM MyTable  WITH (UPDLOCK) WHERE Field1 = '1' AND Field2 = '2')
IF (@Flag > 0) 
BEGIN
    UPDATE MyTable SET Filed3 = '3'  WHERE Field1 = '1' AND Field2 = '2'
END

From time to time the C# method is throwing a SqlException with the following message:

Transaction (Process ID 202) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

How is it possible for a SQL serializable transaction that has a UPDLOCK table hint at the beginning to be deadlocked? The very first query of the transaction obtains an Update lock on the data. What kind of other transactions can be deadlocked with this query?

Nazz
  • 193
  • 3
  • 9
  • Analyze the actual deadlock and see. Never try to guess at what the deadlock could be -- you probably guess wrong. Deadlocks are possible even if you exclusively lock everything: all it takes is for two queries to attempt to take those exclusive locks in a different order. – Jeroen Mostert May 22 '17 at 08:41
  • Have a look at this: http://stackoverflow.com/questions/7843733/confused-about-updlock-holdlock – OutstandingBill May 22 '17 at 08:46
  • It is not true, by the way, that the `SELECT COUNT(*)` necessarily obtains a lock on *all* the data. If there is an index on `Field1`/`Field2`, the `COUNT(*)` can be satisfied from scanning that index alone, but the `UPDATE` always requires touching the clustered index. You're probably better off rolling this into a single `UPDATE ... WHERE` instead of mucking about with `SELECT COUNT(*)` first. Yes, you can use `UPDLOCK` there too, even though it might seem redundant (it's not -- the `UPDATE` has multiple phases). But really, analyze the deadlock first. – Jeroen Mostert May 22 '17 at 08:55

0 Answers0