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.

Leave a Reply

Your email address will not be published. Required fields are marked *