SQL Server Transaction

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.

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.

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.

Transactional Control Commands

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

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- 

1. COMMIT;

Example –

Example of Commit Command in SQL Server Transaction–

1. SQL> DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. SQL> COMMIT;

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 −

1. ROLLBACK;

Example of ROLLBack Command in SQL Transactions Server –

Example of ROLLBack Command in SQL Transactions Server – 

1. SQL> DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. SQL> ROLLBACK;

Read Complete Article>>