This video introduces the concepts
of transactions and interact actions with database systems.
Transactions are a very important concept.
The concept of transactions is
actually motivated by two completely independent concerns.
One has to do with concurrent
access to the database by multiple clients
and the other has to do
with having a system that is resilient to system failures.
So we're going to talk about each of these in detail in turn.
Let's take a look at the software
structure of how database systems are used.
We have the data itself usually stored on disc.
And then we have the database management
system, or DBMS, that controls interactions with the data.
Often there's additional software above
the DBMS, maybe an application server or web server.
And then interacting with that
might be a human user or
additional software, and the
types of operations that these
users or the software will
be issuing to the database
are the things we've looked at such
as select command and sequel
or update commands, creating tables,
creating and dropping indexes, maybe a help command, a delete command.
So each of the
clients or the software applications
will be issuing these types
of commands for the database, and
most importantly, they will be
issuing them concurrently and we
might have, you know, a
database with one user, ten hundreds
or even thousands of users at the same time.
Let's look at the kind of
difficulties that we can get
into when multiple clients are
interacting with a database at the same time.
We'll be focusing mostly on
sequel statements of modifications and
some queries that are being issued by clients.
And we'll look at some different levels where inconsistency can occur.
We're going to focus our first example
just on a single attribute
and how it can have problems when
multiple clients are working on the same attribute.
So let's say we have two clients,
one is issuing a statement that's
increasing Stanford's enrollment by 1000.
The second client, around
the same time is issuing a
statement that's increasing Stanford's enrollment by 1500.
So we have in
the database our college table here.
And somewhere in this college table
we actually have Stanford's enrollment.
Now the way database systems work
is when we are modifying a value in the database .
Effectively, the system first gets
the value, then it modifies
the value, and then it
puts the modified value back in the database.
So here's the value that we're working on.
So, client S1 will
fetch the value, add one thousand to it, and put it back.
Client S2 will do something
similar, but adding 1,500 instead.
Let's suppose Standford starts out
with an enrollment of 15,000
and these two statements are executed concurrently.
What are the final values that we might have?
Well, if one of them runs
before the other completely, then it
will add 2,500 and we will get 17,500.
On the other hand
if they really can interleave their
get, modifies, and puts, then
it is possible that we'll
instead only add a
1,000, because the 1,500 gets
lost, or we could only add 1,500.
So there are three
possible final values if there's
interleaving of the operations that modify the value.
Of course we are going to see
that there are mechanisms in the
database to avoid this, but
I want to motivate the reason that we have to have those mechanisms.
Now let's look at an
example where the inconsistency
actually occurs at the tuple level.
Again, we're going to have two
clients that are both
issuing statements to the database
that will have some conflicting behavior.
In this case they're both modifying
the apply record for the
student with ID 123.
The first client is trying
to change that apply record to have a major that see us.
Well the second is modifying a
different attribute of the
same record, saying that the decision should be yes.
Now let's take a look at again, so we'll have our table here.
And now, in this case, we're looking at an entire tuple.
Let's say that this is
student 123 and so
we have say, the major
in here and the decision in here.
Now I've mentioned already that
the databases tend to use
a sort of get, modify,
put, and we
talked about that at the
attribute level previously, but
in fact the reality is
that it does occur at
the tuple level, in fact sometimes
it occurs at the entire page or disc lock level.
So let's suppose that it
occurs at least at the tuple level.
So, again, we can see the
same problem, then, when we
have the two clients.
Each will be performing
a get, modify, put if they
do it interleaved, then it's
possible that we will see both changes.
Whoops, then it
is possible that we'll see both changes.
We'll correctly get the
major reset and the
decision reset but it's
also possible we'd only
see one of the two changes and it could be either one.
So again, we need some mechanism
to insure that we have consistent
updates to the database that would give us what we expect.
In this case, we would probably
expect both changes to be persistent in the database.
Now, let's go one step further and look at table level inconsistency.
Again we have our two clients,
and they're submitting statements to the database around the same time.
One of them is
modifying the applied table
and it's setting the decision for
applications TS for every
student who's GPA is greater than 3.9.
The other statement, occurring at about
the same time, is modifying the
student table; we've decided to
increase the GPA of every
student who comes from a large high school.
So here we have the apply table.
We have the student table.
We have the first
client S1 working on
the apply table, but the
conditions in which apply
tables are updated depend on probing the student table.
Meanwhile we have client S2
that's modifying the student table.
So what happens in the
apply table can depend on
whether it occurs before or
after or during the modifications
of the student table.
So you know, students would certainly
prefer if their GPA is
increased before the apply
records are automatically accepted based on the GPA.
So again, a notion of
consistency here would be
that we understand that either
all the GPA's are modified first
and then the acceptances are made
or vice versa and we'll
again see mechanisms that will
help us enforce that.
As a final example we'll consider
clients that are interacting with multiple tables again.
But in this case we
also have multiple statements that are playing into the situation.
Specifically we'll see an example,
where we will want a
one statement to not occur
concurrently even between the statements from another client.
So let me just show you the example.
So here again we have two clients.
I'll label them C1 and C2
now, since the first one has two statements.
So what the first client is
doing is moving records from
the apply table to an archive table.
Specifically, it looks in
the apply table for records
where the decision is no
and it inserts those into an archive.
And then in a second statement, and
this is really the only way
to do it, it deletes those tuples from the apply relation.
The second statement... the second
client, I'm sorry, just happens
to want to count the number
of tuples in the apply
table and in the the archive table.
So again we have the
two tables that are... we're
concerned with, the apply table
and the archive table and what
the first client is
doing, is it's moving
some tuples from apply to
archive, and then in
a second statement deleting those tuples from apply.
Our second client-- I'll
put this one in red--is counting
from the apply and then counting from the archive.
Now if we want the
second client to see sort
of a consistent state of
the database, where we don't
have records that are duplicated between
apply and archive, then we
will really want that second
client to go either completely
before, or completely after, the first client.
So after that long sequence of
examples, I hope you
get the feeling of what we're looking for in concurrency.
We have multiple clients interacting
with the database at the same
time, and if they
have true interleaving of the
commands that they're executing on
the database, often update commands
but some select commands as well,
then we may get inconsistent or unexpected behavior.
So what we'd like to have
overall is the ability for
clients to execute statements
against the database and not
have to worry about what other clients are doing at the same time.
Specifically, a little more
generally, we like the class
to be able to execute a sequence
of sequel statements, so that
the client can at least act
like those statements are running in isolation.
Now there's one obvious solution to do this, right?
Why don't we just execute them in isolation?
This database system can take
its client requests and just
do them one at a time with no concurrency.
On the other hand we really do want to enable concurrency whenever we can.
Database systems are geared
towards providing the highest possible
performance, we talked about that way in the introduction into the course.
And they typically do operate it
in an environment where concurrency is possible.
They may be working on a multi-processor system.
They may be using even a multi-threaded system.
And database systems, also as
they access the database, tend to
do a whole bunch of I/O so
a system that provides asynchronous
I/O can also run multiple things concurrently.
It can do one thing while it's waiting for data to be fetched by another.
So assuming that system
supports concurrency, then we
would like the database software to support it as well.
As a very simple example, let's suppose
we have five clients that are
operating on the database and
each one of them is operating
on a completely different part of
the database, and we certainly
wouldn't want to force them
to execute sequentially when they
could execute in parallel without
causing any inconsistency problems.
Let's switch gears entirely now and talk about system failures.
Again, we have our database system
working with the data on disc,
and let's suppose we just
happen to be in the process of bulk loading our database.
Maybe bringing in a large
amount of data from an
external source, say a set of
files, and right in
the middle of that bulk load
we have a system crash or a system failure.
It could be a software failure,
could be a hardware failure, could
be as simple as the power going out.
So, if the database is,
at that point, half loaded, so
let's say half of this data
has made its way to disc
and the other half hasn't, what happens
when the system comes back up?
That leaves us in a rather unpleasant inconsistent state.
Or maybe we're just executing
some commands on an existing
data, remember our example
earlier, where we were moving
tuples from an apply relation to an archive.
So we have our relations here.
We're in the process of moving some
data and then once it's moved,
we're going to delete the
data that we moved, and then
all of a sudden once again
we have a crash or a
failure of some type right in the middle of that move.
So what do we do now?
When the system comes up, how
do we know what was moved and what wasn't?
As a last example, let's just
suppose we were performing a whole bunch of updates on the database.
And as a reminder the way database
systems perform updates is they
bring some data from the disc into the memory.
They modify it in memory
and then eventually they write it back to disc.
So let's suppose in the middle
of that process again we have a system crash.
That would again leave the database in an inconsistent state.
So our overall goal for dealing
with system failures is that
when we want to do something
on the database that needs
to be done in an all or nothing
fashion, we'd like to tell
the system that we want to
guarantee all or nothing execution
for that particular set of
operations on the database regardless
of failures that might occur during the execution.
So we've talked about problems
with concurrency, we've talked about
problems with system failures, and
interestingly the exact same mechanism
can be used to deal with both of those issues,
and that mechanism is not surprisingly transactions.
So overall, a transaction is
a sequence of one or more
operations that are treated as a unit.
Specifically, each transaction appears
to run in isolation, and furthermore,
if the system fails, each transaction
is either executed in its
entirety or not all.
In terms of the SQL
standard, a transaction begins
SQL statement is issued by a client to the database.
When the commit command is
issued that's a special key
word, the current transaction ends and a new one begins.
The current transaction also ends
when its session with the database terminates.
And finally there is a mode
called autocommit and in this
mode each statement, each SQL
statement, is executed as a transaction on its own.
So for each client, we can actually think about a time-line
and the client will be
executing along, it might
say commit and that will
commit anything that happened prior to this point.
Then it might do some operations, maybe
a select, an update, a
delete, and then it
says commit again, and when
it says commit at this point
that turns this amount of
work into a single transaction that's treated as a unit.
Maybe it'll update, maybe it'll
create an index, and again
maybe commit at this point in time
and that, right here, will
turn this into a transaction.
And so we can see
for each client, this is
Client 1, their execution
on the database is seen as
a sequence of transactions, each of which has the properties that we describe.
And then we may have a
second client, that's also
operating on the database, and
then it will also have its set of transactions.
In the next video, we'll give
more details and a
more formal treatment on the
properties that are guaranteed by
transactions, both how they
operate individually and how
they interact when multiple clients
are executing on the database at
the same time or when the system fails.