未加星标

Adventures In Foreign Keys 3: A Cascade Of Badness

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏
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.

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 .

SELECTdtl.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 FROMsys.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 BYCASE 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.


Adventures In Foreign Keys 3: A Cascade Of Badness

Seriously Serializable

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.


Adventures In Foreign Keys 3: A Cascade Of Badness

BIG HUGS

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.


Adventures In Foreign Keys 3: A Cascade Of Badness

I’ll make you a believer

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!

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Adventures In Foreign Keys 3: A Cascade Of Badness
本站链接:https://www.codesec.net/view/614248.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(74)