SQL Server Transaction – Properties, Control, Example

FREE Online Courses: Dive into Knowledge for Free. Learn More!

We are aware of various aspects and uses of SQL from our previous tutorials. In this tutorial, we will discuss transactions. We will dive deep into what are transactions in SQL and the usage.

We will also discuss various examples of transactions in SQL. When we talk about transactions, we can think of them as a series of work performed on a database.

What are SQL Transactions?

A transaction is a work that is performed against a database.

Transactions are units or arrangements of work achieved in a reasonable request, regardless of whether in a manual manner by a client or consequently by a database program.

For instance, we can face a scenario where we are updating a record, and parallelly some other user is deleting the same record. So, to avoid such cases, we use transactions.

It is essential to control these transactions to guarantee the information is correct and to check the database mistakes.

Essentially, we will club numerous SQL inquiries into a gathering, and you will execute every one of them all together on an exchange.

Transactions are used only with the DML queries i.e. Delete, Update, and Insert statements.

What are SQL transactions

Properties of Transactions in SQL

All transactions follow four major properties referred by the acronym ACID. The properties are as follows:

Sr.NoProperty
1Atomicity
2Consistency
3Isolation
4Durability

Let us understand each of these in details:

1. Atomicity: This property ensures that the operations we are performing on the database during a session are completed successfully.

If a transaction fails before completion, the whole process is sent back to the previous state. This means the transaction is halted at the point of failure and all the previous operations are reset to the original state.

2. Consistency: With this property, we ensure that the database is able to change the states upon successfully committed transactions.

3. Isolation: With the isolation property, we make sure that transactions operate independently and are always transparent to each other.

4. Durability: With the durability property, we make sure that the result or effect of a committed transaction is put down properly in case of any type of system failure.

Properties of Transactions in SQL

SQL Control Statements

We have various defined control statements for transactions and are widely used as commands.

The commands used to control transactions are as follows:

1. START TRANSACTION: starts a SQL transaction for us.

2. COMMIT: helps to save the changes made in the database.

3. ROLLBACK: helps to restore the previous state of the database.

4. SAVEPOINT: helps to create points in the transaction groups to which the system might try to rollback.

5. RELEASE SAVEPOINT: helps us to remove an already existing SAVEPOINT.

6. SET TRANSACTION: helps to name a transaction, so that it can be pointed out easily.

Demo Database

Let us first view our demo database DataFlair_emp2.
Query:

SELECT * FROM DataFlair_emp2;

SQL Demo database

Here we can see our demo database and the contents in it.

Example 1: Let us start a transaction on our demo database.
Query:

START TRANSACTION;

SQL Start Transaction

Example 2: Let us create a save point in our transaction.
Query:

SAVEPOINT initialState;

SQL Transaction Example

Example 3: Let us delete the records with employee age more than 25 and save the transaction as DataFlair_under25.

DELETE from dataflair_emp2 WHERE age>25;
SAVEPOINT DataFlair_under25;

SQL Transactions Example

Now we have saved this state of our transaction as DataFlair_under25.

Let us view the database of DataFlair_under25 transaction.
Query:

SELECT * FROM DataFlair_emp2;

SQL Transaction Example

We can see the updated database with details of employees under 25 in DataFlair.

Example 4: Let us now restore our original DataFlair database.
Query:

ROLLBACK  to initialState;

SQL Rollback

Let us now view our database again.
Query: 

SELECT * FROM DataFlair_emp2;

SQL View DataBase

Example 5: Let us now remove the initialState SAVEPOINT we created.
Query:

RELEASE SAVEPOINT initialState;

SQL release Savepoint

After the execution of the query, our already existing SAVEPOINT is released. Once we release a SAVEPOINT we can no longer ROLLBACK to the SAVEPOINT.

Summary

In this tutorial, we have understood SQL Transactions with examples and implementations.

Firstly start a transaction and then we can even name a transaction to access afterward. Then save the transaction by using commit.

We also save various states of the transaction by using savepoint, and these are helpful when we need to rollback our transactions.

We use rollbacks when a transaction makes an exit in between or shuts down. Rollback helps us to maintain our data properly. We have covered all the details of SQL transactions in our tutorial.

If you are Happy with DataFlair, do not forget to make us happy with your positive feedback on Google

follow dataflair on YouTube

Leave a Reply

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