Transaction Control Language in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Transaction Control Language (TCL) Commands in Oracle

In this article, I am going to discuss Transaction Control Language (TCL) Commands in Oracle with Examples. At the end of this article, you will understand what is a transaction and why do we need transaction management as well as we will also discuss and explore the Transaction Control Language commands in Oracle with examples.

Transaction Control Language in Oracle

Before understanding Transaction Control Language commands in Oracle, let us first understand what is a transaction, why do we need to manage the transactions i.e. transaction management, and then we will see how to manage the transaction using TCL commands in Oracle.

What is a Transaction?

The transaction allows us to group a set of related tasks as one logical unit and all of these sets of related tasks are either get committed or get rollback if there is an error. In technical words, we can say that a transaction in Oracle is a set of SQL statements (mostly DML Statements) that should be executed as one unit.

So, a transaction in Oracle ensures that either all of the command succeeds or none of the commands succeeds. If one of the commands in the transaction fails, then all of the commands fail and any data that is modified in the database is rolled back. If all the commands are executed successfully, then the modification made to the database are committed.

Why do we need Transaction Management in Oracle?

When we use the DML statements as soon as we execute the SQL statements the changes are made to the database and it is very difficult to roll back the changes made by DDL statements.

Let us understand this with an example. Suppose user 1 and user 2 have accounts in the same bank and user 1 wants to transfer $100 to user 2 account. There will be a series of statements involved in it, first user 1 account would be debited by $100, then the user 2 account will be credited by $100 and finally, the transaction details will be saved in the bank records.

In this case, we need to perform at least two updates. The first update is happening in the sender’s account from where the money is sending and the other update is happening in the receiver’s account who is receiving the money. Both of these updates should either get committed or get rollback if there is an error. We don’t want the transaction to be in a half-committed state. For better understanding, please have a look at the below image.

Suppose, the first update work successfully and user 1 account was debited by $100 and the 2^nd update failed and the user 2 account was not credited by $100 and the 3^rd query works successfully and the transaction details are saved successfully in the bank record. In this example, though all the queries are interrelated they work separately and in case of failure of any of the queries, it makes it difficult to keep track of data or revert the changes. In such a case, we need to use transaction.

Let us see another example. Suppose, we have a customer table and an address table. And we want if someone adds a customer and an address, then both of the inserts should be successful or both of the inserts should fail, then in such cases, we need to use transactions.

What is Transaction Management in Oracle?

The process of combining sets of inter-related Operations into a single unit and executing those operations by applying the do everything or do-nothing principle is called transaction management in Oracle. For example, the transfer money task is the combination of two operations

1. Withdraw money from the Senders account
2. Deposit Money into the Receivers account.

We need to execute the above two operations by applying the do-everything or do-nothing principle is nothing but transaction management. So, every transaction has two boundaries

1. Beginning
2. Ending

And controlling the boundaries of a transaction is nothing but transaction management.

How to implement Transaction Management in Oracle?

In order to understand how to implement transaction management in Oracle, please have a look at the below image. As you can see in the below image, transaction management involves three steps. First, we need to begin the transaction. Then we need to write the SQL Statements which we want to execute as a single unit. In the third step, we need to check for errors. If there is any error i.e. any of the SQL statements fails, then roll back the transaction (any data that is modified in the database will be rollback) else commit the transaction so that the data is saved permanently to the database.

Transaction Control Language (TCL) Commands in Oracle:

A transaction is a unit of work that is performed against a database. If we are inserting / updating / deleting data to/from a table then we are performing a transaction on a table. To manage the transactions on database tables in Oracle, we are provided with transaction control language (TCL) commands. Transaction Control Language provides the following commands which we can use to implement transactions in Oracle.

1. SET TRANSACTION: It indicates that the transaction is started and is optional.
2. COMMIT: It indicates that the transaction was completed successfully and all the DML performed since the start of the transaction are committed to the database as well as frees the resources held by the transaction.
3. ROLLBACK: It will roll back the data to its previous state.
4. SAVEPOINT: This is used for dividing or breaking a transaction into multiple units so that the user has a chance of rolling back a transaction up to a point or location. It creates points within the groups of transactions in which to ROLLBACK.

Note: The Transactional Control Language commands are only used with the DML statements such as INSERT, UPDATE, and DELETE. The TCL Commands cannot be used while creating tables or dropping (basically DDL operations) them because these operations are automatically committed to the database.

Example to implement Transaction in Oracle:

Let us understand how to implement transactions in Oracle with some examples. We are going to use the following Product table to understand transactions in Oracle.

Please use the below SQL Script to create and populate the Product table with the required sample data.

CREATE TABLE Product
(
 ProductId INT PRIMARY KEY, 
 ProductName VARCHAR2(20), 
 Price INT,
 Quantity INT
);
 
 INSERT INTO Product VALUES(1001, 'Product-1', 1000, 100);
 INSERT INTO Product VALUES(1002, 'Product-2', 2000, 150);
 INSERT INTO Product VALUES(1003, 'Product-3', 3000, 200);
 INSERT INTO Product VALUES(1004, 'Product-4', 4000, 250);

COMMIT TCL Command in Oracle:

The commit statement completes the transaction and saves the changes in the database permanently. The transaction can be inserted, updated, or deleted queries. Once we execute the commit statement, we cannot revert back the changes. The commit statements can be executed after the transaction statement. This command is used to make a transaction permanent in the database. These are of two types.

1. Implicit Commit: These transactions are committed by the system (oracle database) by default. Example: DDL commands
2. Explicit commit: These transactions are committed by the user as per requirement. Example: DML commands

COMMIT Command Example in Oracle:

The following is an example of a Commit Transaction in Oracle. In the below code, we have two DML statements. One is inserting a new record while the second one is updating a record in the Product table. The last statement is the commit statement.

INSERT INTO Product VALUES (1005, 'Product-5', 5000, 300);
UPDATE Product SET Price =3500 WHERE ProductID = 1003;
COMMIT;

Now, execute the above statements one by one and once you execute the commit statement, then you cannot Rollback the above two DML statements. The above DML operations are not possible to “ROLLBACK” because those operations are committed by user explicitly. After committing the transaction, now the Product table will have the following data.

Note: The point here is that only after executing the commit statement the transaction is complete and the changes were saved in the database permanently.

What is the need for the ROLLBACK command in Oracle?

The Rollback command in Oracle is used to undo the transactions that have not already been saved permanently to the database and get back to the initial state from where the transaction was started. So, if you want to restore the data into its previous state, then you need to use the ROLLBACK command at any time after the DML queries have been written but remember once the COMMIT statement has been executed then you cannot ROLLBACK the data. So, this command is used to cancel transactions. But once a transaction is committed then we cannot “rollback(cancel)”.

Example of ROLLBACK TCL Command in Oracle:

The ROLLBACK statement reverts back the changes or restores the table to its previous data. Please execute the below SQL statement to understand the Rollback command. Here, first, we start the transaction and then we execute three DML statements.

INSERT INTO Product VALUES(1006,'Product-6',6000, 300);
UPDATE Product SET Price =550 WHERE ProductID = 1005;
DELETE FROM Product WHERE ProductID = 1004;

When you execute the above transaction and as you can see the transaction is not committed, but if you verify the table, then you will see the above changes in the database as shown in the below image. But these changes are not in a permanent state.

We have not executed the COMMIT statement yet, so we have a chance to roll back all the DML operations on the table by using the Rollback command as follows.

ROLLBACK;

Once you execute the ROLLBACK command, then verify the Product table and you will see the data in its previous state i.e. the state before the transaction started as shown in the below image.

SET TRANSACTION TCL Command in Oracle:

Using SET TRANSACTION in Oracle to begin a transaction is optional. A new transaction begins implicitly with the first DML statement that you execute after you make a database connection or with the first DML statement that you execute following a COMMIT or a ROLLBACK. You need to use SET TRANSACTION only when you want transaction attributes such as READ ONLY that are not the default.

The SET TRANSACTION marks the beginning of a transaction. Any changes you make to your data following the beginning of a transaction are not made permanent until you issue a COMMIT. Furthermore, those changes are not visible to other users until you’ve issued a COMMIT.

Syntax: SET TRANSACTION [ READ ONLY | READ WRITE] [ NAME ‘transaction_name’ ];

Example: Please execute the following statement. As you can see, here, we are starting the transaction by using the SET TRANSACTION statement which is optional in oracle.

SET TRANSACTION READ WRITE;
INSERT INTO Product VALUES (1006,'Product-6',6000, 300);
UPDATE Product SET Price =550 WHERE ProductID = 1005;
DELETE FROM Product WHERE ProductID = 1004;
COMMIT;

Once you execute the above transaction and as you can see the transaction is committed, and if you verify the table, then you will see the above changes in the database table as shown in the below image.

Transaction Types in Oracle:

1. SET TRANSACTION READ WRITE: It is the default transaction type specified as a read/write transaction. It allows us to issue statements such as UPDATE and DELETE.
2. SET TRANSACTION READ ONLY: It is a read-only transaction that does not allow the UPDATE and the DELETE DML statements.

AUTOCOMMIT:

You don’t require to execute a COMMIT statement every time whenever an INSERT, UPDATE or DELETE command is executed. You just need to set AUTOCOMMIT ON to execute COMMIT Statement automatically in Oracle. It is going to be executed for each DML statement. set auto-commit on using the following syntax.

Syntax: SET AUTOCOMMIT ON;

You can also set auto-commit off using the following syntax.

Syntax: SET AUTOCOMMIT OFF;

Why do we need Transaction in Oracle?

We need to use the Transaction in Oracle to safeguard the enterprise data. A transaction is the propagation of one or more changes to the database. For example, if we are creating a record or updating a record, or deleting a record from a table or tables, then we are performing transactions on the table or tables. It is important to control transactions to ensure data integrity and to handle database errors.

What is the thumb rule of Transaction?

The rule of transaction tells that either all the statements in the transaction should be executed (all are committed) successfully or none of those statements to be executed (i.e. all are canceled or rollbacked).

Points to Remember

In this article, you learn about transactions, but you are always using Transactions. You can’t issue a SQL statement and not be in a transaction. If you omit to execute the SET TRANSACTION statement, Oracle will implicitly begin a read/write transaction with the first SQL statement that you do execute. Oracle will automatically commit (or rollback) transactions for you, too, under certain circumstances:

1. Oracle implicitly commits any pending transaction the moment you issue a DDL statement such as CREATE TABLE or TRUNCATE TABLE.
2. Oracle implicitly commits any pending transaction when you exit SQL*Plus normally, e.g., you issue the EXIT command.
3. Oracle implicitly rolls back any pending transaction when your connection terminates abnormally, e.g., when your network connection is broken or when the server (or your workstation) crashes.

In the next article, I am going to discuss SAVEPOINT Command in Oracle with Examples. Here, in this article, I try to explain Transaction Control Language (TCL) Commands in Oracle with Examples. I hope you enjoy this TCL in Oracle with Examples article and understand the need and use of Transactions in Oracle.