Oracle SQL Tutorials – Chapter 6 (Part 2 of 2)

DATABASE TRANSACTIONS

• DB transaction is start with any of them;
• When DML command is running.
• When DDL command is running.
• When DCL command is running.

• Transaction is started when a DML command is executed. And ;
• Ends with one of them :
• When commit or Rollback is occurred.
• A DDL or DCL command is executed ( Autocommit).
• When user logouts.
• When an error occurs in the system

COMMIT & ROLLBACK

• ROLLBACK : Provides reverting data changes.
• COMMIT : It makes the changes permanent.
• These commands help you to track data changes.
• It is provided to session level control of changes made.
• They provide a safety work area.

SAVEPOINT

• The SAVEPOINT command is used to put a save point.
• The ROLLBACK[] command is used to return to created save point.

BEFORE COMMIT OR ROLLBACK

• The owner of the transaction can check the data changed after executing a DML, using SELECT command.
• Other users can not see the results of transactions that the current user did.
• Affected rows are locked.

AFTER COMMIT

• Data changes in the database are permanently applied.
• The previous state of the data is lost.
• All user can see the new data.
• The affected rows are unlocked.
• All savepoints are deleted.

COMMIT EXAMPLE

• I will delete a row from hr.jobs table but I will not commit this transaction. Then I will check this table with another user. This user will see the previous state of the data, because of not committed.

First State :

DELETE FROM hr.jobs WHERE job_id='AD_PRES';

Current user see like that;

But other users see like that; There is still ‘AD_PRES’ row. Because we didn’t commit.

Now I will commit, so changes will be permanent

COMMIT ;

AFTER ROLLBACK

• We use ROLLBACK to undo work done in the current session.
• The data returns to the previous state.
• Changed records are released, locks removed.

ROLLBACK EXAMPLE

• I will delete all rows of the hr.job_history table. Then I will rollback this transaction. Let’s see what happens.

First State of the hr.job_history table :

• Then, I will delete all rows of this table.

DELETE FROM hr.job_history;

ROLLBACK ;

• When I ROLLBACK the uncommited transaction, unmodified data is retrieved from the Undo Tablespace.

FOR UPDATE

• It locks selected records, so that other users cannot change it.
• If more than one table is contained in a join, all selected records are locked in both tables.
• Records are released at ROLLBACK or COMMIT.

FOR UPDATE EXAMPLE

• I will select hr.employees table with FOR UPDATE option.

SELECT * FROM hr.employees FOR UPDATE;

• Then, I’m trying to delete from this table with another user but doesn’t allow me to do that.