Transcript
This example is pretty confusing. Honestly, sometimes when I first look at it, I’m like: wait how does this work?
We’re going to step through this again with some graphics
I think it’s easier when you look at the execution plan all drawn-out like a football play. I think it’s a little easier to understand. The reason that it’s important, even though it’s confusing, is that it shows that these non-repeatable reads can happen even in a single select statement.
It would be really hard for you to ever know it happened until a customer came to you and is like: look I got these results, and these are just not right. And then it’s even very hard to figure out what the heck happened, because it had to do with: we were reading while updates were happening. It’s very hard to reproduce that.
Raising our isolation level, potentially using repeatable read or serializable – potentially using optimistic locking, these are not our only choices – but raising our isolation level can protect us from these inaccuracies.
Under read committed, here’s what happened
Before we started our query, there was that explicit transaction. It said begin tran, and then it did an update on ref.FirstName. But it only needed to update columns that were in FirstReportYear_Etc. It wasn’t updating any columns in LastReportYear_Etc.
SQL Server is smart enough for an update – if we’re not updating stuff in an index, it’s like: oh, I don’t need to lock that index. We didn’t have to put locks on the LastReportYear_Etc index. That means that when our select query started it did that build phase that runs first. It was able to read from that index. It was able to read three columns from that index. It didn’t have all the information needed, but it got the FirstNameId, it got the FirstName, and it got the LastReportYear.
But then, when it went to do the probe phase, it was blocked. Because that lock is there, it had to wait. Meanwhile we have let go of our locks. The select query has let go of its locks at this point on LastReportYear_Etc. Since it’s in read committed, it was able to let this go. So when the modification, then, its second update statement went to do an update, the data happened to be in LastReportYear_Etc. It was able to got its lock and it committed.
Now, OK, the second update ran. I no longer have to wait, I can read from FirstReportYear_Etc. We got the last two columns that we need from there.
But, what happened is we got the FirstName, And, the rest of the information out of sync. We think that the FirstName Babe has the total name count that it in fact never actionally had, because while we’re reading data that’s committed, we’re only holding our locks for right now.
So we have a non repeatable read that was returned. That was the FirstName. If we were to go and we were to read that again, if this was a multi statement transaction, we would see the name Baba there. We would not see Babe. We cannot repeat the read and get the same result for Babe.
How does this change under repeatable read?
When we did this under repeatable read, the modification started up, it got its lock. That was the same.
But here’s where things got different: we read… our select query fired up, it was able to read the build phase. But it didn’t let go of those locks. It left those locks there, and is holding them until the select statement completes. It then goes to wait. The modification now tries to do its second update statement, which is modifying that index that we have locked.
And here we are in this situation where we are stuck at a traffic jam. We are on a one way street, you know, we’re pointed each other and, I have a resource that the other transaction needs. It has a resource that I need, and we just can’t make progress.
The deadlock manager wakes up, it sees that we’re stuck and it says: one you is gonna have to go. It picks the cheapest one, which in this case is the select, and, is not going to return any data, it’s just dead.
And, the modification can complete.
I mean, this is a feature though, because we didn’t get that non repeatable read.
Under serializable, the same scenario plays out, except it holds range locks
It also protects us from that non-repeatable read, the only difference here is that it’s a key range lock, so that if an insert were to try to happen, it would protect against that. We still end up in a situation where– just like with repeatable read– we’re blocking one another. We can’t make progress.
I’m sorry, select statement, you have been chosen as the deadlock victim, please rerun your transaction. Says the deadlock manager, and it says that in a little dinosaur voice, “rawr rawr rawr” And, then the modification completes.
Hiram asks: why is the deadlock manager a dinosaur?
You know, I was thinking about this. Like everyone else, I like emoji. Not the emoji movie, but just the emoji. and I was just looking at my favorite emoji, like which would be the deadlock manager? And I think of dinosaurs as, you know, kind of cool animals. They’re kinda friendly, but also kind of archaic. It’s also kind of old-school and old fashioned to have all this locking, and it’s just sort of deadly.
So it’s kind of a T-Rex move to just be like “RRRAWR!”, right?
We DO have smarter more gentler ways that we can take care of this, that don’t necessarily involve having dinosaurs kill off your transactions.