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.

SQL Transactions

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:

IDNAMEPhone No.Percent
12Sanyam Gupta565215641579%
43Divyam Singh152214125285%
76Diya Sinha478847115477%
88Prabhjeet Singh987554556692%
70Preena Rajora870099791265%

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:

IDNAMEPhone No.Percent
12Sanyam Gupta565215641579%
43Divyam Singh152214125285%
76Diya Sinha478847115477%
88Prabhjeet Singh987554556692%

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:

IDNAMEPhone No.Percent
12Sanyam Gupta565215641579%
43Divyam Singh152214125285%
76Diya Sinha478847115477%
88Prabhjeet Singh987554556692%
70Preena Rajora870099791265%

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:

IDNAMEPhone No.Percent
12Sanyam Gupta565215641579%
43Divyam Singh152214125285%
76Diya Sinha478847115477%
88Prabhjeet Singh987554556692%

Now if we use ROLLBACK command, we will get the following:

ROLLBACK;
IDNAMEPhone No.Percent
12Sanyam Gupta565215641579%
43Divyam Singh152214125285%
76Diya Sinha478847115477%
88Prabhjeet Singh987554556692%
70Preena Rajora870099791265%

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 ];


Comments and Discussions!

Load comments ↻





Copyright © 2024 www.includehelp.com. All rights reserved.