Deadlock occurring during query with UPDLOCK hint

2018-05-30 22:52:56

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?

链接地址: http://www.djcxy.com/p/5626.html

上一篇: Browserify插入全局变量

下一篇: UPDLOCK提示查询期间发生死锁