We have a table where we're updating a table column, increasing the 3length of an NVARCHAR column, but results in DEADLOCK

Question

We have a table where we're updating a table column, increasing the 3length of an NVARCHAR column, but results in DEADLOCK

Cobb Kevin 21

Not sure why this is happening. It's variable and if we try a few times, it eventually updates. The confusing part is, if we're just updating ONE table, how is it involved in a DEADLOCK? The assumption is that it should only be locking the ONE table, but it has to also be locking other tables or there could not be a DEADLOCK. Our DBA thinks it's also locking tables that use the primary key of this table as a foreign key, and that would explain it. If that's the case, is there a way around this?

EDIT: Adding what a DBA sent me when I asked for deadlock trace:

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-26T21:26:23.833+00:00

We need more information. Particularly, need to capture the deadlock graph one way or another. For instance, by enabling trace flag 1222 so that they are written to the errorlog. Once we have the deadlock trace, we can analyse it.

There can certainly be deadlock that involves single table.
Cobb Kevin 21 Reputation points

2021-07-27T11:58:25.557+00:00

We've gotten past the issue, so cannot get more details of the deadlock. I would, however, like to know how a single table can be involved in a deadlock, please and thanks.
Tom Phillips 17,771 Reputation points

2021-07-28T14:57:03.707+00:00

This is a deadlock on a single table, but NOT a single command. Your update command is not causing a deadlock by itself. Your graph shows there are 2 OTHER processes which have schema locks on the table you are trying to update. You would need to investigate those processes.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-28T20:36:57.703+00:00

This is a deadlock on a single table,

Hm, I can't see no evidence of that. As far as I'm concerned that could be three different tables. Yes, those deadlock graphs are useless.

Accepted answer

6 additional answers

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-26T21:26:23.833+00:00

We need more information. Particularly, need to capture the deadlock graph one way or another. For instance, by enabling trace flag 1222 so that they are written to the errorlog. Once we have the deadlock trace, we can analyse it.

There can certainly be deadlock that involves single table.
Cobb Kevin 21 Reputation points

2021-07-27T11:58:25.557+00:00

We've gotten past the issue, so cannot get more details of the deadlock. I would, however, like to know how a single table can be involved in a deadlock, please and thanks.
Tom Phillips 17,771 Reputation points

2021-07-28T14:57:03.707+00:00

This is a deadlock on a single table, but NOT a single command. Your update command is not causing a deadlock by itself. Your graph shows there are 2 OTHER processes which have schema locks on the table you are trying to update. You would need to investigate those processes.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-28T20:36:57.703+00:00

This is a deadlock on a single table,

Hm, I can't see no evidence of that. As far as I'm concerned that could be three different tables. Yes, those deadlock graphs are useless.

Answer 1

I don't fully understand this deadlock, but a key factor is that the ALTER TABLE statement also wants to take a log on the DB_Name_Obfuscated.dbo.PrefixObfuscated_Merchants. Why I don't know, but may be there some dependency.

The first reader process runs a join against merchants and merchants (and more tables). For the second reader process, the statement have been truncated, so we don't see all, but it may be something similar.

Both reader processes are running with NOLOCK/READ UNCOMMITTED. Despite the mode, process do still take locks in this mode, to wit a Sch-S, Schema-stability, locks. While they can live with the rows changing while they are reading, they don't want the schema to change.

The ALTER TABLE statement on the other hand, wants a Sch-M, schema-modification lock, on both tables. It is obvious why this is needed on Organization, less so on Merchants. The ALTER TABLE first takes the lock on Organization, whereas the read first gets a lock Merchants, and then they try to take the lock they want on the other table, blocking each other.

Answer 2

Tom Phillips 17,771

If you are increasing the size of the PK, then every table with an FK to the table would also need to be touched.

Answer 3

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Cobb Kevin ,

Updating the primary key colum? Make sure the parameter data type in the code matches the column data type.

Please check if the thread Concurrent Update On Primary Key Column Making Deadlock could help you.

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Cobb Kevin 21 Reputation points

2021-07-27T11:55:54.777+00:00

Not updating the primary key column, simply updating an NVARCHAR column, increasing the length of the NVARCHAR.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-27T11:57:52.147+00:00

We still don't know what this means. One would like to think that you could at least care to share the statement.

Although, as I noted, to be able to help you, we need to see the deadlock trace.
Cobb Kevin 21 Reputation points

2021-07-27T12:01:21.237+00:00

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA='dbo' AND c.TABLE_NAME = 'Organizations' AND c.COLUMN_NAME='AckOutputFileName' AND CHARACTER_MAXIMUM_LENGTH < 255)
BEGIN
ALTER TABLE dbo.[Organizations] ALTER COLUMN [AckOutputFileName] NVARCHAR (255) NULL
END
GO
Tom Phillips 17,771 Reputation points

2021-07-27T19:51:22.973+00:00

Altering a table requires a schema lock. If anything is trying to use the table, it would result in blocking. However, this would not result in a "deadlock" unless something else is going on.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-27T20:00:31.167+00:00
There is always "something else" going on when you have a deadlock. (Well, almost. There are parallelism deadlocks which affects one session only.)

Here is a possible scenario:

Process A starts a transaction an updates a row in table X.

Process B issues ALTER TABLE on X and is blocked by X.

Process C starts a transaction an updates a row in table Y.

Process C attempts to update a row in tbl A. It is blocked by B as it waiting on a Sch-M lock on the entire table.

Process A attempts to read the row that C updated in Y.

There is now a three-process deadlock, and process B may seem as the cheapest to evict, so it becomes the victim. (And it may be enough, if C is after a completely different row in A.)

Answer 4

Erland Sommarskog 121.9K MVP Volunteer Moderator

I would, however, like to know how a single table can be involved in a deadlock, please and thanks.

Very simple:

Process A starts a transaction.
Process B starts a transaction.
Process A updates row with id = 23
Process B updates row with id = 98.
Process A attempts to update row with id = 98, but gets blocked by B.
Process B attempts to update row with id = 23, but gets blocked by A.
A few seconds later: SQL Server detects the deadlock.

This is just one example.

Cobb Kevin 21 Reputation points

2021-07-28T12:44:14.867+00:00

I pasted the deadlock trace (what DBA sent me) in the OP. This looks promising as the answer as there are three processes. But other than that, I'm not sure what this trace is saying.
Tom Phillips 17,771 Reputation points

2021-07-28T14:54:44.083+00:00

What you posted is the "deadlock graph". That doesn't really show anything except there was a deadlock and who the victim was. We would need to see the "deadlock trace".

However, your graph shows you have multiple processes all with schema locks. That is not related to the command you are running, other than it needs a schema lock to make the change. You need to find the other processes and determine what they are running which requires a schema lock.

Answer 5

Cobb Kevin 21

I see, thanks for the explanation.

I am wondering, in our case (see below), why this could cause a deadlock. It's a really quick ALTER TABLE statement on a non-primary key. I assume that the only table being locked is Organizations, so Process A, running the SQL below, locks the table, but if one or more other processes are trying to insert/update/delete from Organizations, how could this result in a deadlock? Process A either got the lock or it didn't, and if it didn't then it would just have to wait to get it.

ALTER TABLE dbo.[Organizations] ALTER COLUMN [AckOutputFileName] NVARCHAR (255) NULL

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-27T12:18:57.217+00:00

I'm afraid that without the deadlock trace, we can only spend our time on speculations. Maybe it was executed in a transaction? Maybe there was something else you did not tell us? Maybe there is a DDL trigger? Etc.
Cobb Kevin 21 Reputation points

2021-07-27T12:29:45.45+00:00

I see. Thanks so much for the time spent on this. Next time I see this, I'll ask DBA's to get a DEADLOCK trace for better insight into what's happening.
Cobb Kevin 21 Reputation points

2021-07-28T12:41:00.9+00:00

I asked DBA for the deadlock trace and I pasted what he sent me in the OP.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-28T20:29:35.887+00:00

As Tom says that's a deadlock graph, which does not have the full information about the deadlock. In fact, I have never been able to understand what those graphs are conveying.

The one thing I can see is that all three processes are dealing with schema locks, so this is not exactly the scenario I outlined (but that was just a hypothetical discussion.)

The good news is that if DBA has the deadlock graph, he/she should also have the XML, since I supposed this was collected with a Trace, and it has the XML as well. The XML has a lot more details - including the statements of the other two processes.

Share via

We have a table where we're updating a table column, increasing the 3length of an NVARCHAR column, but results in DEADLOCK

6 additional answers

Your answer