A transaction can define as a sequence of operations performed together as a single logical unit of work.  A single unit of work must possess these properties of database transactions are often referred to by the acronym ACID.

What is ACID?

•          Atomicity
•          Consistency
•          Isolation
•          Durability

Atomicity

This state’s that either all operation performs or none of them performed.  Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.

Consistency

This states that only valid data will be written to the database. If a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules. All rules in a relational database must be applied to the modifications in a transaction to maintain complete data integrity.

Isolation

Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. This states that any data modification made by concurrent transactions must be isolated from the modification made by other concurrent transactions. Note isolation property does not ensure that which transaction will execute first.

Durability

Durability ensures through the use of database backups and transaction log that facilitate restoration of committed transaction, any change in data due to a completed transaction persists even in the event of a system failure

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

•    Begin transaction
•    Rollback transaction
•    Commit transaction

Begin transaction

The begin transaction statement marks the start of a transaction. The syntax of the Begin transaction is:

BeginTran| Transaction [trnsaction_name]

Rollback transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. The Rollback transaction statement rolls back an explicit or implicit transaction to the beginning of the transaction, or to save-point within a transaction.

Rollback [trnsaction_name]

Commit transaction

The Commit transaction statement marks the end of an explicit transaction. In SQL Server terminology, we say that these changes are committed to the database.

Commit[trnsaction_name]

For example I have a situation I want to update coursedetail table record as well as studentdetail table record of particular Id and in this example trying to understand how we can implement transaction in SQL Server.

Query

BeginTransaction Tran1
Begintry
update CourseDetail set Course='MCA'where Id='S002'
--First task--
update StudentDetail set City='Allahabad'where Id='S002'
--Second task
CommitTransaction Tr1
Endtry
BeginCatch
RollbackTransaction Tran1
Endcatch

             CourseDetail                            StudentDetail

In the above screen shot we can see all both database table record has updated of particular Id.