Links

If you’d like to geek out on isolation level escalation a bit more, dig into Conor Cunningham’s post for some of the implementation details. (Some images are missing from the post, but you can get the gist of it.)

Transcript

There are times where you may get serializable isolation level, even if you do not ask for it. I did not order the serializable isolation level, but I may get it. And this is actually quite easy to show.

We will be looking at foreign keys and cascading updates

I’m going-to go ahead and drop my current foreign keys that I have on two tables. I have foreign keys on agg.FirstNameByYear and agg.FirstNameByYearState. The reason I’m dropping them is that they are not cascading updates and deletes. The feature that will escalate your isolation level that I’m going to show you is cascading updates and deletes.

I’m going to recreate those foreign keys, and now the foreign keys say: The parent table, ref.FirstName: if there’s an update or a delete in there, you need to cascade it down to the child table. The children are agg.FirstNameByYearState and agg.FirstNameByYear. So those commands completed successfully.

I’m also going to create some indexes on my child tables for the foreign keys on the child side. On the parent side, ref.FirstName.FirstNameId is the primary key, the parent’s already indexed. But my children: I’m going to put non clustered indexes on FirstNameId on them. So I’m cascading deletes and I’m looking for: hey, who are these children with these FirstNameIds? I can find them quickly. This is a good practice, to index my foreign keys. And that completed successfully, as well.

And now: we’re going to cascade a delete

Ref.FirstName has two children. So I’m going to cascade the delete for FirstNameId = 2 to agg.FirstNameByYear and agg.FirstNameByYearState.

But I’m leaving the transaction open, I’m not committing it.

What locks did I get?

If I go into sp_WhoIsActive now, and I look at those locks: well, what do I see? I see the comment, right, but we can confirm by looking at the TSQL that is running the delete. If I go look at the locks on my delete: wow, that’s a lot-a locks!

Here’s the locks for ref.FirstName. Here’s the locks for the history table, because I still have that temporal table created.

And here’s the locks for agg.FirstNameByYear. Hey, check out, I have key range locks happening on agg.FirstNameByYear.

Similarly on agg.FirstNameByYearState, which is another child: I also have key range locks in that table.

I will only see request mode range if I’m getting key range locks.

My isolation level was escalated for some parts of this operation

This makes sense because although I didn’t ask for serializable, anywhere– I was not in serializable isolation level when I ran this delete– it has to do this.

This is talked about in Conor Cunningham’s post.

We need to use— if I’m cascading these deletes from the parent to the child, I can’t just protect the rows I’m deleting in the child.

What if someone inserted a row with the key that I’m working on? I wouldn’t then be deleting it. I could be left with the value that shouldn’t be there at the end of my cascading delete. That would not be cool. We need to protect a range of the rows.

That means though, that, if I’m using cascading updates and deletes, I’m going to get potentially a lot more blocking and maybe more deadlocks than I thought I would. If I actually do cascade the updates and delete frequently. If you have these in place and you never use them, you’re not going to notice it.

Depending on where you have your foreign key set up – I’ve absolutely run and people who said yeah, we had to stop using cascading updates and delete because the blocking was just crazy. We had to come up with a different way to handle these situations our own, because there was just too many blocking and deadlocks, because of the key range locks.

So we can get escalation to serializable even if we never explicitly asked for it, just because it is needed in that situation.