Have Only One Controlling Part of a Transaction
This chapter is from the book
This chapter is from the book
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.)