Home > Articles > Programming > Windows Programming

Have Only One Controlling Part of a Transaction

  • Print
  • + Share This
If you follow the recommendation to use local transactions when you only have one Resource Manager, but want to prepare for a possible future change to distributed transactions and have as few programming changes as possible, one option is to have only one controlling part for the transaction. Learn more in this chapter excerpt by Jimmy Nilsson.
This excerpt is derived from chapter 6 of .NET Enterprise Design with Visual Basic .NET and SQL Server 2000, by Jimmy Nilsson.
This chapter is from the book

If you follow the recommendation to use local transactions when you have only one Resource Manager, but want to prepare for a possible future change to distributed transactions and have as few programming changes as possible, there are a couple of issues to consider. One issue is to only have one controlling part for the transaction. This brief article provides describes useful ways to attack that issue.

It's possible to use BEGIN TRANSACTION and COMMIT TRANSACTION in your stored procedures, even if they are to be used from COM+ components. SQL Server will keep track of the current @@TRANCOUNT to determine whether COMMIT TRANSACTION really means a COMMIT, or whether it is only subtracting one from @@TRANCOUNT. That's perfectly acceptable.

Nevertheless, there is at least one problem with this. ROLLBACK TRANSACTION won't just subtract 1 from @@TRANCOUNT; it will do a ROLLBACK of the complete transaction, which can create a problem for the components. In my opinion, it's much better to decide on just who is responsible for doing something and then nobody else will interfere. (At least not as long as the first party does the job.) Therefore, I won't do a BEGIN TRANSACTION and COMMIT TRANSACTION/ROLLBACK TRANSACTION in my stored procedures if COM+ transactions are responsible for taking care of the transactions. However, this makes a real mess if the stored procedures are also to be used from other consumers that don't handle transactions on their own.

Moving from automatic to manual transactions for some scenarios will also take a lot of work. However, I use the solution to the problem shown in Listing 1. @@TRANCOUNT is stored in a local variable when the stored procedure is entered. When the stored procedure is about to start a transaction, it investigates whether there is already an active transaction. If there is, there won't be another BEGIN TRANSACTION. At COMMIT/ROLLBACK time, a similar technique is used. If there wasn't an active transaction when the stored procedure was entered, it should be COMMITted/ROLLedBACK now. You should also add to this the criterion that there must be an active transaction immediately. (There can now be several COMMIT sections in the stored procedure without creating any problems.) Clean and simple.

Listing 1—Excerpt from a Stored Procedure Showing How to Write Flexible Transaction Code

 SET @theTranCountAtEntry = @@TRANCOUNT
 IF @theTranCountAtEntry = 0 BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  BEGIN TRAN
 END

 UPDATE...

 --Another DML-statement...
 INSERT...

ExitHandler:
 IF @theTranCountAtEntry = 0 AND @@TRANCOUNT > 0 BEGIN
  IF @error = 0 BEGIN
   COMMIT TRAN
  END
  ELSE BEGIN
   ROLLBACK TRAN
  END
 END

NOTE

Oracle, DB2, and even the SQL-99 standard do not support BEGIN TRANSACTION, but the first SQL command will start the transaction.

Something else you may need to add to this solution is handling the transactions that span over several stored procedures from your ADO.NET code. Then you can use ContextUtil.IsInTransaction() to determine whether you should start a new ADO.NET transaction. (You could also ask the database server for the @@TRANCOUNT value from your component, but that would lead to one more round trip, and you definitely don't want that.)

  • + Share This
  • 🔖 Save To Your Account