Managing concurrent access to a resource, whether it's a row, a table, or
a single value is easy as long as no one's trying to modify the underlying data.
But what happens if someone changes the data while someone else is reading it?
Well, support
for non-blocking transactions are enabled by default in Azure Sequel Database,
Davide Mauri is here to show us how it works – today on Azure
Friday.
Today, I'm
thrilled to welcome Davide Mauri who is here to talk with us about
non-blocking transactions in Azure SQL Database.
Welcome, Davide.
Hey, Lara, thanks for having me. As usual.
As usual.
It's great to have you on the show again.
So talk to me.
What's going on with non-blocking transactions?
Why do they matter?
Yeah, that's a really interesting topic.
So, well, you know, you've been using SQL Server for a while, like me
and like many out there.
And you know that if you start to write
or modify a row, like insert, update, or delete something
and you are in the middle of the transaction, so you begin a transaction,
you know, do something to change the data in before
committing or, you know, rolling back your transaction.
Someone tries to read or access what you are doing...
Ah! you will be blocked.
So people have to wait in line basically. Right.
So this has been the usual behavior since long time ago in 2005 - SQL Server 2005
we change it a little bit.
We introduced what is called the row versioning
so that you don't have to be block edit you can.
Well let me show you maybe in a day more, it's easier.
But long story short,
the problem is that in your SQL Server by default you still are blocking
because you need to enable this feature manually
on as you'll see a lot of.
This is enabled by default.
So by default you don't have to wait. Yeah.
So let me show my screen so I can more easily explain what's going on here.
So we have our usual table.
You know, I created the sample city with some city and I'm grouping by city name.
So there are several rows, one for each city.
Now what I'm doing on the left side, I'm starting a transaction
and you know, making some changes.
So let's say, I mean, something unusual in deleting something.
I'm still in the transaction
right now because I begin a transaction, but I never closed it.
So ideally I would call this transaction later.
Let's say my
transaction is a long running transaction, so I'm in the middle of the transaction.
Now someone else
can try to read my data.
I'm doing it from another connection, so simulating another application.
And right now I'm simulating to the usual be able to the default behavior
that has been there since you know always insecure server.
And that's why I need to specifically put this a hint here
just to simulate what's happening.
You see this second connection is just waiting any it will wait
for as long as we wish or until this transaction is done.
And that's the default behavior in the box in SQL Server on premise.
Okay. Exactly.
And of course, we kept these because we want to keep,
you know, backward compatibility. Consistency.
Yeah, exactly.
But of course, on your second, we can change a little bit things,
especially for, you know,
cloud born application that doesn't have any ties with existing
or past applications.
So what happened in age, your SQL by default again
without having to manually specify anything is that
let me recreate my table, show that I have exactly the same data.
Now let me begin the transaction again and stop exactly where I stopped before
now without having to do literally every anything.
I can just select from cities and I will be able to access this city.
Now you may be thinking that
I'm seeing the thing I had just deleted.
Like if you are a SQL Server user, if you put no local
all written, committed, well, not the case.
That's not the case.
Because as you can see here, I deleted everything that is relative to Bellevue.
But as you can see here, Bellevue is still there.
So the two transactions are isolated, which means that these second transaction,
the second the connection can still see the let's say, commit the data.
So that's why it's called read commit to zebra, because
until the other transaction is committed, the real data, the existing data
are rows that existed before this transaction started to change the data.
Right.
So anyone can still read the commit the data, so have the correct data
without a wait or be blocked.
And of course,
here on the other transaction,
I can still go on, do my other data and then commit the
and only after the transaction on the left has been committed.
Then if I, you know, re-run my application
on the right side, I will see the change in data.
And this is great because immediately you are faster, you don't have to wait.
You you can you know, your application looks like it's more responsive, right?
So that's why.
You don't have the blocking of the for the readers, which
when you design your application with that in mind, it's important to have that
low latency response for the for that application.
Now I'm curious, though, let's say that
we are migrating
a database from SQL Server on-prem into Azure.
And it's important for us to understand that by default, if you're migrating
to Azure SQL Database, this behavior may change that.
It may change your application.
So for those that want to have a consistent experience
with what they have on-prem when they do the migration
and then maybe make choices later to pull back on the blocking
that that they may be experiencing, how would we handle that?
Well, they can, of course, if they want to.
What we saw is that since we keep
returning the old data by basically making a copy of the old data
into another side of the database, or you still access to that system
because when you are blocking, basically you are waiting in order to get the new data
or to get that your data if the transaction is rolled back, right.
So usually the new behavior is just an improvement and doesn't
bring up problems or any consistency problem to application not migrated.
But in any case, if you if you still want, if you still prefer to wait,
yeah, you can.
Enabled by overriding recommitted lock or just, you know, disabling this feature.
All right.
So you just change the setting on the database.
Yeah, correct. Correct. Right.
So now let me also show you another example that shows
even an improved version of what of this technique called row versioning.
This is also available on SQL Server, but these actually behave exactly
in SQL Server.
And this is something
you have to manually enable because it increases the amount of work
that we need to do behind the scene in order to preserve integrity.
But recover our very interesting case that maybe is not the,
you know, the most common one,
but sometimes you need to have complete isolation between the two transaction
and, you know, let me explain that in a little bit.
So in the transaction we run before we begin
a transaction, inserted some data and then deleted something, right?
And then in just one comment
on the other transaction, we run a select.
Now what happened if the other transactions,
the other connection is also a transaction.
So let's say I begin a transaction, select something.
But now before we say that is the left transaction to be very slow, right?
It takes a lot of time.
What if is now is the right
transaction, the transaction the right side to be very slow, very long.
Like, let's say we do a selector and then we have to,
you know, do some computation and wait for 15 seconds.
It could happen that the transaction on the Lester
finishes before this transaction is done.
So which means that I read the some data and I know I'm protected
by, you know, the changes being done during
that, the execution of the second of the other transaction.
But if this transaction is, then committed, that would happen when I reread
my data within the same transaction that they haven't finished yet.
Well, if I use the regular to read, commit the snapshot to what would happen
is that I read the the old data, then the transaction on the left side
finishes and then here I read some different data
because the transaction on the left is finished.
So I'm still reading the committed rows.
Maybe I don't want to do that.
I want to have something called Repeatable Reader,
which means during my transaction, no matter how long it takes,
I always want to access of the same data.
I access to that the beginning of my transaction
because I need consistency throughout of my transaction.
So in order to do that, what we can do is enable something
called the snapshot of the relationship.
So let me show you first of all what happened.
So let's go back, create the, you know, the usual sample table.
I have my cities here as usual, and now
let me run everything without the snapshot resolution level.
So with the default
right
read, commit, snapshot and run a very longer transaction.
In the meantime here, let me execute this shorter one.
So this transaction on the left has finished
and it's changed some data and let's wait for this transaction to finish.
Should be in a couple of seconds.
Now you will see two different result.
So this is the selector that has been done at the beginning.
Then the transaction was doing something complex
and then he tried to read the same data and discovered the data are different
and now know trying for example, to update the data.
Now that's a problem because you have different set of data.
So this is could be something that you don't want to happen.
So all you have to do is enable the transaction.
The transaction reservation level called this natural,
which basically protect you against this issue.
So let me go again here, clean up everything and start this long
transaction.
So making sure they specify these transactions relation level here,
find it
and run this transaction again.
And what we see is that like magic as your SQL
will preserve the changes that has been done on the site.
On the left side of my screen on the transaction side,
so that the, the, the right side of my screen
and the disconnection will always see the same data during its own transaction.
So this is very important because it really gives you
a toggle that you can decide how much consistency you want.
Of course, consistency come with the price of more resources, use it.
So if you just have a
normal need, I would say, or a standard need
the read commit to the level is more than enough.
If it's enabled by default, you just go or you don't wait.
If you also want not to wait
for a long transaction where data may be changed
during the lifetime of transaction, then you want to switch to the
snapshot
level where your transaction is completely isolated
and you can you know, you can make sure that the data will not change during
the lifetime of of the whole transaction, which sometimes is needed
when you need to have consistency throughout the transaction.
It's these critical and.
You can change the settings then both at the database level
as well as at the connection level or the query level.
Yeah, absolutely.
So you have all the code, all the, you know, option to set
the setting exactly where you need to,
you know, to make sure that you use these settings
only when they are really use it.
Because again, we need to keep basically a snapshot of that,
hence the name of the row that you are attaching.
And these of course cost the resources, right?
And just imagine you do a transaction with a hundred million rows.
Well, we need to keep this naturally.
So you really want that to happen if you need it, right?
So it is these option gives you the ability to choose
exactly how much resource consumption you want to have.
So seems like there are a lot of different transaction
isolation levels that that are available for developers to set
both on Azure SQL database as well as an SQL Server and on prem.
Where do we go to learn more about when to use these different settings?
Oh well, there is a really good documentation online, you know, docs
at micro dot com.
We go and explain all the many transaction isolation level
we have because we have many because they all try to do the same thing.
But using different technique, locking is one that overtraining is another one.
And we decided to just go with total versioning on speculation
because we noticed that it cost a little bit more resources,
but it gives some more performance in the sense that you don't have to wait.
So your application is more snappy, your API responds faster.
So we guess that's the, you know, the usual desire.
But then you literally you have probably seven
or six other remember at the moment different option you can choose from.
So yeah, docs are part of the combination SQL is the place
where you can learn everything about the transactions.
Sounds great.
Thank you so much for joining me today to talk more about
not liking transactions as well as different transaction isolation levels.
It's a great information to learn about.
Yep. We've we've been learning all about non-blocking
transactions and Azure SQL Database – today on Azure Friday.
Hey, thanks for watching this episode of Azure Friday.
Now I need you to like it, comment on it, tell your friends, retweet it – watch
more Azure Friday.