1 year ago

#364625

test-img

Mikhail Brinchuk

Why SQL deadlock occurs in insert?

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.

sql

sql-server

deadlock

database-deadlocks

0 Answers

Your Answer

Accepted video resources