Everybody Likes a Demo
As hardcore techies, you no doubt like to see code to understand all this theory, so roll up your sleeves and dive in. I'll show you how to write a quick code sample demonstrating a distributed transaction involving two databases.
First you need two databases. You can easily set them up using the following T-SQL script.
Create Database db1
Create Table Test1
Create Database db2;
Create Table Test2
With this database setup, suppose you want to insert a simple test value into both tables-Test1 and Test2. If either fails, you want to roll back the entire transaction.
The following code will insert into a single database. Note there is nothing transactional about this code yet. This is plain vanilla ADO.NET code.
using (SqlConnection conn1 =
SqlCommand cmd1 =
"Insert Into Test1(Test1) values
If you have two such blocksone for each database, how do you make the entire operation transactional? That, as it turns out, is rather straightforward. As you can see in the code below, you simply wrap two such blocks inside one TransactionScope block and put TransactionScope.Complete
at the very end.
using (TransactionScope txScope =
TransactionInformation info =
using (SqlConnection conn1 =
SqlCommand cmd1 = conn1.CreateCommand();
"Insert Into Test1(Test1) values ('xxx')";
using (SqlConnection conn2 =
SqlCommand cmd2 = conn2.CreateCommand();
"Insert Into Test2(Test2) values ('yyy')";
"Before:" + info.DistributedIdentifier);
"After:" + info.DistributedIdentifier);
Using this methodology, your code doesn't change one bityou just wrap everything inside a single TransactionScope instance. There is some diagnostic information that you're peeping into, however, using the TransactionInformation
variable you declared. Now run the above code and check the output.
You should see the following output.
Interestingly, there isn't a valid distributed identifier associated with the transaction until the second SqlConnection instance is opened. In fact, if you go to Control Panel, choose Administrative Tools, then choose Component Servicessurprise surprisethe GUID shown next to the current running transaction matches the output your program produces. See Figure 1
|Figure 1: Transaction escalated to MSDTC showing a valid GUID.|
At this point the transaction has been promoted to MSDTC and its isolation level has also been escalated. You can verify that by attempting to run a select query using the default ReadCommitted
isolation level from SQL Server Management Studio. You will find that as soon as the second SqlConnection, conn2
, is opened, the inserted records on conn1
are no longer accessible unless you use the NOLOCK
query hint or the ReadUncommitted
The important take-away from this example is the ease by which you can wrap a number of operations within one transaction. But there are a number of questions to consider:
- If it is really this simple, why don't I wrap all my data access within a single transaction scope? If you think I should do that, why isn't my code transactional by default?
- If the SqlConnection instance, conn1, has been closed before the transaction was actually committed, how in the world does it manage to roll the transaction back in the event of failure?
- The above code demonstrated using ExecuteNonQuery, but what if I wanted to wrap DataAdapter.Update or use a TableAdapter and wrap those operations within a single transaction?
I can answer the first question rather easily. If it were that easy, then your code would have definitely been transactional by default. Unfortunately, a one-size-fits-all approach cannot work when you are dealing with disconnected data updates. This is evident when you call DataAdapter.Update
, which ends up running multiple commands and saves your data in the database. Suppose that your DataAdapter tries to execute five commands and the third command errors out. Should you continue with the update? Should you roll back the first and second commands? Should you stop right there and do nothing? Should you revert the data back in the DataSet? Should you communicate the successful updates along with the failed rows? How do you extract the newly retrieved values out of the database and yet not mess up the row states for the rows that did not end up getting saved?
Oh my, that's a lot of questions and possibilities. Needless to say, you need enough control to fine tune the exact behavior for your needs, and a one-size-fits-all approach just cannot work.
I'll show you another practical example to answer the rest of the two questions.