Constraint sys.sp_addextendedproperty deadlocked

Question

Good Morning!

I have an isue with a large script. In that script I create a Constraint something like this:

ALTER TABLE "Schema"."Name"
WITH CHECK ADD CONSTRAINT "CK_Table_Column" CHECK
(
    "Column" > 0
);
GO

Then I execute this stored procedure from Microsoft:

EXECUTE "sys"."sp_addextendedproperty"
    @name = N'MS_Description',
    @value = N'Some description.',
    @level0type = N'SCHEMA',
    @level0name = N'Schema',
    @level1type = N'TABLE',
    @level1name = N'Name',
    @level2type = N'CONSTRAINT',
    @level2name = N'CK_Table_Column';
GO

And I have the next error:

Msg 1205, Level 13, State 56, Procedure sys.sp_addextendedproperty, Line 37 [Batch Start Line 10387]

Transaction (Process ID 104) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I'm runnig it on a SQL Server 2017. How is this possible? and more important if the first code (Create the constraint) hasn't finished, how can I control this code to wait until creation finish before add extended property?

Thanks a lot for your valuable help.

Answer 1

Apparently, someone else was poking around in the database at the time, and you managed to clash with that process. What that may be, I don't know. But it is not your ALTER TABLE statement, assuming that the two statements above are part of the same SQL script. Statements in a script are runt perfectly sequentially.

You can use this query to extract information about deadlocks on your instance. You may to change the path for the query to run. (The folder should be the same as for the SQL Server errorlog).

SELECT CAST(event_data AS xml), timestamp_utc
FROM sys.fn_xe_file_target_read_file(
   N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG\system_health*.xel',
   DEFAULT, DEFAULT, DEFAULT)
WHERE object_name = 'xml_deadlock_report'
ORDER BY timestamp_utc DESC

Answer 2

Make sure you are using the most current patch level and retest.

https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level