 On today's Visual Studio Toolbox, Phil will show how to handle transient database errors. Sounds scary. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today is Phil Jepixi. Hey, Phil. Hey, Robert. We are well into our multi-part series on entity framework core. We're in our in-depth episodes, and today we're going to talk about connection resiliency. What's issue, what happens, what do we do about it? Connection resiliency is an opportunity for your software to retry when certain transient errors happen on the database side. Now, what a transient error is, and I'm just talking about SQL Server here, but all database providers support it, is maybe there's not enough connections available, and so your process failed, or TempDB is full, or any of those things that cause a problem, but it's not a problem if you try again later. Does this include network connectivity? It does not. We're only strictly speaking about database issues in this particular. You send a message a query to the database, the database receives it, but then the database for some reason can't go through with it because of a transient issue. Then the idea is that you want to retry a reasonable number of times in a reasonable time frame, because maybe it'll go through, and then ultimately if it doesn't go through, then you got a bigger problem. Yeah. If it doesn't go through, then you will get a specific error that then tells you as a developer that it was a transient error, and then you can handle it appropriately whatever that means for your application. So if that means telling the user to wait five minutes and retry, or to call the help desk, or talk to your IT team. We should absolutely show the raw error to the user because users want to know these things. Absolutely. The error messages coming out of Microsoft products are so eloquent, that it's worth just showing that. Absolutely. So let's jump into the code and see how this works. Let's look at our blogging context. Again, I'm going with some really simple demos here. So there is an option when you create your DB context, and we talked in an earlier show about all the configuration you can do with the DB context to have some SQL Server options. So here I'm saying use SQL Server, pass in the connection string, then there's an action delegate called SQL Server Options Action, and one of those options is enable retry on failure. Now, this is a shortcut for creating a new SQL Server retry exit strategy. Is retry on failure off by default? It is off by default. You have to opt in. So everything in .NET Core is opt in. So you get nothing out of the box. Matter of fact, if you don't tell it to use SQL Server or use some database provider, you don't have a database, or you don't have any way of connecting to a database. So yeah, everything in the .NET Core paradigm is all opt in. So let's look before we go and see how this works with .NET Core. Let's just look at what this SQL Server retrying execution strategy does, and it's just based on an execution strategy, and the method that we care about is should retry on. So this is what we will override it when we create our own, but it's going to look at the SQL Server errors, and you can pass in additional ones I've never needed to, and you ask me what kind of things can do it. Well, if you really want to geek out with the error numbers, they're all here in the source code. But there's, for example, not enough resources, maximum number of commit dependencies, so all database-y stuff. So we have turned on the retry on the failure, and the problem is it's really hard to replicate these transient errors. It's like trying to replicate a threading issue. So I'm actually going to comment out the enable retry on failure, and when they add in my own custom execution strategy. When we create a custom execution strategy, we inherit from execution strategy, and then here we override the should retry on. So what I have done is regardless of the error that comes from SQL Server, I'm just going to return true because I want to demo how this works. What this SQL Server team has done is decided which ones were transient, put that in, we can add additional ones. Even with the default retry on failure, we can change the max retry count, the time delay, and this allows us to say, well, instead of retrying five times, maybe we want to retry 10 times. Maybe we want to do 10 milliseconds for the first retry or we change it to be 100 milliseconds. So it's totally customizable. Now, when we run this, what will happen is I have the SQL Server error I'm replicating as a bad login, which is not a transient error, but again, to test it, we get this retry limit exceeded exception. It's actually funny we were making some quips about error handling and messages from Microsoft products, because this is actually a really good error message. So let's run this. Now, all of these will drop and recreate database so it takes a little time to run. Is there a, how do you know how many times to retry and how long to wait? Is there a good rule of thumb? So it's a default. That's the good rule of thumb. The defaults don't work for you, then tweak it. The nice thing about how customizable EF Core is and even the out-of-the-box retry on failure, you could set up like if you've got some monitoring software going on with your database, and this works for Azure SQL as well as on-prem SQL, or in a VM SQL. You can say, okay, well, low times between 4 p.m. and 6 p.m. Eastern are the highest. So I want to up those numbers during that time. Then at off times, I can lower it. Because EF Core is configurable per call, you can actually put something in like if it's a web app, you can put something in the app settings.json to then set what the retry should be. So you can customize it per request. So let's look at the message and we see right here that the exception message is maximum number of retries, five exceeded while executing database operations with the custom execution strategy. So you get our exception for most recent failure. If we look at the inner exception, it's going to be invalid login. Okay. So now that I know that I have a transient error, it's not that SQL server is down, it's not my app is down. I can programmatically decide what to do, and this of course will be just be business dependent, right? I can put a thread.sleep 1000 in here and retry. Or I could just send a message back to a user and say, there seems to be a lot of people trying to get on the website right now, why don't you try again later or try off peak hours, whatever your business dictates. Sure. So that's it. I mean, it's a really simple thing. It's one of those things that I have a few rules whenever my team works with any of your framework core, which is how we do all of our data access. But this is one of those musts. I make it a requirement that every time we are building a context that we turn on retry on failure because it really is free, right? Yeah. That's good to know. I would have assumed that there was a built-in retry, but there is, but it's not too. Right. Now, in your custom execution strategy, there was some code in there at the bottom talking about transactions. Yeah, I talked about that for a sec. I'm glad you brought that up because it was on my list and I told, they forgot to bring it up. I was thinking this was kind of a short episode. There's nothing wrong with that, but there we go. Yeah, so if you're writing transactional code in a non-custom execution strategy world. So to be clear, there's always an execution strategy. In fact, there's always an architecture. Sometimes they're bad. So there's always an execution strategy, but we've added on the SQL server retry execution strategy. So now to run a transaction, we actually have to take that strategy into account. So without enabling retry on failure, this is all I have to do to use an explicit transaction with EF Core. Okay. Now, I will tell you from our earlier shows, if I just do a save changes, that's within a transaction. I don't have to do anything different if I'm using the built-in transaction support in EF Core. This is only if I'm doing an explicit transaction. So what I have to do is I have to get the execution strategy and then acute the transaction in the execution strategy context. Okay. So if we, let's do this, just so we can see it happen, execute the transaction, and we're gonna pass in our context, which of course in this demo I called DB. Early on I called them DB always and then later in my demos I call them context. So just trust me up a little bit. Do you typically just call it context? Do you call it the database, name of the database context? How verbose do you like to get? So for a variable name, I always call it context these days. And that's been for about a year now, so that's pretty consistent. From a naming of the actual context, I usually just go with application DB context. If I have more than one context in an app, then I'll use a more specific name, but usually I have just one context which I configured differently. I used to go with these creative names and then it just got to be more work to remember what I called it. I have a little bit of old-timers disease. Well, it's also makes it more difficult to copy the code into a new project because then you have to rename more things. Yeah, I have a standard architecture we carry around for EF Core and ASP.NET Core that's customer neutral. And when you start using customer initials or names in your variables, in your base architecture, then it gets a little complicated. So now I'm just in the operation is executing within the execution strategy context. It's just a regular transaction at that point. I'm still failing to log in, so I still get the same error which will then bring me back to the error handler if I had it in the try catch. So that's the one watch out, I guess. We always turn it on. And then we just have to remember where this really comes into play more than anything else is our integration tests. So we'll write a whole bunch of extra integration tests and we always want to wrap them in transactions because we're going to add a record, we're going to make sure it got added, then we're going to delete that record and we don't want to be jumping the identity count and making our database ugly. We always want to leave the system under tests in the same state it was when we started. So I actually have, in my base tests, I don't think I have it in this project, but I think I showed it when we did our series on testing. I have a base method that's called execute a transaction. And what I do is I, in my test, I create a local function, a C-Sharp8 local function that then I pass in as a delegate into my execute and transaction. So it'd be something like this and it would be an action, right? Method to execute. And then I just execute the action in here, right? So method to execute right there. I've got all the asserts in there, so it looks like just a unit test. And then if this throws an exception, right? It will still give me the output I need in my test runner. Okay, cool. So yeah, it's a great little free tidbit they threw out for us to use. And I just wish more people would use it. Yeah. It gives you a whole bunch of benefit for free. Right on. Thanks so much for doing this. Yeah, hope you guys enjoyed that. We will see you next time on Visual Studio Toolbox. Yeah, thanks for watching. Yeah.