question

ChrisSijtsma avatar image
0 Votes"
ChrisSijtsma asked ErlandSommarskog commented

Deadlock: Why is one of the locks necessary?

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-generalsql-server-transact-sql
ledger.png (9.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you post the deadlock trace?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you give this as an answer, Erland? In that case I can mark it as the answer?

0 Votes 0 ·

I've now converted my comment to be an Answer.

0 Votes 0 ·
ChrisSijtsma avatar image
0 Votes"
ChrisSijtsma answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChrisSijtsma avatar image
0 Votes"
ChrisSijtsma answered ErlandSommarskog converted comment to answer

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.