Transaction in SQL

In this article I am trying to explain the concept of Transaction in SQL Server.

Transaction is a unit of work that performs against a database. Transaction is a sequence of work accomplished in a logical order, weather in manual fashion by a user or automatically by some program. Transactions allow you to execute several related T-SQL statements and ensure that the database remains consistent if one of the commands fails. 

There are two main purpose of transaction:

1-To provide reliability

2-to provide isolation between programs using database concurrently

A transaction must be follow ACID properties.

ACID Properties:

ACID is acronym of four key properties of a transaction: atomicity, consistency, isolation, and durability. ACID is a set of properties that that guarantee the database transaction are processed reliably.

*Atomicity refers that a transaction must be atomic. That means when a transaction executes either all changes made successfully or no change have been made.

*Consistency refers that from start to end of a transaction data must be in consistent stage. Any command that breaks the integrity of the database results in the entire transaction being rolled back.

* Isolation refers that keep transaction separate from each other until they are finished. That means intermediate changes that are made during a transaction must be isolated from other users and other connections to the database until the transaction is committed.

* A transaction is durable in that once it has been successfully completed; all of the changes it made to the system are permanent. Durability guarantees that the database will keep track of pending changes in such way that the server can recover from abnormal termination.

Transaction Control Commands:

There are following command used in transaction

Commit:

Commit command used to commit the transaction. It saves all changes made by transaction.

Rollback:

Rollback command is used to rollback last committed transaction.

Savepoint:

With the help of savepoint we can rollback a transaction partially.We needs to make savepoint from where we want rollback.

Set Transaction:

Set Transaction is used to initiate a database transaction. This command is used to specify the characteristics of transaction.