SQL - Transactions

Sometimes we come across a situation where we  need to execute two or more SQL commands in such a way that - if any one of the statements fails, then no other statement will be able to change the database. For that we can use concept TRANSACTION

There are three importance commands to manage a transaction. BEGIN TRANSACTION will begin a transaction, COMMIT TRANSACTION will commit the transaction to the database, and ROLLBACK TRANSACTION will roll the transaction back.

Within .NET, transactions are managed with the System.Data.SqlClient.SqlTransaction class.A transaction exists over a SqlConnection object  and thus all the SqlCommand objects you create using that connection. Let's look at a quick example:

using System.Data.SqlClient;

SqlConnection myConnection = new SqlConnection("");

        myConnection.Open();

// Start a local transaction

SqlTransaction myTrans = myConnection.BeginTransaction();

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.Transaction = myTrans;

try

{

myCommand.CommandText = "Insert into user_reg (your column name)VALUES (your values goes here)";

myCommand.ExecuteNonQuery();

myCommand.CommandText = "delete from user_reg where ID=101";//

// Attempt to commit the transaction.

myCommand.ExecuteNonQuery();

myTrans.Commit();

Response.Write("Both records are written to database.");

}

catch (Exception ep)

{

// Attempt to roll back the transaction.

myTrans.Rollback();

Response.Write(ep.ToString());

Response.Write("Neither record was written to database.");

}

finally

{

myConnection.Close();

}

As you can see from this example, we first open a connection to the SQL database. We then call the BeginTransaction method on the connection, keeping a reference to the object that it returns. At this point, the connection is bound to the SqlTransaction object that was returned. This means that any SqlCommand executed on that connection will be within the transaction.

There are essentially, two operations you can use on the SqlTransaction object. Rollback will cancel your transaction, undoing all the changes that have been made. Commit will cause the transaction to be written to the database permanently. Either case will end the transaction.

Transactions are useful for several other things. First, they provide a way to rollback a group of SQL statements if a single one should.


Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

Consistency − ensures that the database properly changes states upon a successfully committed transaction.

Isolation − enables transactions to operate independently of and transparent to each other.

Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.


Transaction Control

The following commands are used to control transactions.

COMMIT − to save the changes.

ROLLBACK − to roll back the changes.

SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.

SET TRANSACTION − Places a name on a transaction.