Transactions in sql server

Why transactions.

We generally works with multiple tables in one operation. May be we want to insert data to order header and order detail table in one operation. Then what happens if only part of the operation succeeds. Some statements can be failed.

So we are with lot of possible problems inside our database when it comes to data integrity . This is where we need transactions.

Transactions will allow us to control the entirety of an operation and ensure that everything’s going to succeed or everything is going to get rolled back should something fail.

Transaction must meet ACID.

ACID is comprised with four different components.

A -> Atomic

Entire operation must succeed or the entire operation must fail, and it must do so as a single unit

C -> Consistent

Once the operation is complete, database must be left in a consistent or in a valid state

I -> Isolated

Every transaction must also be isolated, simply meaning that somebody else’s operation at the same time that I’m working on my data, is not allowed to impact my operation.

D -> Durable

The database should be durable enough to hold all its latest updates even if the system fails or restarts

There are three main commands in managing transaction manually.

  1. BEGIN TRANSACTION

This will mark the start of the transaction

2.COMMIT TRANSACTION

We are going to execute this once we know everything is works fine.

3.ROLLBACK TRANSACTION

If some thing went wrong we are going to execute this one. We got some errors and we want to Rollback the transaction.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s