Deadlock involving query against sys.tables

Question

Deadlock involving query against sys.tables

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
April 1, 2015 at 7:22 am

#306187

I got what to me is a weird deadlock.
There's an
ALTER INDEX ALL REBUILD WITH (SORT_IN_TEMP_DB = ON)
that deadlocked with
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Schema],
tbl.name AS [Name],
tbl.object_id AS [ID]
FROM
sys.tables AS tbl
ORDER BY
[Schema] ASC,[Name] ASC
I thought queries against sys tables didn't interact with queries against the database since they just contain metadata.
I wouldn't think it is a resource availability deadlock because a query against sys.tables should be tiny.
Is it because it is an offline rebuild? Does that somehow lock the metadata tables until finished?
The sys.tables query is the one that "lost" the deadlock, so I don't think it was a problem, but I'm not sure why it happened.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ALTER INDEX is a DDL operation, therefore it will be making changes to the system tables, hence needing locks on the rows in the underlying metadata. Without seeing the deadlock graph, that's about all I can guess.

A select will always be chosen the victim in a deadlock by default, as it has done no work that needs to be rolled back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

GilaMonster (4/1/2015)
ALTER INDEX is a DDL operation, therefore it will be making changes to the system tables, hence needing locks on the rows in the underlying metadata. Without seeing the deadlock graph, that's about all I can guess.
A select will always be chosen the victim in a deadlock by default, as it has done no work that needs to be rolled back.

Thanks! I suspected it might be something like that. Good to know!

That second piece of information is great too. I haven't done a lot with deadlocks as yet, so figuring out why they're happening can be a challenge.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

You can try to get SQL to make the ALTER the task that SQL kills if a deadlock occurs, but I've found it's not 100% effective.

SET DEADLOCK_PRIORITY -8

ALTER INDEX ALL REBUILD WITH (SORT_IN_TEMP_DB = ON)

The "-8" is in case the SELECT already sets its dl_p to "low", then -8 will still be below that.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 4

ScottPletcher (4/1/2015)
You can try to get SQL to make the ALTER the task that SQL kills if a deadlock occurs, but I've found it's not 100% effective.
SET DEADLOCK_PRIORITY -8
ALTER INDEX ALL REBUILD WITH (SORT_IN_TEMP_DB = ON)
The "-8" is in case the SELECT already sets its dl_p to "low", then -8 will still be below that.

I can't imagine wanting to interrupt/kill an index rebuild, but thanks.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

ScottPletcher SSC Guru Points: 101097 More actions · Answer 5

Sioban Krzywicki (4/1/2015)
ScottPletcher (4/1/2015)
You can try to get SQL to make the ALTER the task that SQL kills if a deadlock occurs, but I've found it's not 100% effective.
SET DEADLOCK_PRIORITY -8
ALTER INDEX ALL REBUILD WITH (SORT_IN_TEMP_DB = ON)
The "-8" is in case the SELECT already sets its dl_p to "low", then -8 will still be below that.
I can't imagine wanting to interrupt/kill an index rebuild, but thanks.

Really, "can't imagine" it? I'd rather have a rebuild killed than a critical production job even just SELECTing data, but maybe that's just me.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".