Transaction in SQL Server
In SQL Server we can implement transaction to ensure data integrity. In a multiuser situation, may be multiple transaction try to accessing the same resource at the same time. To prevent error that can rise due to transaction accessing same resource, we can use locks. Locks provide us a mechanism to secure a resource until one transaction is executed so that only one transaction can work on a database resource at a time. Transactions provide an “all-or-nothing” proposition stating that work units performed in a database must be completed in their entirety or take no effect. Further, transactions must be isolated from other transactions, results must conform to existing constraints in the database and transactions that complete successfully must be committed to durable storage.
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?
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.
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 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 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
The begin transaction statement marks the start of a transaction. The syntax of the Begin transaction is:
Begin Tran| Transaction [trnsaction_name]
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.
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.
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.
Begin Transaction Tran1
update CourseDetail set Course='MCA' where Id='S002'
update StudentDetail set City='Allahabad' where Id='S002'
Commit Transaction Tr1
Rollback Transaction Tran1
In the above screen shot we can see all both database table record has updated of particular Id.