1

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.

  • 5
    Put the requests into a queue and process the queue, or add retry logic to your application. There's no way to avoid occasional deadlocks from the type of activity you're throwing at it. – Randolph West Sep 29 '16 at 18:40
  • Do you have foreign key constraints defined on TableB? – Shawn Beddes Sep 29 '16 at 22:31
  • 3
    You should see blocking from the behavior you describe, not deadlocks. Unless some of the batches insert into the tables in the opposite order, or other tables are involved, i don't see how the deadlock came from the queries you posted. I expect there's more to these transactions than you've shared. – Aaron Bertrand Sep 29 '16 at 23:50
  • I agree that he should be seeing blocking behavior and not deadlocks. But it's possible that due to foreign keys, that one session is inserting a record at the end of a page, then a new session is creating a record on a new page. Then when it comes time to make sure the foreign key is valid, it is causing a deadlock across just those 2 pages in the TableA. – Shawn Beddes Sep 30 '16 at 17:44
  • @AaronBertrand Thank you, I agree that I must be missing something here. There is some logic where, in some cases, additional things happen before the COMMIT. But since the deadlocks are always on inserting into TableB, I suspect that's irrelevant. There's absolutely nothing else happening in the transaction BEFORE that, other than the insert into TableA. – Todd Menier Sep 30 '16 at 19:45
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
}
1

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.

  • Thanks for the suggestion and +1. I did try removing the FK and it didn't help. The culprit turned out to be an indexed view. – Todd Menier Oct 5 '16 at 13:37
1

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/

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/06/13/indexed-view-deadlocking.aspx

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.

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.