We've talked a lot about ways to query your data. And a little bit about how change it.
But, what happens when someone else changes your data while you're reading it?
When do you see their updates? Can the results of your query change part way through? And
what does this mean for your code?
It's time to talk about read phenomena. And isolation levels...
There are three types of phenomena you need to watch for when using a database.
Dirty reads.
Non-repeatable or fuzzy reads.
And phantom reads.
These describe problems that can occur when one person reads the data while another person
changes it. Dirty reads are as bad as they sound. This is where you can see changes made
by someone else before they've committed it!
So you can read a table while part way through a transaction you'll get an incomplete picture.
But, even worse, they might rollback their changes.
So you can read changes which were never saved to the database. This can lead to all sorts
of inconsistent and incorrect results and is best avoided.
Non-repeatable or fuzzy reads are caused by updates and can lead to the same query returning
different results. Say I'm counting my bricks here by colour. So we've got three blue and
three red. After doing this, someone else changes one of the blue bricks here to be
red. We run the query again and we've now got two blue bricks and four red.
A phantom read is a similar issue. But is caused by others inserting rows. So if you
now add another read brick we count them again and we've got two blue bricks and five red.
So why are non-repeatable and phantom reads a problem? After all if someone else saves
changes to a table, you want to see them, right?
Well, not necessarily!
I've instructions for various things I can make with my bricks here, such as this bird
model. Now, to ensure I've always got the bricks I need to make it, what I want to do
is save these bricks here in this box.
So we've got one long blue one, a short blue one, a red, an orange, and finally two yellow.
So now I've got them in the box, but from time-to-time I might want to steal them to
use in something else. So I want to know what's inside it without having to root around and
check what's there. So I'm going to write the contents on the lid. And, to save me time,
what I'm going to do is copy the values from the instructions here.
But, before I do, someone else changes the definition, swapping a blue brick for a green.
And adding a red one. If I copy these new values to the box, the totals don't match
the contents!
I'm doing this all in one transaction. So I want the model definition to stay the same
until I've put all the bricks in the box and written what's needed on the lid.
To fix this, the second query needs to return the data as it existed at the time the first
started.
We'll see how to do this in a minute. But it's worth noting that the real issue here
is we've duplicated information. I've stored the contents of the box on the lid for convenience.
Which could get out of sync with its actual contents. Things like this can happen in real
applications with things like orders and invoices.
As well as the item level prices, people often want to store the overall value. This can
easily lead to inconsistent values and some unhappy customers!
So far we've talked about problems that can occur when you query the same table many times
within a transaction. But these phenomena are also possible within a single query. To
see what effect this has, let's return to counting our bricks here.
First we read the three blue bricks. But, at this point someone else swaps the colours
of these red and blue bricks. We then continue to count those remaining, giving us four blue
and two red. So, due to fuzzy reads, we have a total that was never saved to the table!
And, if someone adds another red brick before we finish, we've now got a phantom read, giving
us three red.
Which of these effects you're exposed to affects how you write your SQL. To help you figure
this out, the SQL standard defines four isolation levels.
These are read uncommitted, which allows all of these issues. Read committed, which only
stops dirty reads. Next you have repeatable reads, which also stop fuzzy reads. And finally
you have serializable, which prevents all these problems. Most databases allow you to
define which level your transaction uses.
So if serializable fixes all these issues, why not just set the isolation level to it,
and be done with it?!
Well, this may decrease the number of that can use your application at the same time!
In serializable, if you try and update or delete rows changed by someone else after
the start of your transaction, you'll hit errors.
When you do, you've got to make a decision. Bail out and tell the user, try the transaction
again, or do something else?
There isn't a fixed answer here. Which is correct depends on your business requirements
and your application. But in any case, the database does more work and you process fewer
transactions. So to get the throughput you want, you may need to use a lower isolation
level.
Oracle Database only implements read committed and serializable. With the default being read
committed.
So if you re-run the same statement within a transaction, you can get different results.
To avoid this, alter the session to set the isolation level to serializable. This fixes
the results of all queries to the time the first statement in the transaction starts.
So you don't see changes by other people. Once your transaction ends, you can then see
what others have done.
Luckily, you don't need to worry about this at the statement level in Oracle Database.
It has a fantastic feature called multiversion concurrency control (MVCC). This gives statement-level
consistency even in read committed. So the results of a query are always fixed at the
time it starts.
No matter how long it runs, or what changes other people make, none of the phenomena apply.
Your results stay the same!
Other database vendors handle this differently. Although the standard appears clear cut, there
are other ways to implement it!
In particular, they may not have statement-level consistency in read committed. Instead, queries
can block writes by other users.
This never happens in Oracle Database. Readers never block writers. Writers never block readers.
These implementation details have big implications for when sessions block each other and on
which statements. Make sure you read and understand how the isolation levels work in all the databases
you use.
But in Oracle Database, you only need to consider non-repeatable and phantom read issues when
querying the same table many times in a transaction.
Finally, Oracle Database implements another mode. Read-only. This works in the same way
as serializable. The results for all queries are frozen to the same point in time. But,
it has the added restriction that you can only run queries.
Any attempt to change rows lead to an error. This can be handy when running reports. Particularly
if you need to ensure that all queries are consistent to the same point in time.
But, when using Oracle Database, usually you can stick with the default of read committed.
If you have a transaction which accesses the same table many times, think carefully about
what should happen while one person reads it and another changes at the same time. Getting
this wrong can lead to hard to track down errors which only appear when millions of
people use your application!
Thanks for watching!
Got any SQL topics you'd like us to create videos on? Let us know in the comments. And
subscribe for future updates.