Home »
SQL
SQL Transactions
SQL Transactions: In this tutorial, we are going to learn about the transactions, properties of transaction, commands used in SQL Transactions, etc.
Submitted by Abhishek Goel, on March 22, 2020
SQL | Transactions
A transaction may be a unit of labor that's performed against a database. A transaction is the propagation of one or more changes to the database. For instance, if you're creating a record or updating a record or deleting a record from the table, then you're performing a transaction there on the table. It's important to regulate these transactions to make sure the info integrity and to handle database errors.
Practically, we'll club many SQL queries into a gaggle and we'll execute all of them together as a neighborhood of a transaction.
Properties of Transaction
| Properties |
Description |
| Atomicity |
It guarantees that all activities inside the work unit are finished effectively. Otherwise, the exchange is prematurely ended at the purpose of disappointment and all the past activities are moved back to their previous state. |
| Consistency |
It guarantees that the database appropriately changes states upon an effectively dedicated exchange. |
| Isolation |
It empowers exchanges to work autonomously and straightforward to one another. |
| Durability |
It makes sure that the changes of a committed transaction remain in case of a system failure. |
Commands Used in Transaction
Value-based control orders are just utilized with the DML Commands, for example, - INSERT, UPDATE and DELETE as it were. While making tables or dropping them we cannot use these commands.
1) The COMMIT Command
The COMMIT order is the value-based order used to spare changes summoned by an exchange to the database.
The COMMIT order is the value-based order used to spare changes conjured by an exchange to the database. The COMMIT order spares all the exchanges to the database since the last COMMIT or ROLLBACK order.
The language structure for the COMMIT order is as per the following.
COMMIT;
Example:
We will consider the following STUDENT table:
| ID | NAME | Phone No. | Percent |
|---|
| 12 | Sanyam Gupta | 5652156415 | 79% |
| 43 | Divyam Singh | 1522141252 | 85% |
| 76 | Diya Sinha | 4788471154 | 77% |
| 88 | Prabhjeet Singh | 9875545566 | 92% |
| 70 | Preena Rajora | 8700997912 | 65% |
The following example will delete a record of a student having a percentage 65 and COMMIT the changes in the database.
DELETE FROM STUDENT
WHERE percent=65%;
COMMIT;
We will get the following result:
| ID | NAME | Phone No. | Percent |
|---|
| 12 | Sanyam Gupta | 5652156415 | 79% |
| 43 | Divyam Singh | 1522141252 | 85% |
| 76 | Diya Sinha | 4788471154 | 77% |
| 88 | Prabhjeet Singh | 9875545566 | 92% |
2) The ROLLBACK Command
The ROLLBACK order is the value-based order used to fix exchanges that have not as of now been spared to the database. This order must be utilized to fix exchanges since the last COMMIT or ROLLBACK order was given.
The punctuation for a ROLLBACK order is as per the following.
ROLLBACK;
Consider the following data table:
| ID | NAME | Phone No. | Percent |
|---|
| 12 | Sanyam Gupta | 5652156415 | 79% |
| 43 | Divyam Singh | 1522141252 | 85% |
| 76 | Diya Sinha | 4788471154 | 77% |
| 88 | Prabhjeet Singh | 9875545566 | 92% |
| 70 | Preena Rajora | 8700997912 | 65% |
Here if we will first delete the record then will use the ROLLBACK command to get back the data deleted.
DELETE FROM STUDENT
WHERE percent=65%;
This will give an output:
| ID | NAME | Phone No. | Percent |
|---|
| 12 | Sanyam Gupta | 5652156415 | 79% |
| 43 | Divyam Singh | 1522141252 | 85% |
| 76 | Diya Sinha | 4788471154 | 77% |
| 88 | Prabhjeet Singh | 9875545566 | 92% |
Now if we use ROLLBACK command, we will get the following:
ROLLBACK;
| ID | NAME | Phone No. | Percent |
|---|
| 12 | Sanyam Gupta | 5652156415 | 79% |
| 43 | Divyam Singh | 1522141252 | 85% |
| 76 | Diya Sinha | 4788471154 | 77% |
| 88 | Prabhjeet Singh | 9875545566 | 92% |
| 70 | Preena Rajora | 8700997912 | 65% |
3) The SAVEPOINT Command
A SAVEPOINT is a point in exchange when you can roll the exchange back in a specific way without moving back the whole exchange.
The sentence structure for a SAVEPOINT order is as demonstrated as follows.
SAVEPOINT SAVEPOINT_NAME;
This order serves just in the formation of a SAVEPOINT among all the value-based explanations. The ROLLBACK order is utilized to fix a gathering of exchanges.
The sentence structure for moving back to a SAVEPOINT is as demonstrated as follows.
ROLLBACK TO SAVEPOINT_NAME;
4) The SET TRANSACTION Command
The SET TRANSACTION order can be utilized to start a database exchange. This order is utilized to determine attributes for the exchange that follows. For instance, you can indicate an exchange to be perused just or perused compose.
The sentence structure for a SET TRANSACTION order is as per the following.
SET TRANSACTION [ READ WRITE | READ ONLY ];