SQL Server Transaction – Properties, Control, Example

1. Transaction in SQL

In this SQL tutorial, we are going to study SQL Server Transaction. Moreover, we will discuss what is Transaction in SQL and Transactions Properties. Also, we will see the SQL Transaction Example and SQL Server Transaction Control.
So, let us start with SQL Server Transaction.

SQL Server Transaction

SQL Server Transaction – Properties, Control, Example

2. What is SQL Server Transaction?

Transactions in SQL are units or sequences of labor accomplished in a very logical order.
A transaction is that the propagation of one or additional changes to the database. As an SQL Transaction example, if you’re making a record or updating a record or deleting a record from the table, then you’re performing a dealing on that table. It’s important to regulate these SQL transactions to make sure the info integrity and to handle database errors.
Do you know about SQL Create Database
Practically, you may club many SQL queries into a group and you may execute all of them along as a part of a transaction.

3. Properties of SQL Transactions

There are 4 properties in SQL Server Transactions called as ACID.

  • Atomicity − This property is used to ensure that all operations within the work unit are completed successfully. Otherwise, the dealing is aborted at the purpose of failure and the previous operations square measure rolled back to their former state.
  • Consistency – This property is used to ensure that the database properly changes states upon a successfully committed transaction.
  • Isolation − Permits transactions to operate independently of and transparent to each other.
  • Durability − This property is used to ensure that the result or impact of a committed transaction persists in case of a system failure.

4. Transaction Control in SQL

The following commands are used to control SQL Transactions.

  • COMMIT − To save the changes.
  • ROLLBACK – This command is used to roll back the changes.
  • SAVEPOINT − This command is used to create points inside the groups of transactions in which to ROLLBACK.
  • SET transaction − This command is used to place a name on a transaction.

5. Transactional Control Commands

Transactional control commands are solely used with the DML Commands like – INSERT, UPDATE and DELETE solely.

SQL Serer Transaction

Transactional Control Commands

a. The COMMIT Command

The COMMIT command is the transactional command that won’t be able to save changes invoked by a transaction to the database.
Let’s discuss SQL Constraint
The syntax of Commit Command in SQL Server Transaction- 

COMMIT;
SQL Server Transaction

The COMMIT Command – Example

Example –

Example of Commit Command in SQL Server Transaction–

SQL> DELETE FROM CUSTOMERS
  WHERE AGE = 25;
SQL> COMMIT;
SQL Server Transaction

The COMMIT Command – Example

b. The ROLLBACK Command

This command is a transactional command which is used to undo transactions that have not already been saved to the database called rollback command.
Have a look at SQL Expressions
The syntax for a ROLLBACK command is as follows −

ROLLBACK;
SQL server Transaction

The ROLLBACK Command

Example of ROLLBack Command in SQL Transactions Server –

Example of ROLLBack Command in SQL Transactions Server – 

SQL> DELETE FROM CUSTOMERS
  WHERE AGE = 25;
SQL> ROLLBACK;
SQL server Transaction

The ROLLBACK Command

c. The SAVEPOINT Command

A SAVEPOINT command is a point in a transaction when you can roll the transaction back to a certain point and also without rolling back the entire transaction.
The syntax of SAVEPOINT Command in SQL Server Transaction

SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
SQL Server Transaction

The SAVEPOINT Command

Example of Savepoint Command in SQL Server Transaction
Consider the CUSTOMERS table having the following records.

SQL> SAVEPOINT SP1;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=1;

1 row deleted.
Do you know about SQL Operators

SQL> SAVEPOINT SP2;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=2;

1 row deleted.

SQL> SAVEPOINT SP3;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=3;

1 row deleted.

SQL> ROLLBACK TO SP2;

Rollback complete.
Notice that only the first deletion took place since you rolled back to SP2.

SQL Server Transaction

d. The RELEASE SAVEPOINT Command

This command is used to remove a SAVEPOINT which was once created.
The syntax of the Release Savepoint Command in SQL Server Transaction.

RELEASE SAVEPOINT SAVEPOINT_NAME;

e. The SET TRANSACTION Command

This command can be used to initiate a database transaction; it is also used to specify characteristics.
Let’s learn about SQL Data Types
The Syntax of Set Transaction Command

SET TRANSACTION [ READ WRITE | READ ONLY ];

So, this was all in SQL Server Transactions. Hope you like our explanation.

6. Conclusion

Hence, in this SQL Server Transactions Tutorial, we learned transactions in SQL. Moreover, we discussed Properties of Transactions and Transaction control in SQL. Still, if any doubt regarding SQL Server Transaction, ask in the comment tab.
See also –
SQL RDBMS Concept
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.