CODEWITHSUNDEEP
sql-serverdatabase-deadlocks
Robin Palm
Robin Palm

Reputation: 121

Self-deadlock in SQL Server

During an installation process running in a transaction I suddenly get the following error:

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

This is strange since there are no other sessions accessing the db. I have also turned off parallellism on the server. This is happening on my dev-machine running Sql Server Developer 2017:

The deadlock graph looks like:

<deadlock-list>
 <deadlock victim="process23c22077088">
  <process-list>
   <process id="process23c22077088" taskpriority="0" logused="0" waitresource="METADATA: database_id = 40 SCHEMA(schema_id = 16), lockPartitionId = 0" waittime="3489" ownerId="19528332" transactionname="GetInitializedIMA" lasttranstarted="2019-09-30T18:40:58.847" XDES="0x23b6850fac0" lockMode="Sch-S" schedulerid="12" kpid="17636" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-09-30T18:40:58.783" lastbatchcompleted="2019-09-30T18:40:58.783" lastattention="2019-09-30T18:36:47.943" clientapp=".Net SqlClient Data Provider" hostname="RND68" hostpid="29660" loginname="S-1-9-3-2459696885-1204846140-3013888703-3157444644" isolationlevel="read committed (2)" xactid="19528330" currentdb="40" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="unknown" line="129" stmtstart="-1" sqlhandle="0x03002800205b3c6bdde23301d8aa000001000000000000000000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="1" stmtend="104" sqlhandle="0x01002800f50e5536807d84d63b02000000000000000000000000000000000000000000000000000000000000">
EXEC SoftadminMapQBIntegration.QueryModel_SyncWithMa     </frame>
    </executionStack>
    <inputbuf>
EXEC SoftadminMapQBIntegration.QueryModel_SyncWithMap
EXEC SoftadminMapQB.CacheDatabaseNames

    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <metadatalock subresource="SCHEMA" classid="schema_id = 16" dbid="40" lockPartition="0" id="lock23bb4ee9b00" mode="Sch-M">
    <owner-list>
     <owner id="process23c22077088" mode="Sch-M"/>
     <owner id="process23c22077088" mode="Sch-S" requestType="wait"/>
    </owner-list>
    <waiter-list>
     <waiter id="process23c22077088" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </metadatalock>
  </resource-list>
 </deadlock>
</deadlock-list>

The error always occurs at the same place. The client is a .NET application running a series of sql batches using SqlClient.

I have tracked down the error to a view that is created during the transaction. The error occurs when a reference to this view appears in a later batch. I can modify the view to make the deadlock go away by removing a join in its definition (regular join on FK equals PK).

Has anyone seen anything like this before or know what is going on?

Full version info:

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763:) (Hypervisor)

Upvotes: 3

Views: 1442

Answers (2)

Abbas
Abbas

Reputation: 3258

I was facing the exact same problem. I had a Table-Value parameter with some varchar columns, e.g.

create type tt_my_type as table
(
  guid                       varchar(250) not null,
  name                       varchar(250)
  primary key (guid)
)

However, in my app, I was passing values to name that were longer than 250 characters, which was causing the deadlock. The process deadlock disappeared when I increased the varchar length after re-assessing the data.

Upvotes: 2

DAGUE Benjamin
DAGUE Benjamin

Reputation: 41

I'm facing a very similar issue on my dev machine, I've a script that was working well few days ago and now I get the deadlock each time... As you, I'm in a transaction (.NET App executing script using SQL Client) and no other connection is made. The only change in my setup was a migration of my instance from 2017 to 2019 few hours ago. I managed to solve the issue by upgrading the compatibility mode of the target DB from 2017 (140) to 2019 (150) and no more deadlock !

Hope this help.

Upvotes: 2

Related Questions