>> On today's Visual Studio Toolbox,
Phil will show how to handle
transient database errors. Sounds scary.
[MUSIC]
>> Hi, welcome to Visual Studio Toolbox.
I'm your host, Robert Green, and joining me
today is Phil Japikse.
Hey, Phil.
>> Hey, Robert.
>> We are well into our multipart series on Entity Framework Core.
We're in our in-depth episodes and
today, we're going to talk about connection resiliency.
So what's issue?
What happens?
What do we do about it?
>> So connection resiliency is an opportunity for your software
to retry when certain transient errors
happened on the database side.
Now, what a transient error is,
and [inaudible] just have a SQL server here,
but all database providers support it,
is maybe there's not enough connections available,
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.
>> So does this include network connectivity?
>> It does not. We're only strictly
speaking about database issues in this particular [inaudible] .
>> So 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 them.
>> Which absolutely show the raw error to
the user because users want to
know these things at that deep level?
>> Absolutely, and the error message is coming out of
Microsoft products are so eloquent that it's worth just showing.
>> Absolutely.
>> So let's jump into the code and see how this works.
>> Okay.
>> So let's look at our blogging contexts.
Again, I'm going with some really simple demos here.
So there is an option when you create your DbContext,
and we talked in an earlier show
about all the configuration you can do with
the DbContext to have some SQL Server Options.
So here I'm saying use SQL Server passing 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 and failure off by default?
>> It is off by default, and you have to opt-in.
So everything in.NET Core is opt-in.
>> Okay.
>> So you get nothing out-of-the-box.
As a matter of fact,
if you don't tell it to use
SQL Server or use some database provider,
you don't have a database.
If you don't have any way of creating 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 EF Core.
Let's look at what does
SQL Server retrying execution strategy
does and it just based on an execution strategy.
The method that we care about is should retry on.
So this 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.
You're asked me what kind of things can do it?
Well, if you really want to geek out with the errored numbers,
they're all here in the source code.
But there's, for example,
none of resources, maximum number of commit dependencies.
So all databases stuff.
>> Okay.
>> 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 kind of 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.
Then here, we override the ShouldRetryOn.
So what I have done is
regardless of the error that comes from SQL Server,
I'm just going to return true.
Because I'm going to demo how this works.
What does 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 maxRetryCount, the time delay.
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 a 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 RetryLimitExceededException.
It's actually kind of 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 the database so it takes a little time to run.
>> How do you know how many times to retry and how long to wait?
Is there a good rule of thumb?
>> Go into defaults,
that's the good rule of thumb.
If defaults don't work for you, then tweak it.
>> Okay.
>> 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 VM SQL.
You can say, "We'll load times between
04:00 PM and 06:00 PM Eastern or the highest.
So I want to up those numbers during
that time and off times I can lower it.
Because EF Core is configurable per call,
you could actually put something in,
like if it's a web app, you could put something in the
appsettings.json to then set what do retries should be.
>> Okay.
>> So you can customize it per requests.
So let's look at the message.
We see right here that
the exception message is maximum number of retries,
five, exceeded while executing
database operations with the customer ExecutionStrategy.
See inner exception for most recent failure.
If we look at the inner exception,
it's going to be invalid login.
I know that I have a transient error,
it's not that SQL server is down,
it's not LAMP is down,
I can programmatically decide what to do.
This of course will just be business dependent.
I could put a thread ".sleep 1000" in here in 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 Entity 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 ExecutionStrategy,
there was some code in there at
the bottom talking about transactions.
>> Yeah, good point.
>> Can you talk about that for a second?
>> Yeah I'm glad you brought that up because it was
on my list and I totally forgot to bring it up.
I was thinking this is a short episode.
>> There's nothing wrong with that but there we go.
>> So if you're writing transactional code in
a non-custom ExecutionStrategy world.
So to be clear there's always an ExecutionStrategy.
it's like there's always an architecture, sometimes they're bad.
So there's always an ExecutionStrategy,
but we've added on the SQL server retry ExecutionStrategy.
So now, to run a transaction,
we actually have to take that strategy into account.
Without enabling retry on failure,
this is all I have to do to use
an explicit transaction with EF Core.
>> Okay.
>> Now, 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 ExecutionStrategy and
then execute the transaction in the ExecutionStrategy context.
Let's do this just so we can see it happen.
Executing the transaction and we're going to parse in
our context which of course in this demo I called db.
Early on I call them db always and
then later in my demos I call them context.
So just [inaudible] me up a little bit.
>> Do you typically just call it context?
Do you do call it the name of the database context?
How verbose do you like to get?
>> So for a variable name,
I always call it context these days.
It has been for about a year now so that's pretty consistent.
From a naming of the actual context,
I usually just go with ApplicationDbContext.
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.
>> Okay.
>> I used to go with these creative names and then it
just got to be more work to remember what I called it,
a little bit of old timers disease.
>> It 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.
When you start using customer initials or names in your variables,
in your base architecture,
then it gets a little complicated.
>> Yeah.
>> So now I'm just in the operation is
executing within the ExecutionStrategy context.
It's just a regular transaction at that point.
I'm still failing to login, 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 day 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
[inaudible] integration tests and we're
also going 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 test 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.
What I do is in my test,
I create a local function,
a C#8 local function, then I
parse in as a delegate into my execute and transaction.
So it'd be something like this and it will be an action,
MethodToExecute and then I just execute the action in here.
So method to execute right there.
I've got all the asserts in there,
so it looks like just a regular unit test
and then if this throws an exception,
it will still give me the output I need on my test runner.
>> Okay. Cool.
>> Yeah. It's a great little free tidbit
they threw out for us to use,
and I just wish more people would use it,
because It gives you a whole bunch 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.
[MUSIC]