0

I'm using EF Core and wrote a command interceptor to make some table locks before executing the SQL query that was generated by the EF Core. So if EF Core is generating an UPDATE or DELETE statement, the first few lines will always be the following.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @bUseTransaction BIT
SELECT @bUseTransaction = (CASE WHEN @@TRANCOUNT > 0 THEN 0 ELSE 1 END)
IF (1 = @bUseTransaction) BEGIN TRANSACTION
DECLARE @dummy BIGINT 
SELECT TOP 1 @dummy = Id FROM batchEngine.tblBEReceiveUserDataHeader (TABLOCKX) 
SELECT TOP 1 @dummy = Id FROM batchEngine.tblBEReceiveUserDataParameter (TABLOCKX)

Now I would assume, that both tables are locked until the end of the transaction. But every few days I get an deadlock that contradicts this assumption.

My transaction (process2d70f9d7468) is waiting for an exclusive lock on table batchEngine.tblBEReceiveUserDataHeader and already holds a lock on table batchEngine.tblBEReceiveUserDataParameter.

Why is the table lock of table batchEngine.tblBEReceiveUserDataHeader not hold until the transaction is committed?

The full deadlock information

<deadlock>
 <victim-list>
  <victimProcess id="process2d71a037468" />
 </victim-list>
 <process-list>
  <process id="process2d71a037468" taskpriority="0" logused="0" waitresource="OBJECT: 5:586263131:0 " waittime="4432" ownerId="9806861026" transactionname="user_transaction" lasttranstarted="2021-04-29T12:56:08.893" XDES="0x2d7054e4420" lockMode="X" schedulerid="4" kpid="66856" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-04-29T12:56:08.893" lastbatchcompleted="2021-04-29T12:56:08.903" lastattention="1900-01-01T00:00:00.903" clientapp="Core Microsoft SqlClient Data Provider" hostname="DEDOMESPS001" hostpid="36748" loginname="sa" isolationlevel="repeatable read (3)" xactid="9806861026" currentdb="5" currentdbname="dbIdc" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="dbIdc.batchEngine.usp_BEOfflineOperation" line="1525" stmtstart="130202" stmtend="130364" sqlhandle="0x030005008343252c620cba0018ad000001000000000000000000000000000000000000000000000000000000">
select top 1 @dummy = Id from batchEngine.tblBEReceiveUserDataParameter (TABLOCKX    </frame>
    <frame procname="adhoc" line="1" stmtend="174" sqlhandle="0x010005002c2c6b1d205e85f6dd02000000000000000000000000000000000000000000000000000000000000">
exec batchEngine.usp_BEOfflineOperation @bNoResultSet = 0, @nOperationInstanceId = 
89    </frame>
   </executionStack>
   <inputbuf>
exec batchEngine.usp_BEOfflineOperation @bNoResultSet = 0, @nOperationInstanceId = 
899   </inputbuf>
  </process>
  <process id="process2d70f9d7468" taskpriority="0" logused="20112" waitresource="OBJECT: 5:570263074:0 " waittime="4431" ownerId="9806860987" transactionname="user_transaction" lasttranstarted="2021-04-29T12:56:08.890" XDES="0x2d6d11d0420" lockMode="X" schedulerid="3" kpid="2872" status="suspended" spid="297" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-04-29T12:56:08.897" lastbatchcompleted="2021-04-29T12:56:08.897" lastattention="1900-01-01T00:00:00.897" clientapp="Core Microsoft SqlClient Data Provider" hostname="DEDOMESPS001" hostpid="36748" loginname="sa" isolationlevel="serializable (4)" xactid="9806860987" currentdb="5" currentdbname="dbIdc" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="6" stmtstart="1248" stmtend="1404" sqlhandle="0x020000007cd1c1019afdc43eae85743c7336f501fc0d968b0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p68 bigint,@p69 bigint,@p70 bigint,@p71 bigint,@p72 bigint,@p73 bigint,@p74 bigint,@p75 bigint,@p76 bigint,@p77 bigint,@p78 bigint,@p79 bigint,@p80 bigint,@p81 bigint,@p82 bigint,@p83 bigint,@p84 bigint,@p85 bigint,@p86 bigint,@p87 bigint,@p88 bigint,@p89 bigint,@p90 bigint,@p91 bigint,@p92 bigint,@p93 bigint,@p94 bigint,@p95 bigint,@p96 bigint,@p97 bigint,@p98 bigint,@p99 bigint,@p100 bigint,@p101 bigint)SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @bUseTransaction BIT
SELECT @bUseTransaction = (CASE WHEN @@TRANCOUNT &gt; 0 THEN 0 ELSE 1 END)
IF (1 = @bUseTransaction) BEGIN TRANSACTION
DECLARE @dummy BIGINT 
SELECT TOP 1 @dummy = Id FROM batchEngine.tblBEReceiveUserDataHeader (TABLOCKX) 
SELECT TOP 1 @dummy = Id FROM batchEngine.tblBEReceiveUserDataParameter (TABLOCKX) 
SET NOCOUNT ON;
DELETE FROM [batchEngine].[tblBEReceiveUserDataHeader]
WHERE [Id] = @p68;
SELECT @@ROWCOUNT;

 

DELETE FROM [batchEngine].[tblBEReceiveUserDataHeader]
WHERE [Id] = @p69;
SELECT @@ROWCOUNT;

 

DELETE FROM [batchEng   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="586263131" subresource="FULL" dbid="5" objectname="dbIdc.batchEngine.tblBEReceiveUserDataParameter" id="lock2d6c9045700" mode="IX" associatedObjectId="586263131">
   <owner-list>
    <owner id="process2d70f9d7468" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process2d71a037468" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="570263074" subresource="FULL" dbid="5" objectname="dbIdc.batchEngine.tblBEReceiveUserDataHeader" id="lock2d6aaddce00" mode="X" associatedObjectId="570263074">
   <owner-list>
    <owner id="process2d71a037468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2d70f9d7468" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>
Tim Meier
  • 1
  • 1
  • Add the `batchEngine.usp_BEOfflineOperation` proc code to your question. – Dan Guzman Jun 25 '21 at 11:34
  • The procedure is pretty huge and I don't think it can affect the missing lock in the EF Core transaction. – Tim Meier Jun 25 '21 at 12:31
  • Does this answer your question? [SQL Server, the misleading XLOCK & optimizations](https://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations) You *need* `UPDLOCK`, and `TABLOCKX` is just a red herring for `select` statements, don't use it. You should only lock what you need, and in the same order every time, so you would have all your parameters sorted in the correct order (or pop them into an indexed temp table) – Charlieface Jun 25 '21 at 13:23
  • Putting it another way, if there are no modifications then an X lock cannot be taken, and the only way to hold a shared lock is with `UPDLOCK` – Charlieface Jun 25 '21 at 14:42
  • Ok, the XLOCK hint can hold a table lock until the end of the transaction. But shouldn't the transaction level SERIALIZABLE do the same thing? [MS Transaction Level](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15) says that transaction level SERIALIZABLE means, that every SELECT statement is executed with HOLDLOCK – Tim Meier Jun 25 '21 at 16:43
  • That statement is correct, but `select` statements never take X locks, only S locks, so adding an `XLOCK` or `TABLOCKX` is a misnomer and doesn't do what it claims to do. `XLOCK` hint by itself certainly doesn't make a `select` hold a lock longer than the end of the statement, you need `UPDLOCK` for that. I would suggest removing all of this, just run the EF statements in a transaction and make sure that all statements run in the correct order (the order of the clustered index) – Charlieface Jun 26 '21 at 23:04
  • An `SELECT` can hold X locks. I checked it with Management Studio, SqlConnection and EF Core. Executing 2 `SELECT` statements with `TABLOCKX` with a sleep of 10 seconds between them and after them. I can see the X lock with `sp_lock` in all 3 cases. And it always works as expected. The locks are aquired in the correct order and hold until the transaction is commited. – Tim Meier Jun 28 '21 at 07:24

0 Answers0