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.
-
A DDL(Data Definition Language) statement, such as CREATE TABLE, is issued since a COMMIT is completed automatically in that situation.
-
A 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!