0

I have a test that sometimes fails with a transaction deadlock exception, and I'd like to understand why.

The test several times concurrently inserts data to the table when records with the key being inserted are missing.

The SQL command being executed is similar to

CREATE TYPE TableType AS TABLE (
   [Number] varchar(20) NOT NULL
);

CREATE PROCEDURE [dbo].[Procedure]
    @tvp TableType READONLY,
    @updatedAt datetime2
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Table
    (Number, UpdatedAt)
    SELECT Number, @updatedAt
    FROM @tvp AS tvp
    WHERE NOT EXISTS (
        SELECT 1
        FROM [dbo].Table
        WHERE [Number] = tvp.[Number]
);
END

The table is created by the following code:

CREATE TABLE [dbo].Table
(
  [Number] varchar(100) NOT NULL,
  [UpdatedAt] datetime2 NOT NULL,
  PRIMARY KEY (Number)
)

Then the test executes this stored procedure several (20) times concurrently with each procedure call wrapped in a serializable transaction, and sometimes it fails with a transaction deadlock exception. Transaction deadlock happens even when there is only a single row in the table-value parameter.

The output of MSSQL database_xml_deadlock_report is as follows (trimmed):

<deadlock>
  <victim-list>
   <victimProcess id="Process_A" />
  </victim-list>
  <process-list>
   <process id="Process_A" transactionname="user_transaction" lockMode="X" status="suspended" spid="61" priority="0" trancount="2" isolationlevel="serializable (4)" xactid="180783" lockTimeout="4294967295">
    <executionStack>
     <frame>
      INSERT INTO Table ... (trimmed)
    </frame>
    </executionStack>
   </process>
   <process id="Process_B" transactionname="user_transaction" lockMode="X" status="suspended" spid="62" priority="0" trancount="2" isolationlevel="serializable (4)" xactid="180777" lockTimeout="4294967295">
    <executionStack>
     <frame>
      INSERT INTO Table ... (trimmed)
     </frame>
    </executionStack>
   </process>
  </process-list>
  <resource-list>
   <keylock objectname="Table" indexname="PK_Table" id="lock" mode="S">
    <owner-list>
     <owner id="Process_B" mode="S" />
     <owner id="Process_B" mode="X" requestType="convert" />
    </owner-list>
    <waiter-list>
     <waiter id="Process_A" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
   <keylock objectname="Table" indexname="PK_Table" id="lock" mode="S">
    <owner-list>
     <owner id="Process_A" mode="S" />
     <owner id="Process_A" mode="X" requestType="convert" />
    </owner-list>
    <waiter-list>
     <waiter id="Process_B" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>

Why does the transaction deadlock happen?

I'm trying to understand how it happens so that different transactions acquire locks in a different order, considering that the data is sorted, so the rows won't be locked in a different order.

Can a transaction isolation level (serializable) be a reason for the deadlocks?

UPD: added a note that stored procedure invocations are wrapped in serializable transactions.

UPD2: The test is to ensure that the procedure updates the table correctly. In the application, it will be used from a transaction containing other DB updates. For this reason, transaction is not open in the stored procedure itself, but in the application.

Mikhail Brinchuk
  • 656
  • 6
  • 16
  • 5
    Side note, I strongly recommend aliases and properly qualifying *all* your objects/columns. A clause like `WHERE [Number] = tvp.[Number]` can easily result in behaviour which [is not a bug in T-SQL](https://sqlinthewild.co.za/index.php/2019/04/23/no-this-is-not-a-bug-in-t-sql/) (related [answer](https://stackoverflow.com/a/67694314/2029983) as the site the article is on appears to be down). – Thom A Apr 01 '22 at 16:40
  • 2
    I would add details about exactly how the application calls the stored procedure, what else might be included inside that transaction, and why the test wraps a procedure call in serializable. For me, the transaction/isolation should be controlled as close to the database as possible. – Aaron Bertrand Apr 01 '22 at 17:07
  • @aaron-bertrand, in the application, the procedure will be called from a transaction containing other DB updates. For this reason, the transaction is not open in the procedure itself, but in the application. Added this to the question. Initially, serializable was chosen because it allowed to achieve reliable updates using MERGE. But MERGE produces transaction deadlocks, now I'm researching whether it is possible to not use it. BTW, I'm trying to follow the approach from your article (https://sqlperformance.com/2020/09/locking/upsert-anti-pattern). Removed `UPDATE` to simplify deadlock diagram. – Mikhail Brinchuk Apr 01 '22 at 17:16
  • @aaron-bertrand, please let me know if I still didn't explain the architecture clearly. – Mikhail Brinchuk Apr 01 '22 at 17:16
  • 1
    That trancount is 2 is suspicious, it leads me to believe that both of these processes entered this transaction scope with an existing transaction already in flight, I still think it would be useful to show exactly how the code works - where does it begin a transaction, where does it commit, what else might be in there... – Aaron Bertrand Apr 01 '22 at 18:07
  • 1
    In the interests of more details... please [Edit](https://stackoverflow.com/posts/71710210/edit) your question to include the output of `select @@version` from the SQL Server that's giving you problems. SQL Server 2019, for example, has numerous cumulative updates that fix deadlocks in particular scenarios: CU5, CU7, CU8, CU9, CU10, CU13 and CU14. – AlwaysLearning Apr 01 '22 at 22:07
  • Please share the query plan via https://brentozar.com/pastetheplan. You can obfuscate it via SentryOne Plan Explorer if necessary – Charlieface Apr 03 '22 at 04:33
  • Have you tried adding a `UPDLOCK` hint on the inner reference to `Table` (inside the `NOT EXISTS` subquery)? Also add a `PRIMARY KEY` to your table type – Charlieface Apr 03 '22 at 04:34

0 Answers0