Inserts and updates request an Sch-M lock

azure-sql-databasedeadlocklocking

I have a set of Azure applications for an OLTP-type system whose backing store is an Azure SQL database. One of the applications receives messages from an external system, parses out the data, and runs a transaction against the SQL database to insert or update all of the relational records related to that message. Recently I have been having an increasing problem with the database reporting deadlocks. When I look at the deadlock XML, the problem boils down to the transaction requesting an Sch-M lock whenever a row is inserted or updated in one specific table in the sequence. It only ever requests this lock for this table; none of the other tables have this problem. Once this lock is granted, obviously, nothing else can take any kind of lock, which leads to the potential deadlock scenarios that I have been experiencing. My question is, exactly how can inserts and updates request an Sch-M lock on a table? The table itself is like any other table on our system: There is a clustered index on a CreatedAt timestamp column, non-clustered indexes on each foreign key and on the primary key, and statistics. I've tried setting the statistics to not auto-recompute, but it still seems to occur (and it occurs on every insert/update to this table, which doesn't seem to fit with the auto-recompute algorithm). I have an XDL here:

<deadlock><victim-list><victimProcess id="processbe2aaf4108" /></victim-list><process-list><process id="processbe2aaf4108" taskpriority="0" logused="19584" waitresource="OBJECT: 6:178099675:0 " waittime="163" ownerId="821562795" transactionname="user_transaction" lasttranstarted="2017-11-10T22:41:10.870" XDES="0xb82bc70458" lockMode="Sch-M" schedulerid="1" kpid="26992" status="suspended" spid="190" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-10T22:41:11.107" lastbatchcompleted="2017-11-10T22:41:11.107" lastattention="1900-01-01T00:00:00.107" clientapp=".Net SqlClient Data Provider" hostname="RD00155DF927EE" hostpid="19864" loginname="admin_user" isolationlevel="read committed (2)" xactid="821562795" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack><frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="17" stmtstart="786" stmtend="956" sqlhandle="0x030006005385d77fc2ad0d0120a8000000000000000000000000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0xb4a85b245a9bf67f" queryplanhash="0x5602a3b281025581" line="1" stmtstart="280" stmtend="684" sqlhandle="0x03000600bf266e0e5d6dea00fea5000000000000000000000000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0x31a9f630de5f6541" queryplanhash="0xdac38a1906668b48" line="1" stmtstart="76" stmtend="274" sqlhandle="0x02000000df69382a493976af56b05a311cb06c35b49e497e0000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@0 bit,@1 nvarchar(128),@2 binary(8))UPDATE [dbo].[TableName]
SET [Deleted] = @0
WHERE (([Id] = @1) AND ([Version] = @2))
SELECT [Version], [UpdatedAt]
FROM [dbo].[TableName]
WHERE @@ROWCOUNT &gt; 0 AND [Id] = @1   </inputbuf></process><process id="processbe32008ca8" taskpriority="0" logused="22472" waitresource="OBJECT: 6:178099675:2 " waittime="163" ownerId="821562170" transactionname="user_transaction" lasttranstarted="2017-11-10T22:41:10.680" XDES="0xb82bc4c458" lockMode="Sch-M" schedulerid="3" kpid="26848" status="suspended" spid="116" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-10T22:41:11.107" lastbatchcompleted="2017-11-10T22:41:11.103" lastattention="1900-01-01T00:00:00.103" clientapp=".Net SqlClient Data Provider" hostname="RD00155DF95FFF" hostpid="11560" loginname="admin_user" isolationlevel="read committed (2)" xactid="821562170" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack><frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="17" stmtstart="786" stmtend="956" sqlhandle="0x030006005385d77fc2ad0d0120a8000000000000000000000000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0xb4a85b245a9bf67f" queryplanhash="0x5602a3b281025581" line="1" stmtstart="280" stmtend="684" sqlhandle="0x03000600bf266e0e5d6dea00fea5000000000000000000000000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0x41d4d2a2c84c8081" queryplanhash="0xd08cd5876525ba20" line="1" stmtstart="428" stmtend="1122" sqlhandle="0x02000000244ffa0646cf2425389a2897be68d8fb72e8d53f0000000000000000000000000000000000000000">
unknown    </frame><frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(ParameterTypes)INSERT [dbo].[TableName](ColumnList)
VALUES (ParameterValues)
SELECT [Version], [CreatedAt], [UpdatedAt]
FROM [dbo].[TableName]
WHERE @@ROWCOUNT &gt; 0 AND [Id] = @0   </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="178099675" subresource="FULL" dbid="6" objectname="unknown" id="lockb9f21f7c00" mode="Sch-M" associatedObjectId="178099675"><owner-list><owner id="processbe32008ca8" mode="Sch-M" /></owner-list><waiter-list><waiter id="processbe2aaf4108" mode="Sch-M" requestType="wait" /></waiter-list></objectlock><objectlock lockPartition="2" objid="178099675" subresource="FULL" dbid="6" objectname="unknown" id="lockba29ec2380" mode="IX" associatedObjectId="178099675"><owner-list><owner id="processbe2aaf4108" mode="IX" /></owner-list><waiter-list><waiter id="processbe32008ca8" mode="Sch-M" requestType="convert" /></waiter-list></objectlock></resource-list></deadlock>

Best Answer

After following AMtwo's suggestion in the comments and obtaining the text of the SQL in the handle, I discovered the issue. The transaction's queries were not actually responsible for locking the table; there was an initialization procedure that was incorrectly running at the same time that was (re-) creating a trigger on the table at the same time. This trigger creation was locking the table while the transaction was running, which led to the deadlock (the timing of the initialization procedure and the transaction is arbitrary, so sometimes it would succeed and sometimes it would deadlock). Thank you for your help! I've learned a lot about SQL Server analysis from researching all this.

Related Question