Explain transaction in SQL Server Database and ACID (atomicity, consistency, isolation and durability) properties in database.

Transaction:

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.  

A transaction has four key properties that are abbreviated as ACID. ACID is an acronym for "Atomicity", "Consistency", "Isolation" and “Durability".

Suppose if we want to execute two statements together, if error occurs in one of the statement then other will also rolled back. Transaction will be committed if both the statement got executed without any error. Transactions provide an "all-or-nothing" concept that means each work-unit performed in a database must be either completed or have no effect. So if a transaction is successful, all of the data modifications made during the transaction are committed using COMMIT statement and become a permanent part of the database. If a transaction encounters any errors then the changes need to be aborted and all of the data modifications are erased. This process of reversing the changes is called ROLLBACK in SQL Server terminology.

Steps we use in transaction:

·         Begin Transaction

·         Rollback Transaction

·         Commit Transaction

Acid Properties of Transactions:

Each transaction must have four characteristics to ensure consistency and integrity of the data:

Atomicity: In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.

This property ensures that all operations is treated as a single work unit and are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their previous state. So either it is all performed or none of it.

For example: Banking Transaction. If debit from one account and credit to other account, both are successful then transaction will be committed otherwise it will be rolled back.

Consistency:

A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started. Transactions provide an "all-or-nothing" concept that means each work-unit performed in a database must be either completed or have no effect thus will never leave the database in inconsistent or half-finished state.

Isolation: 

A transaction in process and not yet committed must remain isolated from any other transaction. This property enables transactions to operate independently of each other and also transparent to each other.  It keeps transactions separated from each other until they’re finished. Transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will execute first and then the other will execute.

Durability: 

Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state. This property ensures that the result or effect of a committed transaction persists in case of a system failure. So it means that the results of the transaction are permanently stored in the system.