Share via

Deadlock: Why is one of the locks necessary?

Chris Sijtsma 106 Reputation points
2021-05-06T10:09:42.76+00:00

In one of our applications, a deadlock was caused by the following two ad-hoc queries, issued by the entity-framework.

Query 1: 

DELETE [dbo].[T_Fin_RequestGeneralLedger]  
WHERE ([RequestId] = @0)

Query 2: 

INSERT [dbo].[T_Fin_RequestGeneralLedgerEntityLink]  
([RequestId], [FinancialCompanyCd], [FiscalYear], [LedgerCode], LastUpdatedOn], [LastUpdatedBy])  
VALUES (@0, @1, @2, @3, @4, @5)

The relations between the relevant tables are as follows.
94399-ledger.png

The FK from T_Fin_RequestGeneralLedgerEntityLink to T_Fin_RequestGeneralLedger has "ON DELETE CASCADE" specified.
There are no triggers.

The deadlock is on the primary key indexes PK_T_Fin_RequestGeneralLedgerEntityLink of T_Fin_RequestGeneralLedgerEntityLink and PK_T_FinCompFYLedger on T_FinCompFYLedger.

I do understand that the insert needs locks on both indexes.
I do understand that the delete on T_Fin_RequestGeneralLedger needs locks on PK_T_Fin_RequestGeneralLedgerEntityLink.
I fail to see why the delete needs a lock on PK_T_FinCompFYLedger, however. Can anyone explain why this lock is needed?

Kind regards,
Chris Sijtsma

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2021-05-07T21:07:07.103+00:00

    I suspected so, but I wanted to see the deadlock trace to know for sure. And also how I could point you to how to see it.

    If you go back to the deadlock trace, you will find that the process performing the DELETE, has transaction=USER TRANSACTION. Furthermore, you will also see that lasttransstarted is before lastbatchstarted, which makes it clear that it is a transaction started from the client. (Or an orphaned transaction that was never committed or rolled back although it should have been.)

    The deadlock trace can only show the current statements, but when there is a multi-statement transactions, some of the locks involved in the deadlock might have been taken in earlier statements.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chris Sijtsma 106 Reputation points
    2021-05-06T10:13:38.75+00:00

    By the way, the foreign key from T_Fin_RequestGeneralLedgerEntityLink to T_FinCompFYLedger is not a cascading FK.

    0 comments
  2. Chris Sijtsma 106 Reputation points
    2021-05-07T07:25:51.777+00:00

    Hi Erland,

    It is not necessary, anymore. It was an entity framework application making contact with our database. In this application, a client side transaction was started. But all sql statements were fired one by one. When I did a full profiler trace of the two processes causing the deadlock, just now, I captured the other statement causing the lock on T_FinCompFYLedger. But because this was another single ad hoc statement, the deadlock trace didn't capture it.

    Thanks for your trouble anyway.

    0 comments