Sunday, January 5, 2014

What is a transaction?

When you log into your database (with sqlplus or any other DB tool), you assign to a Session.
For each log in, a new session will be created.

On that session you can make your DML and DDL commands, and that session is private for the user logged in.

Oracle, and some other DBs, work in a transaction-based mode.
A transaction is a set of DML commands that hasn't been executed for the entire DB but only for the user's session.
To close a transaction you use either -
COMMIT: for saying - OK, I want those changes
ROLLBACK: for omitting the changes on the data.

This allows you to perform multiple DML commands and preview the result in your private session before publishing it.

A session can have only one active transaction (See more here) and a rule of thumb says that you should not leave an open transaction. Every command (or commands set) should be finished with a Commit or Rollback.

Oracle works in a 'Rollback segment' method which is very important to understand:

When a session performs a DML, the data is being changed on the table! It doesn't wait for a commit.
But it marks those affected rows with a flag means they are not updated.
The opposite command is being saved as well.
Lets say we Inserted 30 rows, those rows has been actually inserted into the table, and a 30-rows-delete-clause has been created.

When the transaction is still open, and other sessions query that data, oracle will perform that reverse command and show those sessions that data BEFORE that change.

After a commit, those affected rows are marked as updated and the transaction is terminate.
After a rollback, on the other hand, Oracle will use the reverse script to "fix" the non-wanted rows.

Now you can understand several things:

1) Why Commit takes zero time (Oracle thought that commit is more common than rollback).
2) Why Rollback takes a lot of time.
3) Why deleting a large amount of rows without using commit each 100 rows is a bad thing.
4) Why leaving an open transaction can cause problems.




No comments:

Post a Comment