Table of contents
1.
Introduction
2.
PL/SQL Transactions
3.
Structure of Transaction
4.
Commit Transaction
4.1.
COMMIT Syntax
5.
Rollback Transaction
5.1.
ROLLBACK Syntax
6.
Savepoint Transaction
6.1.
SAVEPOINT Syntax
7.
Autocommit Transaction
7.1.
AUTOCOMMIT Example
8.
Set Transaction
8.1.
SET TRANSACTION Syntax
9.
Example Using These transactions
9.1.
Commit Example
9.2.
ROLLBACK Example
9.3.
ROLLBACK WITH SAVEPOINT Example
9.4.
SET TRANSACTION Example
10.
FAQs
11.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Transactions

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

A database transaction is a discrete piece of work made up of one or more SQL(Structure Query Language) statements. It's named atomic because the database changes caused by the SQL statements that make up a transaction can either be committed, made permanent in the database, or rolled back (undone) from the database.

A committed transaction is not the same as a successfully executed SQL statement. Even if a SQL statement is successfully run, it can be rolled back, and all changes made by the statement(s) can be undone unless the transaction containing the statement is committed.

The many forms of PL/SQL Transactions will be discussed in this article. 

PL/SQL Transactions

Oracle PL/SQL is a transactional language. Oracle transactions ensure data consistency. A PL/SQL transaction is a logical unit of work that consists of a series of Sql data manipulation statements. A transaction is an atomic unit that contains all of the changes that have been committed or rolled back.

Oracle makes all database changes permanent save or maybe undone at the end of the transaction that makes database changes. If your programme crashes in the middle of a transaction, Oracle recognises the problem and rolls the transaction back while restoring the database.

To control the transaction, utilise the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands.

  • COMMIT: The COMMIT command saves changes to a database permanently during the current transaction.
  • ROLLBACK: At the end of the current transaction, the ROLLBACK command undoes/undo any changes made since the beginning of the transaction.
  • SAVEPOINT: The SAVEPOINT command saves the current point in a transaction's processing with a unique name.
  • AUTOCOMMIT: Set AUTOCOMMIT ON to automatically execute the COMMIT Statement.
  • PL/SQL TRANSACTION SET: The SET TRANSACTION command configures transaction attributes like read-write and read-only access.

Structure of Transaction

A transaction has a start and a finish. When one of the following events occurs, a transaction is initiated:

  • After connecting to the database, the first SQL command is executed.
  • Following the completion of a transaction, each new SQL statement is issued.

 

When one of the following occurrences occurs, the transaction comes to an end:

  • The statement is either a COMMIT or a ROLLBACK.
  • DDL(Data Definition Language) statement, such as CREATE TABLE, is issued since a COMMIT is completed automatically in that situation.
  • DCL(Data Control Language) statement, such as a GRANT statement, is issued since a COMMIT is performed automatically in that situation.
  • The database is disconnected from the user.
  • When a user departs SQL*PLUS with the EXIT command, a COMMIT is performed automatically.
  • When SQL*Plus crashes, a ROLLBACK is conducted automatically.
  • When a DML(Data Manipulation Language) statement fails, a ROLLBACK is conducted automatically to undo the DML statement.

Commit Transaction

The COMMIT statement can terminate the current transaction and make a permanent update to the data. We can get hold of the updated data once COMMIT has been done.

When the COMMIT statement is executed, all locks on the table's impacted rows are released. It also helps in the removal of the SAVEPOINT. A WORK statement [COMMIT WORK] can be appended to a COMMIT statement for the sole purpose of improving code readability.

If a transaction fails at the moment of COMMIT and the transaction's state is unknown, the COMMENT text in the PL/SQL code is placed in a data dictionary together with the transaction's id.

COMMIT Syntax

COMMIT [COMMENT "comment text"];

 

Backward compatibility is the only reason commit comments are supported. Commit comment will be deprecated in a future edition.

Rollback Transaction

If you stop a current transaction with a ROLLBACK statement, it will undo all the changes that were planned to happen in the transaction.

The following are the characteristics of a ROLLBACK statement:

  • If we delete a critical row from the table by accident, the database is restored to its original form with a ROLLBACK statement.
  • In the event of an exception that causes a SQL statement to fail to execute, a ROLLBACK statement allows us to return to the beginning of the programme and take corrective action.
  • A ROLLBACK statement can undo database updates that were done without using the COMMIT statement.

ROLLBACK Syntax

ROLLBACK [To SAVEPOINT_NAME];

Savepoint Transaction

The current transaction processing point is given a name and an identity by SAVEPOINT. It's usually used in conjunction with a ROLLBACK statement. It allows us to revert portions of a transaction without having to revert the full transaction.

When we apply ROLLBACK to a SAVEPOINT, it deletes all the SAVEPOINTS that are included after that SAVEPOINT [for example, if we have three SAVEPOINTS marked and apply ROLLBACK to the second SAVEPOINT, the third SAVEPOINT will be erased automatically.]

All SAVEPOINTS are deleted when a COMMIT or ROLLBACK statement is issued. SAVEPOINT's names are undeclared identifiers that can be used multiple times within a transaction. SAVEPOINT is being moved from the old to the new position within the transaction.

When you apply a ROLLBACK to a SAVEPOINT, it just impacts the current part of the transaction. As a result, a SAVEPOINT assists in the division of a long transaction into smaller portions by placing validation points.

SAVEPOINT Syntax

SAVEPOINT SAVEPOINT_NAME;

Autocommit Transaction

No need to execute the COMMIT statement every time. You just set AUTOCOMMIT ON to execute COMMIT Statement automatically. It's automatic to execute for each DML statement. Set auto-commit on using the following statement:

AUTOCOMMIT Example

SET AUTOCOMMIT ON;

 

You can also set auto-commit off by the following code,

SET AUTOCOMMIT OFF;

Set Transaction

SET TRANSACTION statement is used to set transactions are read-only or both read-write. you can also assign a transaction name.

SET TRANSACTION Syntax

SET TRANSACTION [ READ ONLY | READ WRITE ]
       [ NAME 'transaction_name' ];

 

Set transaction name using the SET TRANSACTION [...] NAME statement before you start the transaction.

Example Using These transactions

Let's have a look at the TEACHERS table.

With the help of the SQL statement below, we built the TEACHERS table:

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

Commit Example

Fill in the blanks in this table, then commit the transaction as follows:

INSERT INTO TEACHERS VALUES (1, 'PHYSICS', 'PRANAY');
INSERT INTO TEACHERS VALUES (2, 'MATHS', 'JOHN');
INSERT INTO TEACHERS VALUES (3, 'ENGLISH', 'JACOB');
COMMIT;

 

Next, the below query is executed:

SELECT * FROM TEACHERS;

 

Output

ROLLBACK Example

DELETE FROM TEACHERS WHERE CODE= 3;
ROLLBACK;

 

Next, the below query is executed:

SELECT * FROM TEACHERS;

 

Output

We ran a DELETE statement in the above code, which is designed to delete the record of the teacher with a CODE equal to 3. The ROLLBACK statement, on the other hand, has no effect on the database; therefore, deletion is not performed.

ROLLBACK WITH SAVEPOINT Example

INSERT INTO TEACHERS VALUES (4, 'CHEMISTRY', 'NAMDEO');
SAVEPOINT s;
INSERT INTO TEACHERS VALUES (5, 'PYTHON', 'STEVE');
INSERT INTO TEACHERS VALUES (6, 'PYTEST', 'ARNOLD');
ROLLBACK TO s;
INSERT INTO TEACHERS VALUES (7, 'BIOLOGY', 'SUPRIYA');
COMMIT;   

 

Next, the below query is executed:

SELECT * FROM TEACHERS;

 

Output

Only two further rows were entered after ROLLBACK with SAVEPOINT ‘s’ was implemented in the above code, namely teachers with CODE 4 and 7, respectively. Please note that teachers with codes 1, 2, and 3 were added to the chart during its construction.

SET TRANSACTION Example

We will set the transactions to become READ and WRITE format. To set this we use the below command.

SET TRANSACTION READ WRITE;

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

FAQs

1. What is the transaction in PL/SQL?

Ans: A transaction is a collection of SQL data calculation statements that work together as a single unit. All transactions are atomic in nature and can be committed or rolled back at any time.

2. What is COMMIT in PL/SQL?

Ans: The COMMIT statement is used to commit the current transaction's changes to the database. It makes the database modification visible to users.

3. How do you end a transaction?

Ans: A transaction is explicitly ended with a COMMIT or ROLLBACK command. It can also be terminated without warning after a DML statement has been executed.

4. Can we commit in a trigger?

Ans: Yes, we can commit in a trigger transaction only if it is separate from its parent transaction.

5. Is SELECT a transaction?

Ans: Yes, SELECT is a transaction with all of the transaction's properties.

6. How do I roll back a transaction in Oracle?

Ans: To roll back a transaction in Oracle, we must use the ROLLBACK command. It will undo all changes made to the database by the transaction and return it to its previous state.

Key Takeaways

In this article, we learned about some basic concepts of PL/SQL transactions that are essential for PL/SQL Transaction management. We have covered the topics like Commit, Rollback, Set Transaction and Savepoint. We also saw the implementation of these transactions using an Example.

We hope that this article helped you enhance your knowledge regarding PL/SQL transactions and if you would like to explore more, check out our articles on PL/SQL.

You can also refer to the Top 100 Questions of SQL.

Do upvote our blog to help other ninjas grow.

Happy Learning!

Live masterclass