1 year ago
#364625
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