Introduction
A transaction is a common word which you might have heard many times, maybe at a bank. In everyday language, whenever someone says a transaction is done, the only thing which comes into our mind is that a transaction is an exchange of money between two parties. The bank manages to perform transactions using DBMS. In this article, we will study transactions in DBMS:
Source: www.meme-arsenal.com
Recommended topics, Coalesce in SQL and Tcl Commands in SQL
What is a transaction?
A transaction is a group of tasks/instructions designed to perform analytical work. Each transaction starts with some instruction and ends with some instruction. A transaction always causes some changes in the data of the database. When all the instructions of a transaction are executed, the transaction is said to be successful; else, the transaction is considered failed.
For example, a person wants to transfer Rs. 1000 from his account A to his friend’s account B. So, the instructions for this transaction will be-
- Read the balance of account A.
- Subtract Rs. 1000 from account A.
- Update and save the balance in account A.
- Read the balance of account B.
- Add Rs. 1000 to account B.
- Update and save the balance of account B.
The above instructions are written in everyday language. In DBMS, we use Read(A) and Write(A) functions to depict the instructions in a transaction.
- Read(A): Read(A), or R(A) function, is used to read the value of account A from the database and store it in the buffer in the main memory.
- Write(A): Write(A), or W(A) function, is used to write the value of account A in the database from the buffer in the main memory.
Now, the above transaction instructions can be written as-
- Read(A)
- A = A - 1000
- Write(A)
- Read(B)
- B = B + 1000
- Write(B)
If any of the above instructions fail, the whole transaction is considered to fail. A transaction should either be completed or should not happen at all. The database after a transaction must be consistent. However, it may be inconsistent between a transaction.
Thus, a transaction should follow some properties known as ACID properties. We will now discuss the ACID properties of a transaction.
ACID Properties
To maintain consistency, accuracy and integrity in a database before and after a transaction, the transaction should follow some properties. These properties are known as ACID properties.
ACID properties in transaction stand for:
- A -> Atomicity: According to this property, a transaction must be treated as an atomic unit which means either all or no instructions of a transaction must be executed. Either a transaction should happen completely or should happen at all. There must be no instruction in a transaction that is partially executed. If any instruction fails to complete, the transaction should revert all the previously executed instructions and return to the former state.
- C -> Consistency: According to this property, the database must be consistent before and after a transaction. There should not be any adverse effect on the database's data due to a transaction. The data in the database should always be correct.
- I -> Isolation: The term isolation refers to separation. According to this property, since multiple transactions execute concurrently in a system, each transaction should be independent in the system. One transaction should not affect or be affected by other transactions.
-
D -> Durability: According to this property, the database should be durable enough to retain all of its most recent updates of a transaction even if some failure in the system occurs or the system restarts. The data in the database should become permanent after the execution of a transaction.
To commit the values to the database, we use the COMMIT command after making the changes to the database.
Now we will study the SQL commands to control a transaction in DBMS.
Transaction Control Commands
Transaction Control Command or TCL is a component of SQL used to manage transactions in a database. These are used to manage changes made to the data in a table by DML statements (INSERT, UPDATE and DELETE). It also allows statements to be grouped into logical transactions.
TCL is not used with CREATE and DROP commands since these operations are automatically committed in the database.
There are four commands to control a transaction. These commands are-
- COMMIT
- ROLLBACK
- SAVEPOINT
- SET TRANSACTION
We will study these commands one by one.
COMMIT Command
The COMMIT transaction control command is used to save the changes done in the database during the transaction since the last COMMIT or ROLLBACK command. This command permanently saves the data in the database.
The syntax of the COMMIT command is as follows-
COMMIT;
Let’s take an example to understand the COMMIT command.
Consider the following EMPLOYEE table-
EmployeeID | Name | Age | Salary |
1 | Deepak | 30 | 50000 |
2 | Farah | 22 | 50000 |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
Now, we will delete those records from the table which have a salary less than 60000 and commit the changes in the database.
DELETE FROM EMPLOYEE WHERE Salary < 60000;
COMMIT;
The records are deleted. The records left in the table can be checked using the SELECT statement.
SELECT * FROM EMPLOYEE;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
Note: If we do not use the COMMIT command, then the output of the above DELETE query will be the same, but the result of this query (deletion of first two records) will not be saved into the database. If we close MySql, open it again and perform the SELECT operation, it will give us the same five rows we had initially.
Let’s perform one more query to understand the COMMIT command.
We will insert two new rows into the EMPLOYEE table and commit the changes.
INSERT INTO EMPLOYEE VALUES(6, "Chavi", 23, 65000);
INSERT INTO EMPLOYEE VALUES(7, "Raj", 35, 75000);
COMMIT;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
6 | Chavi | 23 | 65000 |
7 | Raj | 35 | 75000 |
Now, we shall proceed to the following TCL command, i.e., the ROLLBACK command.
ROLLBACK Command
The ROLLBACK transaction control command is used to undo the changes performed in the database that have not been saved during the transaction since the last COMMIT or ROLLBACK command.
The syntax of the ROLLBACK command is as follows-
ROLLBACK;
Let’s take an example to understand the ROLLBACK command.
Consider the following EMPLOYEE table-
EmployeeID | Name | Age | Salary |
1 | Deepak | 30 | 50000 |
2 | Farah | 22 | 50000 |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
Now, we will delete those records from the table having an age greater than 25 years. We will not commit to the changes.
DELETE FROM EMPLOYEE WHERE Age > 25;
The records are deleted. The records left in the table can be checked using the SELECT statement.
SELECT * FROM EMPLOYEE;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
2 | Farah | 22 | 50000 |
4 | Peter | 23 | 70000 |
Now, we want to undo the previous query executed. For this purpose, we will use the ROLLBACK command.
ROLLBACK;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
1 | Deepak | 30 | 50000 |
2 | Farah | 22 | 50000 |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
Let’s move to the third command, i.e., the SAVEPOINT command.
SAVEPOINT Command
Suppose you have made specific changes to the database during a transaction without committing them. Now you want to undo some of your changes. You can perform the ROLLBACK command, but in that case, you will lose all of your changes since you don’t have any last commit. So, in this case, the SAVEPOINT command will save you.
The SAVEPOINT transaction control command is used to undo the changes to a certain point without rolling back the entire changes in a transaction.
The syntax of the SAVEPOINT command is-
SAVEPOINT SAVEPOINT_NAME;
The above command will only create a SAVEPOINT of the mentioned name. The ROLLBACK command is used to undo the changes till the SAVEPOINT. The syntax for this is-
ROLLBACK TO SAVEPOINT_NAME;
Let’s understand the SAVEPOINT command using an example.
Consider the following EMPLOYEE table-
EmployeeID | Name | Age | Salary |
1 | Deepak | 30 | 50000 |
2 | Farah | 22 | 50000 |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
We will perform some queries to delete three records from the table and will create a savepoint after executing each query.
SAVEPOINT S1;
DELETE FROM EMPLOYEE WHERE EmployeeID = 1;
SAVEPOINT S2;
DELETE FROM EMPLOYEE WHERE EmployeeID = 2;
SAVEPOINT S3;
DELETE FROM EMPLOYEE WHERE EmployeeID = 3;
The records are deleted. The records left in the table can be checked using the SELECT statement.
SELECT * FROM EMPLOYEE;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
Suppose now we want the employee with EmployeeID 3 back in our database. For this purpose, we will roll back to savepoint S3. The last delete query will be undone.
ROLLBACK TO S3;
The output of the above query will be-
EmployeeID | Name | Age | Salary |
3 | Juhi | 29 | 60000 |
4 | Peter | 23 | 70000 |
5 | Bhavya | 26 | 65000 |
We can also remove the SAVEPOINT created using the RELEASE SAVEPOINT Command.
The syntax of the RELEASE SAVEPOINT Command is-
RELEASE SAVEPOINT SAVEPOINT_NAME;
For example, if we remove the savepoint S2.
RELEASE SAVEPOINT S2;
Then, we cannot roll back to savepoint S2.
Finally, we will discuss the final TCL command, i.e., SET TRANSACTION Command.
SET TRANSACTION Command
The SET TRANSACTION command starts a database transaction. This command is also used to specify the characteristics of the transaction.
For example, we can specify whether a transaction should be read write or read only.
The syntax of the SET TRANSACTION command is-
SET TRANSACTION [ READ WRITE | READ ONLY ];
Must Recommended Topic, Schema in DBMS