Transactions in Oracle

Transactions:-

When we execute any DML statements and it shows statement executed successfully,  it does not mean your transaction is committed. A successfully executed SQL statement and a committed transaction are not same. Therefore whenever you execute the statement we need to make that statement commit to save the changes permanently or we can rollback the changes made.

There are three transaction types:-

·         Commit

·         Rollback

·         Savepoint

Commit:-

By using commit transaction can be made permanent. Commit is very important while dealing with DML (insert, update and delete) statements.

Most importantly, when you perform any DML operation and don’t commit or rollback then for your particular session (from which you have performed DML operations) you can see changes made but other users will be able to see the changes until you permanently save the changes. For example, if you have inserted some values in a table and without committing that insert you fired select statement then, it will be visible for you but not for others until you commit that transaction.

Syntax of the commit is:-

COMMIT;

Let’s look at the example:-

Rollback:-

The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction. For example, if we have inserted some values in a table and rollback the transaction then it will undone all the insert and the table will contain only the records which were present before firing the insert statement.

Syntax of the rollback is:-

ROLLBACK;

Let’s take an example:-

Above insert statement have not inserted any values in the employee table since we have undone the entire insert by using rollback statement.

Savepoint:-

Savepoint is a kind of mark by using which we can split the DML statement for particular point of time.

Syntax of the savepoint is:-

SAVEPOINT SAVEPOINT_NAME;

Let’s look at the example:-

Now check the employee table:-

It shows ROBIN instate of ROBERT for the employee number 10.




Read Also:-