Home > DeveloperSection > Articles > Transaction in SQL Server

Transaction in SQL Server


Database Database 
Ratings:
0 Comment(s)
 4770  View(s)
Rate this:

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?

·         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:

Begin Tran| 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

Begin Transaction Tran1

Begin try

update CourseDetail set Course='MCA' where Id='S002'

--First task--

update StudentDetail set City='Allahabad' where Id='S002'

--Second task

Commit Transaction Tr1

End try

Begin Catch

Rollback Transaction Tran1

End catch

 

             CourseDetail                            StudentDetail

Transaction in SQL Server                         Transaction in SQL Server

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


Don't want to miss updates? Please click the below button!

Follow MindStick