How to resolve SQL Server deadlocks involving concurrent INSERTs?

Question

I have a transaction that inserts a row into 2 tables that is getting hit from multiple threads concurrently and causing occasional deadlocks. We have reproduced in both SQL Server 2014 and 2016.

TableA has an IDENTITY (auto-incrementing int) column as its PK.

TableB has a composite PK made up of TableA.ID and another table's ID. (Effectively it models a many-to-many relationship.)

The transaction (implemented in C#/ADO.NET) looks approximately like this:

BEGIN TRAN
INSERT INTO TableA (...) OUTPUT INSERTED.ID VALUES (...)
INSERT INTO TableB (TableA_ID, ...) VALUES (...)
COMMIT TRAN

Here is a sample XML deadlock report we captured:

<deadlock>
 <victim-list>
  <victimProcess id="process22e2908c8" />
 </victim-list>
 <process-list>
  <process id="process22e2908c8" taskpriority="0" logused="1448" waitresource="KEY: 5:72057594781630464 (274b30e6b09d)" waittime="14" ownerId="31777821" transactionname="user_transaction" lasttranstarted="2016-09-29T11:55:16.830" XDES="0x238c71270" lockMode="S" schedulerid="6" kpid="6156" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-09-29T11:55:16.833" lastbatchcompleted="2016-09-29T11:55:16.830" lastattention="1900-01-01T00:00:00.830" clientapp=".Net SqlClient Data Provider" hostname="..." hostpid="2376" loginname="..." isolationlevel="read committed (2)" xactid="31777821" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="148" stmtend="408" sqlhandle="0x0200000076f9e4365c825254c9131048395bcaa4079e0fac0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
INSERT INTO TableB (...) VALUES (...)
   </inputbuf>
  </process>
  <process id="process2232c2ca8" taskpriority="0" logused="1448" waitresource="KEY: 5:72057594781630464 (afb9ab6e6f5f)" waittime="15" ownerId="31777823" transactionname="user_transaction" lasttranstarted="2016-09-29T11:55:16.830" XDES="0x238c70890" lockMode="S" schedulerid="6" kpid="11480" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-09-29T11:55:16.833" lastbatchcompleted="2016-09-29T11:55:16.830" lastattention="1900-01-01T00:00:00.830" clientapp=".Net SqlClient Data Provider" hostname="..." hostpid="2376" loginname="..." isolationlevel="read committed (2)" xactid="31777823" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="148" stmtend="408" sqlhandle="0x0200000076f9e4365c825254c9131048395bcaa4079e0fac0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
INSERT INTO TableB (...) VALUES (...)
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594781630464" dbid="5" objectname="MyDatabase.dbo.TableA" indexname="IndexOnTableA" id="lock2267be800" mode="X" associatedObjectId="72057594781630464">
   <owner-list>
    <owner id="process2232c2ca8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process22e2908c8" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594781630464" dbid="5" objectname="MyDatabase.dbo.TableA" indexname="IndexOnTableA" id="lock2267f7080" mode="X" associatedObjectId="72057594781630464">
   <owner-list>
    <owner id="process22e2908c8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2232c2ca8" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

Things tried:

• READ COMMITTED and READ UNCOMMITTED isolation levels
• WITH (ROWLOCK) hint on the INSERT
• Snapshot Isolation and Read Committed Snapshot enabled
• Lots of index tweaks (open to suggestions though - is it likely we have too many or too few?)

I don't want to give up the transactional integrity of the 2 inserts, but it must be able to be hit from multiple threads without deadlocking. Any help is greatly appreciated.

---

UPDATE: Found the culprit, see my answer below. Thanks all for the help.

Answer 1

You could do a repeat/recycle for the failed transaction in C#. Log the event to table, etc... Something like the following (pardon any pseudo-ish elements). Consider using the MessageBox for testing purposes:

SqlConnection connection = new SqlConnection(TargetsqlConnectionString);
Server server = new Server(new ServerConnection(connection));
string = "BEGIN TRAN
    INSERT INTO TableA (...) OUTPUT INSERTED.ID VALUES (...)
    INSERT INTO TableB (TableA_ID, ...) VALUES (...)
    COMMIT TRAN";
string SQLMsg="";
bool deadlocked= true;
bool errorconditon=false;
while(deadlocked){
try
{
     server.ConnectionContext.ExecuteNonQuery(script);

 }   catch(Exception ex)
    {
     MessageBox.Show(ex.Message);
     SQLMsg = ex.Message + "\r\n";
     //determine if it's a deadlock and then set the errorcondition value
     errorcondition=true;
        }
 if (!errorcondition) deadlocked=false //exit the loop
}

Answer 2

You will need to test this in a dev environment, but I would suspect that if you got rid of the foreign key to TableA, that you would no longer see the deadlock issues. That being said, I would leave the foreign key there so that you don't get data integrity issues.

What I think is going on is that you have 2 inserts happening at nearly the same time, but when it does the insert into TableB the database engine has to do a query against TableA to make sure the foreign key value is valid. So between the exclusive locks and shared locks on TableA you are getting the deadlock.

One solution that may work is to choose something other than an incrementing key on TableA so that the inserts can happen throughout the table instead of just the last page, which could reduce the contention but could also lead to more fragmentation, this could also have other impacts on your query patterns, so you would have to decide if the trade off is worth it.

And in the end it may only reduce the deadlocks. No matter what we do there will always be some deadlocks so the code really needs to have retry logic so you don't lose the data.

Answer 3

I am at an unfair advantage because I have more info than I provided in the question, but the problem turned out to be the recent introduction of an indexed view. To no surprise, it contained joins involving all tables that showed up in the deadlock reports. It was added proactively, but turned out to be unnecessary, and un-indexing it completely eliminated all the nasty deadlocks we were seeing.

This is a well known issue, as it turns out:

Resolving deadlock from 2 tables only related through indexed view

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/02/be-ready-to-drop-your-indexed-view.aspx

https://blogs.msdn.microsoft.com/vijaybk/2009/05/01/deadlock-because-of-indexed-views/

The lesson: beware, indexed views can be evil. There is much locking and blocking going on behind the scenes that will not reveal itself in tools such as XML deadlock reports. So if you're ever baffled about why simple, seemingly non-conflicting INSERTs and UPDATEs are suddenly the apparent source of deadlocks and severe blocking issues, be sure and check if they are involved in any indexed view.