0

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>
  • I notice statements involved in this deadlock start on line 17, and your @@TRANCOUNT is 2. You should include the full sql text of the batch, and query plan as well, which may help understand. You can use the sql_handle value from the deadlock to get the text like this: SELECT text FROM sys.dm_exec_sql_text(0x030006005385d77fc2ad0d0120a8000000000000000000000000000000000000000000000000000000000000) – AMtwo Nov 12 '17 at 12:22
  • Only INSERT or UPDATE shouldn't be causing this. Do you run a TRUNCATE? What are the commands before the INSERT statement that leads to the SCH-M lock? Any kind of DDL scripts? Something has to be leading to a schema lock. – Grant Fritchey Nov 12 '17 at 15:12
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.