Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn’t even get to the point where I wanted to test all those relationships.
But there’s an even worse surprise waiting for you if you do implement foreign keys with cascading actions.
Locking From Hell
Let’s pretend that we gave up on stuff getting deleted from other tables if we canned a post. Let’s just get things set up so that if a user bids us adieu, we’ll get rid of their badges, comments, and posts. It’s, like, GDPR, or something.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ALTER TABLE dbo.Badges ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId) REFERENCES dbo.Users(Id) ON DELETE CASCADE; ALTER TABLE dbo.Comments ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId) REFERENCES dbo.Users(Id) ON DELETE CASCADE; ALTER TABLE dbo.Posts ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId) REFERENCES dbo.Users (Id) ON DELETE CASCADE; |
These all get added without a problem.
What if we delete some data?
Harken Back, Ye Merry Reader
I’m gonna use some code from a previous post about locks taken during indexed view creation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT dtl.request_mode, CASE dtl.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(dtl.resource_associated_entity_id) ELSE OBJECT_NAME(p.object_id) END AS locked_object, dtl.resource_type, COUNT_BIG(*) AS total_locks FROM sys.dm_tran_locks AS dtl LEFT JOIN sys.partitions AS p ON p.hobt_id = dtl.resource_associated_entity_id WHERE 1=1 --AND dtl.request_session_id = @@SPID AND dtl.resource_type <> 'DATABASE' GROUP BY CASE dtl.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(dtl.resource_associated_entity_id) ELSE OBJECT_NAME(p.object_id) END, dtl.resource_type, dtl.request_mode; |
When we run a delete for a single user, we log a Whole Mess® of locks.
What comes back is pretty nasty.
My Own Little Nasty World
If you’ve ever watched locks for modification queries (and who doesn’t spend weekend doing that, really?), most of these will look normal to you.
These locks change based on how many rows we need to get rid of.
For example, User Id 1 is hardly anything, but for the rows we need to delete, we take serializable locks – RANGEX-X.
This type of lock upgrade may come as a surprise to you, but this is how SQL Server guarantees that whole referential integrity thing so that it can trust the foreign keys you’ve got on there. Lucky you!
Let’s think about a perfect storm, where…
- You’ve got lots of cascading actions firing off (we don’t)
- Your foreign keys aren’t indexed well (ours are, we’ll look at the query plan in a minute)
- You need to cascade down large amounts of data (this only hit about 400 rows at most)
- Maybe there’s a begin tran or something in there for good measure (I did that to get the locking info, ha ha ha)
You could end up with really bad locking if any one or two of those things is true.
Let’s say we need to delete Jon Skeet. Because he’s a wonderful piece of outlier data.
SQL Server is all like “object locks immediately.” And, yeah, X locks on an object (table) are a lot like serializable locks on a range, just, you know, bigger.
You Could Learn A Lot From A Query Plan
While query plans don’t show us locking per se, they will show us needing to go out to each table that references Users.
And we can see a delete run on all three referencing tables for both the clustered index, and the nonclustered indexes we have to support the foreign keys.
But hey, at least I got some index seeks. My Friend Forrest® didn’t have as much luck.
Lining Bottoms
Be really careful with how you set up foreign keys. If you have cascading actions on them, you could be causing yourself a whole lot of locking headaches.
We do warn about these in sp_BlitzIndex, if you’re curious about the state of your database.
Next up, we’ll look at different tactics for indexing foreign keys.
We’re going to stay far away from cascading actions, though.
Thanks for reading!
3 Comments. Leave new
[…] named SSISDB – uses cascading DELETEs to enforce referential integrity. In his post titled Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly, by Erik Darling (Blog, About) aka, “Nice post, Brent!” at the Brent Ozar (@BrentO | […]
What is missing here is the equivalent action doing the same thing without cascading FK’s (or any FK’s) with the same data integrity enforced by the FK. (It would require begin tran before starting deleting from the bottom. ) I assume that the data integrity is important, otherwise who’d bother with FK’s or relational databases.
It would be useful to compare two approaches.
Sure, if that’s something you’re interested in, by all means, go for it! There are probably other folks who would find a blog post about that topic interesting as well.