Transaction in sql server

Transaction is a group of command that changes the data store in a database. A transaction treated as a single unit. In other word we can say that a transaction is a set on T-SQL statement that is executed together as like a single T-SQL statement.

Transaction ensure that either all command successes or none of them. If one of the commands fails all the command fails and all the data modified in the database are rollback. In success of the commands it made the changes.

Transaction maintains the integrity of the data in database.

 Property of transaction

There are 4 property of Transaction (Also known as ACID properties)

1.       Atomicity – All or Nothing transaction

2.       Consistency – Guarantees committed transaction state

3.       Isolation – Transactions are independents

4.       Durability – Committed data never lost

We will see ACID properties in details in next post.

Transaction Control

Below is the transaction control command

1.       Commit – To save the changes

2.       Rollback – To Rollback the changes

3.       Save point – Creates points within groups of transactions in which to ROLLBACK

4.       Set transaction – Place the name of the transaction

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only.

Syntax of Transaction

Begin transaction trans --DML command Commit --or Rollback transaction trans

Let’s see the example

 I have a table and want to update the records. Here I am using transaction if my sql command is executed successfully then the transaction will be committed else it will be the rollback.

Now I am using the Transaction

Begin try Begin transaction trans update Emp set name='Ramesh' where ID=2 Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

  

The update statement executed successfully so the transaction is committed.

Now I am changing the update statement and it is incorrect update statement which is cause of error.

ID is Int but I am passing the string, it is cause of failure.

Begin try Begin transaction trans update Emp set name='Ramesh kumar' where ID='Raj' Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

  

I mean record is not updated.