Constraint sys.sp_addextendedproperty deadlocked

James Eduard Espitia Coronado 61 Reputation points
2022-01-25T13:41:54.463+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-25T22:50:08.847+00:00

    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
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-01-25T13:48:42.007+00:00

    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

    0 comments No comments