question

CobbKevin-0298 avatar image
0 Votes"
CobbKevin-0298 asked ErlandSommarskog commented

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

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:

118667-image.png


sql-server-general
image.png (170.3 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @CobbKevin-0298 ,

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.


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not updating the primary key column, simply updating an NVARCHAR column, increasing the length of the NVARCHAR.

0 Votes 0 ·

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.

1 Vote 1 ·

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

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered TomPhillips-1744 commented

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

Very simple:

  1. Process A starts a transaction.

  2. Process B starts a transaction.

  3. Process A updates row with id = 23

  4. Process B updates row with id = 98.

  5. Process A attempts to update row with id = 98, but gets blocked by B.

  6. Process B attempts to update row with id = 23, but gets blocked by A.

  7. A few seconds later: SQL Server detects the deadlock.

This is just one example.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·
CobbKevin-0298 avatar image
0 Votes"
CobbKevin-0298 answered ErlandSommarskog commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·

I asked DBA for the deadlock trace and I pasted what he sent me in the OP.

0 Votes 0 ·

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.

0 Votes 0 ·
CobbKevin-0298 avatar image
0 Votes"
CobbKevin-0298 answered

Thanks all, this thread bore much more fruit than I imagined. I did ask the DBA for the deadlock trace XML and he was able to pull something. I am pasting that here. Appreciate any feedback about the content.

119160-deadlock.pdf



deadlock.pdf (243.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CobbKevin-0298 avatar image
0 Votes"
CobbKevin-0298 answered ErlandSommarskog commented

Merchants table has a FK relationship with Organizations and Organizations has about 10 other table relationships. So, sounds like a Schema-M lock will also lock the tables that Organizations tables has a relationship with? Is that also a Schema-M lock or something else (or perhaps it doesn't matter, the fact other tables are getting locked is the issue, doesn't matter the kind of table)?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That sounded a little implausible to me. I mean, I cannot see any reason why SQL Server would need a Sch-M lock on referencing table.

However, I did a quick test, and it confirmed that if run an ALTER TABLE ALTER COLUMN on a non-key column, SQL Server does indeed take out Sch-M with referencing FK constraints. I didn't know that! But then we have the full explanation for your deadlock.

Moral: do schema changes in maintenance windows, even if they are seemingly simple small metadata-only changes.

0 Votes 0 ·