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.
