AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1313&pId=-1
Understanding Transaction in ADO.NET
page
by SANJIT SIL
Feedback
Average Rating: 
Views (Total / Last 10 Days): 48446/ 102

Introduction

A transaction is a set of operations that must either succeed or fail as a unit. The goal of a transaction is to ensure that data is always in a valid, consistent state. For example, consider a transaction that transfers $1000 from account A to account B; clearly there are two transactions. 1) It should deduct $1000 from account A and 2) it should add $1000 to account B. Suppose that a transaction completes step 1, but fails in step 2 because of some errors. This leads to inconsistent data because the total amount of money is no longer accurate. A full $1000 has gone missing. Transactions help avoid these types of problems by ensuring that changes are committed to a database only if all the steps are successful. So in this example, if step 2 fails, then the changes made by step 1 will not be committed to the database. This ensures that the system stays in any of two valid states --- the initial state (with no money transferred) and the final state (with money debited from the account and credited to another). The sample code snippets have been written in C#.

ACID Properties

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

Atomicity

A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.

Consistency

A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Isolation

Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time.  Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.

Durability

If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

When To Use Transactions

We should use transaction when several operations must succeed or fail as a unit.

The following are some frequent scenarios when we must use transactions:

1.  when multiple rows must be inserted or deleted or updated as a single unit

2. whenever a change to one table requires other tables to be updated

3. when modification of data is required in two or more databases, concurrently

4. where data is modified in databases in a different server

How to Code Transactions

We can use two type of basic transaction type in an ASP.NET Application.

Stored Procedure Transaction

It is the best practice to use transaction in stored procedure because all actions can be executed in the database side, which ensure security, quick execution and overall performance boost. The following three T-SQL statements control transaction in the SQL server.

Begin Transaction: This ensures the start of a transaction.

Commit Transaction: This ensures the successful end of a transaction. It passes signals to the database to save the work.

Rollback Transaction: This denotes that a transaction has not been successful and passes signals to the database to roll back to the state it was in before starting of the transaction.

It should be noted that there is no End Transaction statement. Transactions end on (explicit or implicit) commits and rollbacks. We can end transaction with the commit or rollback. If we do not do so, the transaction will be automatically rolled back. In the following Listing it is shown how we can use Begin Transaction, Commit Transaction and Rollback Transaction.

Listing 1

CREATE Procedure SP_TransferAmount
(
@Amount money,
@B_CodeA int,
@B_CodeB int
)
AS
@Amount < 1 
GOTO PROBLEM
Declare  @rows int
SET @rows = (SELECT COUNT(*) FROM Accounts Where A_ID=@B_CodeA)
if(@rows<1)
GOTO PROBLEM
SET @rows = (SELECT COUNT(*) FROM Accounts Where A_ID=@B_CodeB)
if(@rows<1)
GOTO PROBLEM
BEGIN TRANSACTION
UPDATE Accounts Set Balance=Balance+@Amount Where A_ID=@B_CodeA
IF(@@ERROR>0)
GOTO PROBLEM
UPDATE Accounts SET Balance = Balance - @Amount Where A_ID=@B_CodeB
IF(@@ERROR>0)
GOTO PROBLEM
Commit
Return
PROBLEM:
ROLLBACK
RAISERROR ('COULD NOT UPDATE', 16,1)
GO

 

It should be noted that, when using the @@ERROR value we must be careful to check it immediately after each operation. That is because @@ERROR is reset to 0 when a successful SQL statement is completed. As a result, if the 1st update fails and 2nd update is successful, @@ERROR returns to 0. It is, therefore, too late to check at this point. In case of SQL Server 2005, we can use the try catch structure like in our C# coding (See Listing II). The benefit of this approach is that execution passes to subsequent error handling block whenever any error occurs.

Listing 2

@CREATE Procedure SPTransferAmount
(@Amount Money,
@B_CodeA int,
@B_CodeB int
)                              
AS
BEGIN TRY
BEGIN TRANSACTION
UPDATE Accounts Set Balance=Balance+@Amount Where A_ID=@B_CodeA
UPDATE Accounts SET Balance = Balance - @Amount Where A_ID=@B_CodeB
Commit
END TRY
BEGIN CATCH
IF ((@@ TRANCOUNT>0)
ROLLBACK
DECLARE @Errmsg nvarchar (4000), @ErrSeverity int
SELECT @Errmsg=ERROR_MESSAGE (), @Err Severity=ERROR_SEVERITY ()
RAISEERROR (@Errmsg, @ErrSeverity, 1)
END CATCH

Coding Transaction in ADO.NET

Most ADO.NET data providers include support for database transactions. Transactions are started through the connection object by calling the BeginTransaction () method. This method returns a provider specific transaction objects that is used to manage the transaction. All transaction classes implement the IdbTransaction interface. Examples include SQLTransaction, OLEDBTransaction, OracleTransaction, etc. Commands are associated with a specific transaction for a specific connection. The following are the steps to implement transaction processing in ADO.NET (See Code Listing 3).

Step 1: Create an instance of connection object passing connecting string of the database.

Step 2: Create an instance of SqlCommand object with the necessary parameters.

Step 3: Open the database connection using the connection instance.

Step 4: Call the BeginTransaction method of the Connection object to make the beginning of the transaction.

Step 5: Execute the SQL statements using the instance of Command object.

Step 6: Call the Commit method of the Transaction object to complete the
transaction or call the Rollback method to cancel the transaction.

Step 7: Close the connection to the database by closing connection object instance.

Listing 3

string cString =
  WebConfigurationManager.ConnectionString[testDB].ConnectionString;
SqlConnection con = new sqlConnection(cString)SqlCommand cmd1 = new sqlCommand
  (Insert into Emp(E_Code, E_Name)values(1, Employee1));
SqlCommand cmd2 = new sqlCommand(Insert into Emp_Details(E_Code, Sal)values(1,
  10000));
SqlTransaction tran = null;
try
{
  con.Open()tran = con.BeginTransaction()cmd1.Transaction = tran;
  cmd2.Transaction = tran;
  cmd1.ExecuteNonQuery();
  cmd2.ExecuteNonQuery();
  tran.Commit();
  catch (Exception ex)
  {
    tran.Rollback();
    throw ex;
  }
  finally
  {
    con.Close();
  }

In the above example code, instead of taking two separate SqlCommand objects, we can take a single command object and perform the same operation by just setting CommandText property, which is specified in the following code Listing.

Listing 4

SqlCommand cmd = new SqlCommand (); 
 cmd.Transaction = tran; 
  cmd.CommandText =(Insert into Emp(E_Code, E_Name)values(1,Employee1));
  cmd.ExecuteNonQuery(); 
  cmd.CommandText =(Insert into Emp_Details (E_Code, Sal) values (1, 10000));
  cmd.ExecuteNonQuery();

SavePoints

Whenever we Rollback a transaction, it rollbacks all the operations performed from the starting of transaction. But sometimes we need to rollback any part of an ongoing transaction and using Savepoint we can do the same. Savepoints are just like bookmarks within a transaction. The statements given after a Savepoint can be committed or rolled back. A Savepoint has to be given a name. We can set the Savepoint using the Transaction.Save () method. There can be more than one Savepoint within a transaction.

Listing 5

string cString =
  WebConfigurationManager.ConnectionString[testDB].ConnectionString;
SqlConnection con = new sqlConnection(cString)SqlCommand cmd = new SqlCommand();
SqlTransaction tran = null;
try
{
  con.Open()cmd.Transaction = tran;
  cmd.CommandText = (Insert into Emp(E_Code, E_Name)values(1, Employee1));
  cmd.ExecuteNonQuery();
  tran.Save("1stTransaction");
  cmd.CommandText = (Insert into Emp_Details(E_Code, Sal)values(1, 10000));
  cmd.ExecuteNonQuery();
  tran.Rollback("1stTransaction ");
  catch (Exception ex)
  {
    tran.Rollback();
    throw ex;
  }
  finally
  {
    tran.Commit();
    con.Close();
  }

Once we rollback to a SavePoint, all the transactions defined after the Savepoint are lost. In the above example Insert into Emp_Details will be lost.

Distributed Transaction

The .NET Framework 2.0 includes the System.Transactions namespace, which provides support for distributed transactions and defines the TransactionScope class, which enables us to create and manage distributed transactions.  We can implement transactions for multiple database connections using TransactionScope.

Listing 6

using(TransactionScope tranScope = new TransactionScope())
{
  string cString =
    WebConfigurationManager.ConnectionString[testDB].ConnectionString;
  string cString1 =
    WebConfigurationManager.ConnectionString[testDB1].ConnectionString;
 
  using(SqlConnection con = new SqlConnection(cString))
  {
    SqlCommand cmd = codesDatabaseConnection.CreateCommand();
    cmd.CommandText =
      "Insert Into Emp (Emp_Code, Emp_Name) values (1,'Employee1')";
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
  }
  using(SqlConnection con1 = new SqlConnection(cString1))
  {
    SqlCommand cmd1 = con1.CreateCommand();
    cmd1.CommandText = "Insert into Emp_Detailas(Emp_ID,Sal) values (1, 8000)";
    con1.Open();
    cmd1.ExecuteNonQuery();
    con1.Close();
  }
  tranScope.Complete();
}

If all the update operations succeed in a transaction scope, we should call the complete method on the TransactionScope object to indicate that the transaction completed successfully and, thus, the transaction manager commits the transaction. 

Isolation Levels

These determine how a transaction is isolated from other concurrent transactions. The choice of which to use depends on what our program does and what kind of performance it seeks to achieve. The details have been described in my article “Understanding Isolation Levels in Transaction.”

References
Conclusion

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be cancelled or rolled back, then all of the data modifications are erased. As a thumb rule, use a transaction only when the application requires one. For example, if we are simply selecting records from a database or firing a single query, we will not need a transaction. In general, a transaction never requires for single statement commands such as Insert, Update or Delete. On the other hand, for executing batch/multiple statements we need transaction to preserve the database integrity and data consistency. However, it should be noted that transactions hold locks and may harm the overall scalability of the application; they should be as short as it is possible. It should be avoided to return data using a select query in the middle of transaction. Ideally, it should be returned the data before the transaction starts. If we do receive records, fetch only the rows that are required. That means not to lock too many resources to keep performance as good as possible. Whenever possible, write transactions within stored procedures instead of using an ADO.NET transaction.



©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-11-29 7:19:22 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search