I think you will find this article useful. Thanks for reading.
1. Introduction
TransactionScope is a very special and important class in the .NET Framework. Supporting transactions from a code block is the main responsibility of this class. We often use this class for managing local as well as distributed transactions from our code. Use of TransactionScope is very simple and straightforward. It is very reliable and easy to use. For this reason it is very popular among .NET developers. In this article, I explain transaction related theory with code sample, and show various scenarios where we can use TransactionScope with various options for managing real life transactions.
2. Background
Transaction management is very, very important to any business application. Each and every large scale development framework provides a component for managing transactions. .NET Framework is a large development framework and it also provides its own transaction management component. Before the launch of .NET Framework 2.0 we used SqlTransaction to manage transactions. From version 2 .NET Framework has theTransactionScope class. This class is available in the System.Transactions assembly. This class provides a transactional framework with the help of which any .NET developer can write transactional code without knowing much details. For this reason it is very popular among .NET developers and they widely use it for managing transactions. But the story is not finished yet. I will say the story has only just started.
In the real world any one you will find exceptional scenarios, exceptional issues where only a knowledge of how to use TransactionScope is not good enough. To resolve transactional issues like deadlocks, timeouts, etc., you must know each and every concept directly/indirectly related to a transaction. There is no alternative. So the concepts of a transaction and its related components need to be clear.
3. How to Use TransactionScope
Use of TransactionScope in a .NET application is very, very simple. Any one can use it by following these steps:
- Add a System.Transactions assembly reference to the project.
- Create a transactional scope/area with the help of the
TransactionScope class starting with a usingstatement.
- Writing code which needs to have transactional support.
- Execute the
TransactionScope.Complete method to commit and finish a transaction.
Really, as simple as that. But in a real life project only that knowledge is not sufficient. You need more transaction related knowledge, otherwise you can not handle transaction related issues. So first of all, we should be clear about the transactional concept.
4. Transaction
What is a transaction? You can find the definition of a transaction from various sources like Wikipedia, other websites, books, articles, blogs. In a very short, we can say, a series/number of works treated as a whole, either completed fully or not at all.
Example: Transfer money from Bank Account-A to Account-B
Series of (actually two) tasks/processes:
- Withdraw amount from Account-A
- Deposit that amount to Account-B
We understand that transfer of money (from Account-A to Account-B) consists of two individual processes. Transferring money will only be accurate and successful if both the processes are individually successful. If that is not happening, suppose process-1 succeeds but process-2 fails, then the money will be deducted from Account-A but not deposited to Account-B. If that happens, it will be very bad and no one will accept it.
5. Business Transaction
Business transactions are interactions between Customer/Supplier/StackHolders and other parties who are involved in doing business. In this article I am not going to present anything regarding business transactions.
6. Database Transaction
In software development, when we say transaction by default we guess that it is a database transaction. In a database transaction we can say, a series of data manipulation statements (insert/update/delete) execute as a whole. All statements either successfully execute, or will fail each and every statement, so that the database is in consistent mode. Database transactions actually represent a database state change in an accurate way.
7. Local Transaction
A transaction where a series of data manipulation statements execute as a whole on a single data source/database. It is actually a single phase transaction handled by a database directly. To manage local transactions, System.Transactions has a Lightweight Transaction Manager (LTM). It acts like a gateway. All transactions are started by System.Transactions are handled directly by this component. If it finds the transaction nature is distributed based on some predefined rules it has a fallback transaction to the MSDTC distributed transaction.
8. Distributed Transaction
A transaction which works with multiple data sources is called a distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer can not do anything here.
9. Distributed Transaction System Architecture
We know that in a distributed transaction, several sites are involved. Each site has two components:
- Transaction Manager
- Transaction Coordinator
1. Transaction Manager: Maintains a log and uses that log if recovery is needed. It controls the whole transaction by initiating and completing, and managing the durability and atomicity of a transaction. It also coordinates transactions across one or more resources. There are two types of transaction managers.
- Local Transaction Manager: Coordinates transaction over a single resource only.
- Gloabl Transaction Manager: Coordinates transaction over multiple resources.
2. Transaction Coordinator: Starting the execution of transactions that originate at the site. Distributes subtransactions at appropriate sites so that they can execute in those sites. Coordinates each transaction of each site. As a result the transaction is committed or rolled back to all sites.
10. Connection Transaction
Transaction which is tied directly with a database connection (SqlConnection) is called Connection Transaction.SqlTransaction (IDbTransaction) is an example of a connection transaction. In .NET Framework 1.0/1.1 we use SqlTransaction.
Hide Copy Code
string connString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (IDbTransaction tran = conn.BeginTransaction())
{
try
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Data(Code) VALUES('A-100');";
cmd.Transaction = tran as SqlTransaction;
cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch(Exception ex)
{
tran.Rollback();
throw;
}
}
}
11. Ambient Transaction
A transaction which automatically identifies a code block that needs to support a transaction without explicitly mentioning any transaction related things. An ambient transaction is not tied just to a database, any transaction aware provider can be used. TransactionScope implements an ambient transaction. If you see the use of TransactionScope, you will not find transaction related anything sent to any method or setting any property. A code block is automatically attached with the transaction if that code is in any TransactionScope. A WCF transaction is another example of a transaction aware provider. Any one can write a transaction aware provider like the WCF implementation.
12. Transaction Properties
There are four important properties for a transaction. We call them ACID properties. They are:
- A-Atomic
- C-Consistent
- I-Isolation
- D-Durable
- Atomic: If all parts of the transaction individually succeed then data will be committed and the database will be changed. If any single part of a transaction fails then all parts of the transaction will fail and the database will remain unchanged. Part of the transaction might fail for various reasons like business rule violation, power failure, system crash, hardware failure, etc.
- Consistent: Transaction will change the database from one valid state to another valid state following various database rules like various data integrity constraints (primary/unique key, check/not null constraint, referential integrity with valid reference, cascading rules ) etc.
- Isolation: One transaction will be hidden from another transaction. In another way we can say, one a transaction will not affect an other transaction if both work concurrently.
- Durability: After a transaction is successfully completed (committed to the database), changed data will not be lost in any situation like system failure, database crash, hardware failure, power failure etc.
13. Transaction Isolation Level
Now I will start explaining a very important thing directly related to transactions, and that is transaction isolation level. Why is it so important? First of all, I previously explained that isolation is an important transaction property. It describes each transaction is isolated from another and do not affect other concurrently executed transactions. How does a transaction management system achieve that important feature?
A Transaction Management System introduces a locking mechanism. With the help of this mechanism one transaction is isolated from another. The locking policy behaves differently based on the Isolation level set for each transaction. There are four very important isolation levels in .NET transaction scope. These are:
- Serializable
- Repeatable Read
- Read Committed
- Read UnComitted
Before I start explaining isolation levels, I need to explain data reading mechanizm inside a transaction. This data reading mechanism is very important to understand isolation levels properly.
- Dirty Read: One transaction reads changed data of anohter tranaction but that data is still not committed. You may take decission/action based on that data. A problem will arise when data is rolled-back later. If rollback happens then your decision/action will be wrong and it produces a bug in your application.
- Non Repeatable Read: A transaction reads the same data from same table multiple times. A problem will arise when for each read, data is different.
- Phantom Read: Suppose a transaction will read a table first and it finds 100 rows. A problem will arise when the same tranaction goes for another read and it finds 101 rows. The extra row is called a phantom row.
Now I will start explaining in short the important isolation levels:
- Serializable: Highest level of isolation. It locks data exclusively when read and write occurs. It acquires range locks so that phantom rows are not created.
- Repeatable Read: Second highest level of isolation. Same as serializable except it does not acquire range locks so phantom rows may be created.
- Read Committed: It allow shared locks and read only committed data. That means never read changed data that are in the middle of any transaction.
- Read Un-Committed: It is the lowest level of Isolation. It allows dirty read.
Now I will start explaining TransactionScope and its usage pattern:
14. TranactionScope Default Properties
It is very important to know about the default properties of the TransactionScope object. Why? Because many times we create and use this object without configuring anything.
Three very important properties are:
IsolationLevelTimeoutTransactionScopeOptions
We create and use TransactionScope as follows:
Hide Copy Code
using (var scope = new TransactionScope())
{
scope.Complete();
}
Here the TransactionScope object is created with the default constructor. We did not define any value forIsolationLevel, Timeout, and TransactionScopeOptions . So it gets default values for all three properties. So now we need to know what the default property values of these properties.
| Property | Default Value | Available Options |
|---|
IsolationLevel | Serializable | Serializable, Read Committed, Read Un Committed, Repeatable Read |
Timeout | 1 Minute | Maximum 10 Minutes |
TransactionScopeOption | Required | Required, Required New, Suppress |
- Isolation Level: It defines the locking mechanism and policy to read data inside another transaction.
- Timeout: How much time object will wait for a transaction to be completed. Never confuse it with the
SqlCommand Timeout property. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation (select/insert/update/delete) to be completed.
- TransactionScopeOption: It is an enumeration. There are three options available in this enumeration:
| No | Option | Description |
|---|
| 1 | Required | It is default value for TransactionScope. If any already exists any transaction then it will join with that transaciton otherwise create new one. |
| 2 | RequiredNew | When select this option a new transaction is always created. This transaction is independent with its outer transaction. |
| 3 | Suppress | When select this option, no transaction will be created. Even if it already |
How to know the default values of these properties?
The System.Transactions assembly has two classes:
- Transaction
- TransactionManager
These classes will provide default values. Inside TransactionScope, if you run the following code, you will know the default values:
Hide Copy Code
using (var scope = new System.Transactions.TransactionScope())
{
IsolationLevel isolationLevel = Transaction.Current.IsolationLevel;
TimeSpan defaultTimeout = TransactionManager.DefaultTimeout;
TimeSpan maximumTimeout = TransactionManager.MaximumTimeout;
}
Is it possible to override the default property values?
Yes, you can. Suppose you want the default value to be 30 seconds and the maximum timeout value to be 20 minutes. If that is the requirement then you can do it using your web config.
Hide Copy Code
<system.transactions>
<defaultSettings timeout="30"/>
<machineSettings maxTimeout="1200"/>
</system.transactions>
For the machineSettings value, you need to update your machine.config in your server.
Hide Copy Code
<section name="machineSettings" type="System.Transactions.Configuration.MachineSettingsSection,
System.Transactions,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089,
Custom=null"allowdefinition="MachineOnly"allowexedefinition="MachineToApplication" />
15. Transaction Isolation Level Selection
You need to have a proper knowledge when you use isolation levels. The following table will give you a very basic idea so that you can understand the basics and select the appropriate isolation level for your transaction scope.
| Isolation Level | Suggestion |
|---|
| Serializable | It locks data exclusively at the time of read/write operations. For that reason, many times it may create a deadlock, and as a result you may get a timeout exception. You can use this isolation level for a highly secured transactional application like a financial application. |
| Repeatable Read | Same as Serializable except allows phantom rows. May use in a financial application or a heavily transactional application but need to know where phantom row creational scenarios are not there. |
| Read Committed | Most of the applications you can use it. SQL Server default isolation level is this. |
| Read Un-Committed | Applications with these have no need to support concurrent transactions. |
Now I will explain with scenarios, how we can use TransactionScope:
16. Requirement-1
Create a transaction in which isolation level will be read committed and transaction timeout will be 5 minutes.
Implementation:
Hide Copy Code
var option = new TransactionOptions();
option.IsolationLevel = IsolationLevel.ReadCommitted;
option.Timeout = TimeSpan.FromMinutes(5);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
ExcuteSQL("CREATE TABLE MyNewTable(Id int);");
scope.Complete();
}
First off, create TransactionOptions and set ReadCommitted and 5 minutes to its IsolationLevel andTimeout property, respectively.
Second, create a transactional block by creating a TransactionScope object with its parameterized constructor. In this constructor you will pass a TransactionOptions object which you created early and theTransactionScopeOption.Required value.
One important note, many times we are confused when using a DDL statement (Data Definition Language) in a transaction and a question arises, will it support DDL transaction? The answer is yes. You can use a DDL statement like create/alter/ drop statement in the transaction. You can even use a Truncate statement inside the transaction.
17. Requirement-2
We need to create a transaction where a database operation will be in my local database and another will be in a remote database.
Implementation:
Hide Copy Code
using (var scope = new TransactionScope())
{
UpdateLocalDatabase();
UpdateRemoteDatabase();
scope.Complete();
}
There is no difference between a local or remote/distributed transaction implementation code in transactions. Previously I said that TransactionScope implements ambient type transaction. This means, it automatically marks code blocks that need to support a transaction, local or remote. But you may find an error when working with distributed transactions. The error message will be like:
"The partner transaction manager has disabled its support for remote/network transaction."
If you find that type of exception, you need to configure security settings, both your local and remote servers, for MSDTC, and make sure services are running.
To find the MSDTC configuration interface, you will go to:
ControlPanel > AdministritiveTools >ComponentServices > DistributedTransactionCoordinator > LocalDTC
Some options for the Security tab are described bellow:
| Property Name | Description |
|---|
| Network DTC Access | If not selected, MSDTC will not allow any remote transaction |
| Allow Remote Clients | If it is checked, MSDTC will allow to coordinate remote clients for transaction. |
| Allow Remote Administration | Allow remote computers to access and configure these settings. |
| Allow Inbound | Allow computers to flow transaction to local computers. This option is needed where MSDTC is hosted for a resource manager like SQL Server. |
| Allow Outbound | Allow computers to flow transaction to remote computers. It is needed for a client computer where transaction is initiated. |
| Mutual Authentication | Local and Remote computers communicate with encrypted messages. They establish a secured connection with the Windows Domain Account for message communication. |
| Incoming Calling Authentication Required | If mutual authentication cannot be established but the incoming caller is authenticated then communication will be allowed. It supports only Windows 2003/XP ServicePack-2. |
| No Authentication Required | It allows any non-authenticated non-encrypted communication. |
| Enable XA Transaction | Allows different operating systems to communicate with MSDTC with XA Starndard. |
| DTC Logon Account | DTC Service running account. Default account is Network Service. |
18. Distributed Transaction Performance
Distributed transactions are slower than local transactions. A two phase commit protocol is used for managing distributed transactions. A two phase commit protocol is nothing but an algorithm by which a distributed transaction is performed. There are three commit protocols that are mostly used:
- Auto Commit: Transaction is committed automatically if all SQL statements are executed successfully or rolled-back if any of them fails to execute.
- Two Phase Commit: Transaction waits before final commit for messages from all other parties involved in transaction. It locks resources before commit or rollback. For this reason it is called a blocking protocol. In terms of performance it is the reason it is much slower. It is a widely used protocol for managing distributed transactions.
- Three Phase Commit: Transaction is finally committed if all nodes are agreed. It is a non-blocking protocol. In terms of performance it is faster than the two phase commit protocol. This protocol is complicated and more expensive but avoids some drawbacks in the two phase commit protocol.
19. Requirement-3
I want to create a transaction inside another transaction.
Implementation:
Hide Shrink 
Copy Code
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
var option = new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadCommitted,
Timeout = TimeSpan.FromSeconds(60)
};
using (var scopeOuter = new TransactionScope(TransactionScopeOption.Required, option))
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText="INSERT INTO Data(Code, FirstName)VALUES('A-100','Mr.A')";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
using (var scopeInner = new TransactionScope(TransactionScopeOption.Required, option))
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText="INSERT INTO Data(Code, FirstName) VALUES('B-100','Mr.B')";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
scopeInner.Complete();
}
scopeOuter.Complete();
}
No problems in creating a transaction inside anohter (nested) transaction. You should define the behaviour or the inner transaction. This behaviour is dependent on the value of TransactionScopeOption. If you selectRequired as TransactionScopeOption, it will join its outer transaction. That means if the outer transaction is committed then the inner transaction will commit if the outer transaction is rolled back, then the inner transcation will be rolled back. If you select the RequiredNew value of TrasnactionScopeOption, a new transaction will be created and this transaction will independently be committed or rolled back. You must be clear about those concepts before working with nested transactions using TransactionScope.
20. Requirement-4
I want to call rollback explicitly from a transaction.
Implementation:
Hide Copy Code
using (var scope = new TransactionScope())
{
Transaction.Current.Rollback();
scope.Dispose();
}
If you do not call the TransactionScope.Complete() method then the transaction will automatically be rolled back. If you need to explicitly call rollback for some scenarios, then you have two options:
- Executing
Transaction.Current.Rollback() will rollback the current transaction.
- Executing
TransactionScope.Dispose() will also rollback the current transaction.
Just one thing: remember that if you explicitly call Transaction.Rollback() orTranactionScope.Dispose() then you should not call the TransactionScope.Complete() method. If you do so then you will get an ObjectDisposeException.
"Cannot access a disposed object. Object name 'TransactionScope'"
21. Requirement-5
I want to create a file/folder dynamically inside a transaction scope. If my transaction is rolled back then I want that created file/folder to be removed automatically, like a database row.
Implementation:
Hide Copy Code
string newDirectory = @"D:\TestDirectory";
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var scope = new TransactionScope())
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into data(Code) values ('A001');";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
Directory.CreateDirectory(newDirectory);
File.Create(@"D:\NewFile.txt").Dispose();
scope.Dispose();
}
TranactionScope is not limited for only databases. It will support other data sources like FileSystem, MSMQ, etc. But you need more work to support TranactionScope. First of all what I show in the above code block will not work. Why? Because that directory creation and file creation will not be marked for transaction by default. Then what do we need to do?
Hide Copy Code
public interface IEnlistmentNotification
{
void Commit(Enlistment enlistment);
void InDoubt(Enlistment enlistment);
void Prepare(PreparingEnlistment preparingEnlistment);
void Rollback(Enlistment enlistment);
}
The System.Transactions namespace has an interface named IEnlistmentNotification. If I want my component/service to be transaction aware then I need to implement that interface. The following code will show a very simple and straightforward way to implement this:
Hide Shrink Copy Code
public class DirectoryCreator : IEnlistmentNotification
{
public string _directoryName;
private bool _isCommitSucceed = false;
public DirectoryCreator(string directoryName)
{
_directoryName = directoryName;
Transaction.Current.EnlistVolatile(this, EnlistmentOptions.None);
}
public void Commit(Enlistment enlistment)
{
Directory.CreateDirectory(_directoryName);
_isCommitSucceed = true;
enlistment.Done();
}
public void InDoubt(Enlistment enlistment)
{
enlistment.Done();
}
public void Prepare(PreparingEnlistment preparingEnlistment)
{
preparingEnlistment.Prepared();
}
public void Rollback(Enlistment enlistment)
{
if (_isCommitSucceed))
Directory.Delete(_directoryName);
enlistment.Done();
}
}
The above class will create a directory (folder) and this component is transaction aware. We can use this class with any TranactionScope and if TranactionScope is committed the directory will be created, otherwise it will be deleted (if already created). I show here just the diretory creation, if you want you can create a class/component for file creation. Now, how to use this class in the transactions scope?
Hide Copy Code
string newDirectory = @"D:\TestDirectory";
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var scope = new TransactionScope())
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into data(Code) values ('A001');";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
var creator = new DirectoryCreator(newDirectory);
Transaction.Current.Rollback();
}
Now, it will work!
Transactional NTFS(TxF) .NET is a open source project. You can use this library for creating/writing/coping file/directory inside transactionscope and it will support transaction automatically.
- You first need to download component from http://txfnet.codeplex.com
- Add that component as reference to your project.
- Use component api to your transactional block.
Txf API usage code sample:
Hide Copy Code
using (var ts = new System.Transactions.TransactionScope())
{
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into data(Code) values ('A001');";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
TxF.Directory.CreateDirectory("d:\\xyz", true);
TxF.File.CreateFile("D:\\abc.txt", File.CreationDisposition.OpensFileOrCreate);
ts.Complete();
}
TxF component supports:
- Create/Delete Directory
- Create/Delete File
- Read/Write File
- Copy File
22. Points of Interest
Transaction Management is actually a huge subject. It is a very complex subject too, specially distributed transaction. I tried my level best to present it as simple as possible. If you want to get all transaction related knowledge then you should study more on that. I suggest you read research papers on transactions, specially distributed transactions.
You can also explore more regarding transaction aware service/components. I showed here a very simple way to implement them. But in real life you may face difficult scenarios. So you need to prepare for that. In the near future Microsoft may add transaction aware components like dictionary/filesystem/directory service, etc. If it that happens then developers' life will be more easier.
What is a Transaction?
Frequently in a database application you come across a situation where you 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. The classic example of this is transferring money from a bank account into another bank account:
Hide Copy Code
UPDATE Accounts SET Balance = Balance – 10 WHERE Customer = 1;
UPDATE Accounts SET Balance = Balance + 10 WHERE Customer = 2;
If the first SQL statement was to execute and the second SQL statement was to fail, then ten dollars would be removed from the first customer’s account but will never be added to the second customer’s account. This would, naturally, be bad – ten dollars has just disappeared entirely.
One way to deal with this would be to handle the situation in your database access code. You could do this by catching System.Data.SqlClient.SqlException when you do your database access. The situation is not as easy as it first appears, however. The SQL statements could have failed in a number of places:
- Before the first SQL statement is executed.
- After the first SQL statement is executed.
- After the second SQL statement is executed.
This means that you have to determine what the balances of both the accounts were before the transfer and, after the exception was thrown, determine which account’s current balance doesn’t match the starting balance, and execute an UPDATE statement. This can, potentially, be a lot of code – especially if your example is non-trivial.
The situation is made even worse by the fact that SQL Server is a multi-user environment. At any given point of time, other users could be accessing the Accounts table – and if they access that table between your statement failing and your corrections to the table, they could very well access invalid data. This would be extraordinarily bad, and could lead to immensely hard to find bugs in your code. (bugs relating to data synchronization across several threads or processes are so difficult to find, that they have their own name: ‘Heisenbugs’)
Modern database theory postulates that in a perfect transaction world, a database would have a series of properties known as ACID. These properties are:
Atomic – All statements in a group must execute, or no statement in a group must execute.Consistent – This follows naturally from atomic – a group of SQL statements must take the database from a known starting state to a known ending state. If the statements execute, the database must be at the known ending state. If the statements fail, the database must be at the known starting state.Isolated – A group of statements must execute independently from any other statement groups being executed at the same time. If this wasn’t the case, it would be impossible for statement groups to be consistent – the known ending state could be altered by a code you have no control over or knowledge of. This is one of those concepts that are great in theory, but total isolation has important performance implications in the real world. More on how SQL Server implements this is explained later.Durable – Once the group of SQL statements execute, the results need to be stored in a permanent media – if the database crashes right after a group of SQL statements execute, it should be possible to restore the database state to the point after the last transaction committed.
In SQL Server, ACID-ness is provided by the concept of transactions. Simply put, a transaction is a way to group SQL statements together so that, when executed, the transaction obeys the ACID principles. A transaction is enabled on a single connection to the database (over the lifetime of a SqlConnection object, in .NET parlance) and will apply to all commands executed over that connection until the transaction ends. Once you have a transaction, there are two things you can do with it. You can either commit the transaction to the database at the end of the transaction, or you can abandon the transaction and rollback the changes made in the transaction.
In terms of Transact-SQL, there are three importance commands to manage a transaction. BEGIN TRANSACTIONwill begin a transaction, COMMIT TRANSACTION will commit the transaction to the database, and ROLLBACK TRANSACTION will roll the transaction back. These statements can actually be a little more complicated – feel free to refer to the MSDN documentation regarding these statements.
.NET Transactions
Within .NET, transactions are managed with the System.Data.SqlClient.SqlTransaction class. Again, 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:
Hide Shrink Copy Code
public class TransactionDemo
{
public TransactionDemo()
{
}
[STAThread]
public static void Main()
{
Demo1();
}
private static void Demo1()
{
SqlConnection db = new SqlConnection("connstringhere");
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction();
try
{
new SqlCommand("INSERT INTO TransactionDemo " +
"(Text) VALUES ('Row1');", db, transaction)
.ExecuteNonQuery();
new SqlCommand("INSERT INTO TransactionDemo " +
"(Text) VALUES ('Row2');", db, transaction)
.ExecuteNonQuery();
new SqlCommand("INSERT INTO CrashMeNow VALUES " +
"('Die', 'Die', 'Die');", db, transaction)
.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException sqlError)
{
transaction.Rollback();
}
db.Close();
}
}
As you can see from this example, we first open a connection to the SQL database. We then call theBeginTransaction 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 SqlCommandexecuted on that connection will be within the transaction. You can see, within the try block, we create and execute three SqlCommand objects. You’ll notice, though, that in this case we’re using the strings,SqlConnection, SqlTransaction, overload of the SqlCommand constructor. This is because the SqlCommandobject requires passing in the transaction bound to a connection – failing to do so will cause an exception to be thrown. This is, in my opinion, a weakness of the model – since a transaction is bound on a per-connection basis, and a SqlCommand object should be able to simply pull the SqlTransaction object out of the suppliedSqlConnection.
In the example above, the first two SqlCommand executes are perfectly valid – TransactionDemo exists in the database. The CrashMeNow table, however, does not. Since the table does not exist, a SqlException object will be thrown on ExecuteNonQuery object. It is important to realize that having a transaction "does not replace the standard exception handling mechanism". If you think your statements might not execute, you have to catchSqlException and, within your catch block, rollback 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.
If you execute the code above and look at the TransactionDemo table, you’ll see that no rows have been added – the transaction was rolled back after the exception was thrown. If you remove the offending line of SQL, though, and run the program and look again, you’ll see that two rows have been added. That is, essentially, Transactions in action.
Advanced Transactions – isolation levels
This is not, though, the limit of transactions. As I stated earlier in my description of ACID properties, transactions don’t necessarily meet the strictest definition of Isolated. This is because the isolation level of a transaction is configurable by you, when you create the transaction.
Why would you want to do this? Performance. While full isolation is wonderful in theory, so was communism. In reality, exclusively locking a set of rows while a transaction works on them may be unfeasible due to performance – you don’t necessarily want to stop every reader in the system from reading a table because your transaction locked the whole thing.
To alleviate this concern .NET (through SQL Server), provides the ability to specify isolation levels when you create a transaction. Doing this simply requires supplying a System.Data.IsolationLevel value to theBeginTransaction method. The available values for SQL Server 2000 are:
ReadUncommitted – This is, essentially, no isolation. Anyone can read the data placed in a table or updated immediately after the SQL statement causes the change – no commit is required. This could lead to a process having out-of-date data: it may be using a version of the data that was then rolled back out of the table!ReadCommitted – This is slightly more isolated. In this case, a transaction can only read data from the table that has already been committed. When a transaction wants to update data, it acquires a shared lock on that data and (if successful getting the lock) updates the data. Transactions outside of that transaction cannot update the data in that table until the locking transaction commits. This is only slightly more isolated, however: a SQL statement executed twice within a transaction could return a different result-set if a second transaction changes and commits the data the SQL statement executes on between the two statements. This is the default isolation level for SqlTransaction.RepeatableRead – Slowly getting more isolated. In this case, a shared lock is applied on all data queried within a transaction. This means that no other transaction can alter the data used in your transaction. This prevents the case where data you had queried once changes on subsequent queries. It does not, though, prevent the case where rows are added to the table that may be returned in subsequent queries.Serializable – Locks are placed on ranges of the tables you are using, preventing other users from changing your data or adding new rows underneath you. This is the most isolated isolation level, but it does come with the drawback of locking more data than your transaction may strictly need.
In SQL Server 2005, a new isolation level will be added: snapshot isolation. In snapshot isolation, rows are versioned once they are accessed in a transaction. This essentially means that once a transaction accesses a set of values, they are guaranteed to remain the same until you commit or rollback the transaction. Other transactions starting in the middle of the first will get a ‘copy’ of the original database to operate on. Before any transaction commits, though, SQL Server will test to ensure that the original data they were operating on is the same as the current data in the database. If this is the case, the transaction will commit. Otherwise, the transaction will be rolled back and the user will have to try the batch once again.
Conclusion
Transactions are useful for several other things. First, they provide a way to rollback a group of SQL statements if a single one should. Remember that failure can mean more than just an error being returned. A failure can also be logically related - in the example above, perhaps the account being transferred from doesn’t have enough money to do the deposit. In that case, you can roll back the transaction when you discover that fact. Secondly, they provide a way to isolate the data your transaction is working on so that you don’t have to worry about surprises. In all cases, though, you should examine what isolation level you really need and be aware of the performance implications of all of them.
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:
Hide Copy Code
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.
Hide Shrink Copy Code
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.
Hide Shrink Copy Code
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.
Hide Shrink Copy Code
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, thisTransactionScope object will join that one.RequiresNew: Even if within current scope another TransactionScope is already instantiated, thisTransactionScope object will create a new transaction that will work within its scope.Supress: Even if within current scope another TransactionScope is already instantiated, thisTransactionScope 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:
Hide Copy Code
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 2005or 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.
