Transaction In SQL Server

A Transaction is a group of commands that change the data store in the database. A transaction might contain many SQL statements but all they Repeatable as a single unit. By using Transaction it will ensure that either all are succeeded or none of them. If any commands in the transaction are failed then in that transaction any data is modified then it will rollback. So in this way transactions maintain the integrity of data in the database.
If you process some data in the database using a transaction then it is not permanent till you committed a transaction till now it is temporary.

Steps:

1. Begin Transaction
2. Process the database commands
3. Check for errors if any error occurred rollback the transaction else commit the transaction.

Suppose you start transaction and somehow you are not committing your transaction or rollback your transaction it means that your transaction is in progress.
In that time frame if any other user trying to access that on that table where a transaction is ongoing it is not getting that data because the default behavior of the SQL server is read committed isolation level.

But if you want to see the uncommitted data then you need to fire below statement
Set transaction Isolation level read uncommitted.

Transaction ACID Test:

The successful transaction must pass the ACID test.
A) Atomic:
Atomic means transaction either completed successfully or they can rollback. In any case, it not half done and a half left. Means if our transaction contains more than one statement then all will succeed or none of them succeed. If anyone fails then data modified by another statement is rollback.

B) Consistent:
Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

C) Isolation:
It means that the transaction must perform its operation without affecting the other concurrent transaction. Means transaction A, transaction B work in isolation does not affect each other. Means if Transaction A updating some table and other transactions trying to select rows from that table he is not able to see that updated records because by default SQL server use read committed ISOlation level so on transaction B there is no effect of Transaction A.
Transaction achieved isolation by using a locking mechanism.

D) Durable:
Transaction made in a database is permanent. It means any power failure or a system error occurred before a set of commands is complete those commands are undone and data is reverted back to its original state when the system began running again.