Self-deadlock in SQL Server

Question

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)

This is technically a bug, but practically you're doing very hairy things if you create a view in a transaction, then reference it later from the same transaction but a different batch. It's not completely surprising this gets the engine to act squirrely. Is the view defined with or without `SCHEMABINDING`? Does it spell out all columns or does it use `*` somewhere? Either of these things might also affect the results. If possible, separate DDL statements from the rest -- only some are transactional to begin with, and even then the engine doesn't handle it very well. — Jeroen Mostert, Oct 01 '19 at 13:34
"Has anyone seen anything like this before"? Yes I've seen something similar where a system transaction runs in the same session and blocks itself from getting a lock. https://stackoverflow.com/questions/20155337/why-is-this-query-slow-the-first-time-after-i-start-the-service — Martin Smith, Oct 01 '19 at 13:59
@JeroenMostert We don't use SCHEMABINDING and all columns are spelled out explicitly. This is an upgrade of an existing database. It needs to perform DDL and DML to convert data to the new schema. It needs to be an atomic operation thereby the transaction. I guess I don't have to use the view to perform the data conversion but it is definitely more convenient and I don't understand why it would cause a problem. — Robin Palm, Oct 02 '19 at 08:33
Because bugs. It *shouldn't* cause a problem (the same session is holding the locks, so the engine should see lock compatibility isn't a problem) but it does. The scenario where you create objects in a transaction and then use them is probably not particularly well tested, as it is not the common case (beyond temp tables, but, per Martin, even that apparently has its issues). — Jeroen Mostert, Oct 02 '19 at 10:04
@JeroenMostert Thank you! I guess I will have to work around this and maybe file a bug report if I manage to create a self-contained case for it. — Robin Palm, Oct 02 '19 at 10:48

score 2 · Answer 1 · answered Mar 09 '20 at 20:09

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.

score 1 · Answer 2 · answered Feb 17 '21 at 23:15

1

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.

answered Feb 17 '21 at 23:15

Abbas

3,228
1
23
27

I've had that too, an issue all within one SP. Turned out there was a "String or binary data would be truncated" issue with the size of table variable column, and once that was made large enough, the self-deadlock problem vanished. – AjV Jsy Nov 29 '22 at 12:38

Self-deadlock in SQL Server

2 Answers2