PL SQL Transactions – COMMIT, ROLLBACK And SAVEPOINT

Learn about the PL SQL Transactions with examples of COMMIT, ROLLBACK, and SAVEPOINTS statements:

In this article, we will continue with PL/SQL series. In the PL SQL Package tutorial, we have learned about Package, its advantages, specifications, and structure.

Here we will explore PL SQL transactions and their characteristics. We shall discuss some PL/SQL statements like COMMIT, ROLLBACK, SAVEPOINTS and so on which are related to PL SQL transactions.

We will also learn about LOCK TABLe and AUTOCOMMIT statements in PL SQL.

Let’s begin with the discussion!!

PL SQL Transactions

An atomic component of work in a database that has multiple SQL statements is called a transaction. It is referred to as atomic because once there are changes affected by SQL statements, they can either be committed [indefinite updates to the database] or rolled back [undo updates to the database].

A SQL statement needs to be committed otherwise it can be rolled back and all the modifications that the SQL statement was supposed to do, remain undone. If the PL/SQL program gets halted in the midst of the transaction, there is no impact to the database and it gets restored to its original state.

COMMIT and ROLLBACK take care of the fact that the changes to the database are either everlasting or undone. SAVEPOINT statement points to the present position in transaction processing.

Start Transaction Processing

Every transaction has a start and a termination.

The following incidents mark a transaction beginning:

• After the connection to the database, the first SQL statement is executed.
• A new SQL statement is executed after the previous SQL statement is completed.

End Transaction Processing

Each PL/SQL program should have a COMMIT or ROLLBACK statement. It solely depends on the programming logic whether we should use a COMMIT or a ROLLBACK after the transaction.

If COMMIT or ROLLBACK statements are not used, it depends on the host environment how the final state of the database should be. For example, in a SQL*PLUS environment, if a PL/SQL block of code is without a COMMIT or ROLLBACK statement, the state of the database will depend on the block of code that is executed right after that.

In an Oracle environment, if we include a COMMIT statement or execute the DISCONNECT, EXIT, or QUIT command after running a data definition or data control query, the transaction is committed. Also, if we include a ROLLBACK statement, the transaction is undone.

Thus a transaction can be ended because of any below scenarios:

• A ROLLBACK or COMMIT statement is executed.
• A DDL query [for example, a table creation statement] is executed, following which the COMMIT is run by default.
• A DCL query [for example, a GRANT statement] is executed, following which the COMMIT is run by default.
• User terminates the database connection.
• User runs the EXIT statement to come out of the SQL*PLUS, following which COMMIT is executed by default.
• SQL*PLUS encounters an unusual termination, following which ROLLBACK is executed by default.
• A DML query does not run successfully, following which ROLLBACK is run by default to bring back the database to its original state.

COMMIT For Permanent Changes

COMMIT statement is capable of terminating the present transaction and making a permanent modification as performed by the transaction. Once COMMIT is executed, we can get hold of the modified data.

As the COMMIT statement is run, all the impacted rows of the table get released from locks. It also helps us to get rid of the SAVEPOINT. A COMMIT statement can be optionally accompanied by WORK statement [COMMIT WORK] which is added only to improve the code readability.

In case a transaction fails at the time of COMMIT and the state of the transaction is not known, at that time COMMENT text in the PL/SQL code is stored in a data dictionary along with the id of the transaction.

Syntax for transaction COMMIT:

COMMIT;

Let us consider a table named TEACHERS.

We have created the TEACHERS table with the help of the SQL statement given below:

CREATE TABLE TEACHERS (
   CODE INT NOT NULL,
   SUBJECT VARCHAR (15) NOT NULL,
   NAME VARCHAR (15) NOT NULL,      
   PRIMARY KEY (CODE)
);

Insert values to this table and then commit the transaction in the following way:

INSERT INTO TEACHERS VALUES (1, 'SELENIUM', 'TOM');
INSERT INTO TEACHERS VALUES (2, 'UFT', 'SAM');
INSERT INTO TEACHERS VALUES (3, 'JMETERE', 'TONK');
COMMIT;

Next, the below query is executed:

SELECT * FROM TEACHERS;

Output:

ROLLBACK To Undo Changes

If a present transaction is ended with a ROLLBACK statement, then it will undo all the modifications that are supposed to take place in the transaction.

A ROLLBACK statement has the following features as listed below:

• The database is restored with its original state with a ROLLBACK statement in case we have mistakenly deleted an important row from the table.
• In the event of an exception which has led to the execution failure of a SQL statement, a ROLLBACK statement enables us to jump to the starting point of the program from where we can take remedial measures.
• The updates made to the database without a COMMIT statement can be revoked with a ROLLBACK statement.

Syntax for transaction ROLLBACK:

ROLLBACK;

Syntax for transaction ROLLBACK with SAVEPOINT:

ROLLBACK [TO SAVEPOINT < save_n>];

Here, the save_n is the name of the SAVEPOINT.

Let us consider the TEACHERS table we have created earlier.

Code implementation with ROLLBACK:

DELETE FROM TEACHERS WHERE CODE= 3;
 ROLLBACK;

Next, the below query is executed:

SELECT * FROM TEACHERS;

Output of the above code should be:

In the above code, we have executed a DELETE statement which is supposed to delete the record of the teacher with CODE equal to 3. However, because of the ROLLBACK statement, there is no impact on the database, and deletion is not done.

SAVEPOINT For Reverting Partial Changes

SAVEPOINT gives name and identification to the present transaction processing point. It is generally associated with a ROLLBACK statement. It enables us to revert some sections of a transaction by not touching the entire transaction.

As we apply ROLLBACK to a SAVEPOINT, all the SAVEPOINTS included following that particular SAVEPOINT gets removed [that is if we have marked three SAVEPOINTS and applied a ROLLBACK on the second SAVEPOINT, automatically the third SAVEPOINT will be deleted.]

A COMMIT or a ROLLBACK statement deletes all SAVEPOINTS. The names given to SAVEPOINT are undeclared identifiers and can be reapplied several times inside a transaction. There is a movement of SAVEPOINT from the old to the present position inside the transaction.

A ROLLBACK applied to a SAVEPOINT affects only the ongoing part of the transaction. Thus a SAVEPOINT helps to split a lengthy transaction into small sections by positioning validation points.

Syntax for transaction SAVEPOINT:

SAVEPOINT < save_n>;

Here, save_n is the name of the SAVEPOINT.

Let us again consider the TEACHERS table we have created earlier.

Code implementation of ROLLBACK WITH SAVEPOINT:

INSERT INTO TEACHERS VALUES (4, 'CYPRESS', 'MICHEAL');
SAVEPOINT s;
INSERT INTO TEACHERS VALUES (5, 'PYTHON', 'STEVE');
INSERT INTO TEACHERS VALUES (6, 'PYTEST', 'ARNOLD');
ROLLBACK TO s;
INSERT INTO TEACHERS VALUES (7, 'PROTRACTOR', 'FANNY');
COMMIT;		

Next, the below query is executed:

SELECT * FROM TEACHERS;

Output of the above code should be:

In the above code, after ROLLBACK with SAVEPOINT s is applied, only two more rows got inserted, i.e. teachers with CODE 4 and 7, respectively. Please note teachers with code 1, 2, and 3 have been added during the table creation.

LOCK Table In PL/SQL

LOCK statement in PL/SQL enables locking of the full database in a locking mode. This determines if we want to share or disallow access to the table.

Let us take an example of a lock table:

LOCK TABLE teachers IN ROW SHARE MODE NOWAIT;

The above query locks the TEACHERS table in row share mode. A row share mode gives permission for a concurrent usage to a table. It does not allow users to lock the full table for individual use. After the execution of a COMMIT or ROLLBACK, the table locks are released. NOWAIT keyword is used to mention that the database is not allowed to wait for a lock to be freed.

The lock mode is important to conclude what other locks can be applied to a table.

Let us discuss some LOCK modes available in PL/SQL:

Automatic Transaction Control In PL/SQL

We can do configuration such that a COMMIT statement gets executed by default whenever an INSERT or DELETE statement is run. This is done by making the AUTOCOMMIT environment variable to ON.

Syntax:

SET AUTOCOMMIT ON;

Again, this can be turned off by making the AUTOCOMMIT environment variable to OFF.

Syntax:

SET AUTOCOMMIT OFF;

Frequently Asked Questions And Answers

Q #1) What is the transaction in PL SQL?

Answer: A transaction is a group of SQL data computation statements that work as an atomic unit. All the transactions are atomic in nature, which are either committed or roll backed.

Q #2) What is COMMIT in PL SQL?

Answer: A COMMIT statement is used to make a permanent change to the database by the present transaction. It makes the modification to the database visible to users.

Q #3) How do you end a transaction?

Answer: A transaction terminates with a COMMIT or ROLLBACK statement explicitly. It can also be ended unconditionally after a DML statement is run.

Q #4) Can we commit in a trigger?

Answer: Yes, we can commit in trigger only if that trigger transaction is standalone from its parent transaction.

Q #5) Is SELECT a transaction?

Answer: Yes, SELECT is a transaction having all the characteristics of a transaction.

Q #6) How do I rollback a transaction in Oracle?

Answer: We need to run a ROLLBACK statement to rollback a transaction in Oracle. It shall undo all the changes made by the transaction to the database and shall be restored to its original state.

Conclusion

In this tutorial, we have discussed in detail some basic concepts of PL SQL Transactions that are essential for PL SQL Transaction management. We have covered the following topics listed below:

• Transaction.
• Use of COMMIT in a transaction.
• What is a  ROLLBACK in a transaction?
• Use of ROLLBACK with SAVEPOINT in a transaction.
• LOCK table.

<<PREV Tutorial | NEXT Tutorial>>