TRANSACTION refers to a sequence of various operations that are performed (using a single or multiple SQL statements) on a DB as a single unit of work (logical). All the effects of the SQL statements in any transaction could be either applied to the database (all committed) or undone from the database (all rolled back).
In this article, we will dive deeper into the TRANSACTIONS in SQL according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.
Table of Contents
What are TRANSACTIONS in SQL?
A TRANSACTION is a TCL command, and it groups a set of various tasks into one single unit of execution. Every transaction begins with some specific task, and it ends when all of the tasks in a group are completed successfully. If any of these tasks happen to fail, the transaction will ultimately fail. Thus, a transaction will only have two results: failure or success.
Remember that incomplete steps would result in the ultimate failure of the transaction. Thus, by definition, a database transaction must be atomic, consistent, isolated, and durable. So these are usually known as the ACID Properties.
Implementing Transactions Using SQL
Check the following commands that are used in order to control transactions. It is very important to note that such statements can’t be used when creating tables. These are used only with the DML Commands like – DELETE, UPDATE and INSERT.
1. BEGIN TRANSACTION
This transaction indicates what would be the start point of some explicit or local transaction.
Syntax
The syntax of the BEGIN TRANSACTION would go like this:
BEGIN TRANSACTION name_of_transaction;
2. SET TRANSACTION
This transaction places a certain name on any transaction.
Syntax
The syntax of the SET TRANSACTION would go like this:
SET TRANSACTION [ READ WRITE | READ ONLY ];
3. COMMIT
If everything happens to be in order with all statements that exist within any transaction, then all changes that are together recorded in the database are known as committed. Here, the COMMIT command would save all the transactions in the database since the last ROLLBACK or COMMIT command.
Syntax
The syntax of the COMMIT TRANSACTION would go like this:
COMMIT;
Examples
Sample table 1
This is an example that would delete these records from a table that has AGE = 20 and then COMMIT any changes in the DB.
Queries
DELETE FROM Employee WHERE AGE = 20;
COMMIT;
Output
Here, two rows that exist in the table get deleted. Thus, the SELECT statement looks like this:
4. ROLLBACK
In case any error occurs within any SQL grouped statements, then all the changes must be aborted. This process of reversing all the changes is known as rollback. And this command could be only used to undo all the transactions since the last ROLLBACK or COMMIT command was issued.
Syntax
The syntax of the ROLLBACK TRANSACTION would go like this:
ROLLBACK;
Example
We can see from the above example in the Sample table1 that:
Deleting the records from this table that have age = 20, then ROLLBACK these changes in the DB.
Queries
DELETE FROM Employee WHERE AGE = 20;
ROLLBACK;
Output
5. SAVEPOINT
This transaction creates some points within a group of transactions where to ROLLBACK. Here, a SAVEPOINT refers to the point in a transaction where we can roll back the transaction to some certain point without even rolling back the whole transaction.
Syntax
The syntax for the SAVEPOINT command would go like this:
SAVEPOINT SAVEPOINT_NAME;
We can only use this command when we want to create a SAVEPOINT among every transaction. ROLLBACK, in general, can be used to undo some groups of transactions.
Syntax
The syntax for rolling back to the SAVEPOINT command would go like this:
ROLLBACK TO SAVEPOINT_NAME;
We can ROLLBACK to a certain SAVEPOINT at any given time in order to return the appropriate information to the original state.
Example
We can see from the above example in the sample table1 that:
Deleting the records from this table that have age = 20, then ROLLBACK these changes in the DB by keeping the Savepoints.
Queries
SAVEPOINT SP1;
// Creation of Savepoint
DELETE FROM Employee WHERE AGE = 20;
// Deletion
SAVEPOINT SP2;
// Creation of Savepoint
In this case, SP1 refers to the first SAVEPOINT that is created before deletion. Thus, in this example, a deletion has taken place.
Once we perform deletion again, the SAVEPOINT SP2 will be created.
Output
Since deletion has taken place, one might decide to ROLLBACK to the SAVEPOINT – the one that we identified as SP1 before deletion.
We can now undo this deletion by this statement:
ROLLBACK TO SP1;
// Completion of Rollback
6. RELEASE SAVEPOINT
The SAVEPOINT command could be used to remove a certain SAVEPOINT that we have created.
Syntax
The syntax of the RELEASE Savepoint would go like this:
RELEASE SAVEPOINT NAME_OF_SAVEPOINT
Once we have created a SAVEPOINT, we can then no longer use this ROLLBACK command in order to undo all the transactions that we have performed since the very last SAVEPOINT. It is mainly used to initiate a DB transaction to specify the various characteristics of the following transaction.
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments