Original subtitles

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.