Introduction
This article is a beginner's tutorial for understanding what are transactions and how can transactions
be implemented using .Net framework and ADO.NET
for any ASP.NET
web application or any other database driven
application.
Background
Transaction word, when used in normal context mean exchange of goods, products or money in return for
something of equal value. What makes this exchange a transaction is the fact that the total amount of goods
or money remains the same i.e. it doesn't get increased or decreased and the reliability i.e. if one party is
giving something then other party will receive that much quantity (no less, no more).
Following the same lines, when we talk about transactions in database operations, When we perform some database
operations in such a way that either all the database operations are successful or all of them fail. This would result
in the amount information being same once the transaction is complete or it fails.
To illustrate the above process, let say I have two account holders, one person is trying to transfer some
money to other person. From the database perspective this operation consist of two sub-operations i.e.
- Debiting the first account by specified amount.
- Secondly, crediting the second account with required amount.
Now from a technical perspective, if the first operation is successful but second one fails the result would
be that the first persons account will be debited but second one will not be credited i.e. we loose the amount
of information. The other way round will in fact increase the amount ion second account without even debiting the first amount.
So the bottom-line here is that we need either both of them to be successful to both of them should fail.
Success of any one operation will result in inconsistent results and thus even if one operation fails we
need to rollback what we did in the other operation. This is precisely where transaction are useful.
Let us now look at some technical details associated with transactions and transactions in .Net framework.
We will then see how we can implement transactions in .NET.
Using the code
By definition a transaction must be Atomic, Consistent, Isolated and Durable. What does we mean by all these
terms
Properties of Transaction
-
Atomic: Atomic means that all the statements (
SQL
statement or operations) that are a part of the
transaction should work as atomic operation i.e. either all are successful or all should fail.
-
Consistent: This means that in case the atomic transaction success, the database should be in a state
that reflect changes. If the transaction fails then database should be exactly like it was when the
transaction started.
-
Isolated: If more than one transactions are in process then each of these transactions should work
independently and should not effect the other transactions.
-
Durable: Durability means that once the transaction is committed, the changes should be permanent i.e. these
changes will get saved in database and should persist no matter what(like power failure or something).
Description of Sample code
Now to illustrate how can transactions be implemented, We will work on a small application that
contains a single table database. This table contains the account id and the amount present in the
account. The application will facilitate transfer of amount of one account to another. Since there
are two operations involved we will see how we can use transactions to perform these operations.
The sample DB table looks like:
The UI will look like:
And we will implement three versions of this page to see three different ways. We will see how can handle
transaction at database level using SQL
transactions, how to implement transactions using ADO.NET
transaction
object and finally we will see how to implement transactions using TransactionScope
object.
Note: The code is written to elaborate the transaction functionality only, it is not as per the coding
standards i.e it is vulnerable to SQL
injection. It should not be taken as code that could go in production.
It is just the sample code and has a lot of scope for improvement.
Creating and Implementing Transactions in SQL
The transactions can also be handled at SQL
level. The SQL
construct for handling the transactions is like
follows:
BEGIN TRY
BEGIN TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
So if something goes wrong, the SQL
itself will take care of rolling back the transaction.
So for our sample application we can write the following code to get this code to work with
SQL
transactions in place.
private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
SqlConnection con = null;
bool result = false;
string updateCommand = @"BEGIN TRY
BEGIN TRANSACTION
update Account set Amount = Amount - {0} where ID = {1}
update Account set Amount = Amount + {2} where ID = {3}
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH";
try
{
con = new SqlConnection(CONNECTION_STRING);
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(updateCommand,
amount, debitAccountID,
amount, creditAccountID);
result = cmd.ExecuteNonQuery() == 2;
}
}
catch
{
}
finally
{
con.Close();
}
}
Note: This sample code just shows the code snippet handling the transaction and database operation,
to get the full understanding please refer to the sample code.
Creating and Implementing Transactions using ADO.NET DbTransaction Object
Now having the transactions in SQL
level is perfect solution if all the operations are being done in
one place. I can create a stored procedure which will take care of all the transactions But what if
all the operations are taking place in separate classes or even separate assemblies. If that is the
case need to use the ADO.NET
transaction to handle transactions in code.
The other way to handle the transaction is from the code itself using ADO.NET
DbTransaction
object.
To do that following steps should be taken:
- Create the Connection.
- Create the Transaction.
- Create the commands for all the operations that are within transaction.
- Open the connection.
- Begin Transaction.
- Associate all the commands with the above transaction object.
- Execute commands.
- Check for the commands status separately.
- If any of the command fails, Roll back the transaction.
- If all Commands are successful, Commit the transaction.
To illustrate the above mentioned process, lets write some code to perform the transaction in our sample
application.
private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
SqlTransaction transaction = null;
SqlConnection con = null;
bool debitResult = false;
bool creditResult = false;
try
{
con = new SqlConnection(CONNECTION_STRING);
con.Open();
transaction = con.BeginTransaction();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(
"update Account set Amount = Amount - {0} where ID = {1}",
amount, debitAccountID);
cmd.Transaction = transaction;
debitResult = cmd.ExecuteNonQuery() == 1;
}
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(
"update Account set Amount = Amount + {0} where ID = {1}",
amount, creditAccountID);
cmd.Transaction = transaction;
creditResult = cmd.ExecuteNonQuery() == 1;
}
if (debitResult && creditResult)
{
transaction.Commit();
}
}
catch
{
transaction.Rollback();
}
finally
{
con.Close();
}
}
This code block makes sure that either all the operations associated with the transactions succeed or
none of them will.
Note: This sample code just shows the code snippet handling the transaction and database operation,
to get the full understanding please refer to the sample code.
Creating and Implementing Transactions using TransactionScope Object
Creating and using transactions using ADO.NET
transaction is fine as long as we do either a commit
or rollback on the transaction object. If we forget to so do this and leave the code then it will cause
some problems.
To solve such problems there is one more way of handling the transaction i.e. using TransactionScope
object.
TransactionScope
if more of a syntactic sugar when dealing with transactions. Also it makes sure that if the
transaction is not committed and the code goes out of scope, the transaction will get rolled back.
To use the TransactionScope
object to handle the transactions following needs to be done.
- Create a
TransactionScope
object within a using block.
- Create the connection inside this
TransactionScope
using block.
- Create all the commands inside this.
- Perform all the operations using Commands.
- If all the operations are successful call the Complete function on
TransactionScope
object.
- If any of the command fail, let the control go out of scope and the transaction will be rolled back.
To illustrate this process let us try to re-implement the functionality in our sample application using
TransactionScope
object.
private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
bool debitResult = false;
bool creditResult = false;
try
{
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(
"update Account set Amount = Amount - {0} where ID = {1}",
amount, debitAccountID);
debitResult = cmd.ExecuteNonQuery() == 1;
}
throw new Exception("Let see...");
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(
"update Account set Amount = Amount + {0} where ID = {1}",
amount, creditAccountID);
creditResult = cmd.ExecuteNonQuery() == 1;
}
if (debitResult && creditResult)
{
ts.Complete();
}
}
}
}
catch
{
}
}
This code block makes sure that either all the operations within the scope of
TransactionScope
will succeed or none of them will.
Note: This sample code just shows the code snippet handling the transaction and database operation,
to get the full understanding please refer to the sample code.
To give more control to the developer, along with the scoped control of TrasanctionScope
, there are
few options that can be associated with the TransactionScope
Required
: If within current scope another TransactionScope
is already instantiated, this
TransactionScope
object will join that one.
RequiresNew
: Even if within current scope another TransactionScope is already instantiated, this
TransactionScope
object will create a new transaction that will work within its scope.
Supress
: Even if within current scope another TransactionScope
is already instantiated, this
TransactionScope
object now keep all the operations within its scope out of the existing transaction.
These options can be passed in the Constructor of the TransactionScope
as:
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
}
A Note on Distributed Transactions
If a transaction span across multiple processes then it is a Distributed transaction i.e. if I need to
perform an operation in SqlServer
and other in Oracle ans I have a transaction associated with it then it
will be a distributed transaction.
The reason this is important is because of two things. The Isolation property of transaction will be
ensured by the LTM
(Lightweight Transaction Manager) if it is not a distributed transaction. But of it is
a distributed transaction then Distributed Transaction Controller(DTC
) will take over the control.
When we use TransactionScope
class, if the transaction starts with one process LTM
will keep it
going but if another operation in some other process is done the DTC
takes over and the transaction
will then be automatically promoted to distributed transaction. So TransactionScope
class can be used to
create promotable transactions with ease.
But even for non distributed transactions,
transactionScope object should only be used with SqlServer 2005
or later products because
the earlier SqlServer
products were not aware of this LTM
and DTC
. so for the earlier products
ADO.NET
DbTransaction
object is the perfect way to handle the transactions from code.
Point of interest
In this article we tried to see what are transactions and how can transactions be handled in .NET.
We looked at these concepts from a very beginner's perspective and from the topics perspective, it
is just the tip of the iceberg. We have not discussed anything about the isolation levels and the note on distributed transactions was very basic(since it itself is a vast topic). But we did discuss about various ways of handling transactions in .NET.
History
-
05 January 2013: First version.