All language subtitles for [SubtitleTools.com] Transactions And Concurrency - Learning Oracle 12c [Video]

In this lesson, we're
going to take a look

at the topic of concurrency
and what it means

to transactions in a database.

So any relational database
has to obey certain rules.

And those rules are really to
keep the data consistent so

that the view of the
data that one user has

is consistent with what's been
committed by another user.

So let's open up our database
connection, our tables,

and the bonus table.

I'm also going to
open up a command

prompt here for the moment.

And let's take a look
at the bonus table.

So when we make a change
using DML language

in the database, that being an
insert, an update, or a delete,

those changes are not
considered real or permanent

until a commit occurs.

So let's do an example
here and insert.

So if we select
from the table now,

we see that row
inserted into the table.

But what does another user see?

So I'm going to connect
with a different tool.

We could also do this
with a different window

in SQL Developer.

I'm going to connect to the
database using SQL*Plus.

And I will select
star from bonus.

Notice here the output from
our original session and from

the SQL*Plus session.

Since the data has not been
committed in this session,

then it's not considered real
or available to users yet.

Switch over.

Type in a commit.

Now, when this session
looks at the data,

it sees what's expected.

And we'll run this
statement again.

And now, it sees
this data as well.

Notice that this
is true of any set

of transactions
that we might have.

Now let's insert another
row, but we're also

going to do other
statements as well

to illustrate how a transaction
is packaged, if you will.

So in this update
statement, we're

going to update this row because
it's where ename equals Joe.

And we're going to set
his job to analyst.

Then finally, we're
going to delete the Fred

row from this table.

We haven't actually
done any changes yet,

so let's execute these,
insert, update, and our delete.

So what do we see?

Well, our session shows
the insert of the Jean row,

the update of the row
where ename equals Joe,

and the delete of the row
where ename equals Fred.

What does this session see?

It sees exactly what it saw
before those three statements

were executed because
the transaction has not

been committed yet.

Consequently, if we roll this
transaction back and now look

at the table, these
two show the same data.

So it's always necessary, as
I run these again, to commit.

You see the rows, commit,
and now, in this session,

the rows are consistent.

In order to do this, Oracle
uses something called undo data.

So the undo data
is the before image

of the data when it's changed.

The rows of data are actually
stored in database blocks

in the database.

And those blocks,
when they're changed,

the original images
of them are moved

to a special place in the
database called an undo table

space.

And there they're kept as long
as a commit has not occurred.

That way, anytime a
user queries that data,

they can basically be
directed toward the before

image of the data rather
than the data that has been

changed by another session.