Automatic DB Transaction Handling

Overview

ChristianK came up with a way of simplifying the implementation of the DB Access Exception Handling Policy by way of making the Committing or Rolling Back of DB Transactions automatic.

The main goals of this approach are:

• To help us prevent 'dangling transactions' in the simplest way possible.
• To allow the software engineers to use that approach in almost every situation.

That goal is achieved with several new Methods and their overloads that were added to the TDataBase Class. They internally call existing Methods of that Class that we ordinarily use for (non-automatic) DB Transaction handling.

These new Methods can be seen as 'wrappers' around arbitrary program code sections that access the DB in OpenPetra. The Methods each use a C# Delegate to facilitate the 'wrapping' and are fully transparent to the functionality of the code section that they are 'wrapping'.

Advantages

• Correct DB Transaction committing/rolling back is ensured - 'dangling transactions' are always and automatically prevented (for Write Transactions: the programmer needs to supply the correct state in Variables to make that happen)!
• The software engineer doesn't need to write DB Commit or DB Rollback statements.
  • This is fully automatic for Read Transactions.
  • For Write Transactions the programmer needs to set Variables to the correct state (normally just 'bool SubmissionOK' or 'TSubmitChangesResult SubmissionResult')!
• Less 'boilerplate' program code: try-finally blocks do not need to be written (and hence cannot get written in a wrong or buggy way [esp. in Write Transactions scenarios, and there especially in GetNewOrExistingTransaction scenarios]) - less program code to write, read and maintain!
• Exception Handlers does not need to care about a DB Rollback because that either
  • has happened automatically before the Exception Handler is entered if the Exception Handler is outside of the 'automatic' scope, or
  • will happen automatically if the Exception Handler is inside the 'automatic' scope.
  • It doesn't matter whether Exception Handlers are inside or outside of the 'automatic' scope - the only difference is that Exception Handlers that are inside still have access to a working DB Transaction, whereas Exception Handlers that are outside won't as the DB Transaction will have already been rolled back automatically.
• The approach can be used also When DB Transactions for Writing Span Several Methods!
• Authough 'automaticness' is gained with that approach, it is fully transparent what happens (and the software engineer can step into the program code of any of the new Methods that handle the automatic DB Transactions to see the DB Commits/Rollbacks happen)!!!
  • This is unlike the automatic DB Transactions that ADO.NET generates for us if we access the RDBMS without taking out a DB Transaction first!

Details

• Write Transactions
  • Rollback automatically occurs if an Exception is thrown inside the code section that becomes a 'delegate'!
  • Commit automatically occurs
    • if the programmer supplies 'ASubmissionOK = true' - if it is (left) false, a DB Rollback will be done.
    • if the programmer supplies 'SubmissionResult = TSubmitChangesResult.scrOK' - if it has got another value (incl. 'TSubmitChangesResult.scrInfoNeeded'!), a DB Rollback will be done.
    • That DB Commit is not done if one of the GetNewOrExistingAutoTransaction Method overloads was called and a DB Transaction was 'piggy-backed' on!
    • An overload of the GetNewOrExistingAutoTransaction Method exists that can be supplied with 'ACommitTransation = false' to prevent the automatic DB Commit, even if 'ASubmissionOK = true' (or 'SubmissionResult = TSubmitChangesResult.scrOK')!
      • useful When DB Transactions for Writing Span Several Methods
• Read Transactions
  • Rollback automatically occurs if an Exception is thrown inside the code section that becomes a 'delegate' and also if no Exception happened!
• Exception handling by the software engineer can be done as normal and follows the 'usual execution path' that is followed in case an Exception happens.

• The software engineer can create an extra try/finally code section around the the code section that becomes a 'delegate', should the need for that arise.
  • That hand-written 'finally' block will be executed after the 'automatic' finally block, i.e. the automatic transaction handling will have already occurred (and the DB Transaction will have been either Committed or Rolled Back).

Extra Things that You Need to Know

• 'return' statements can be placed inside the code section that becomes a 'delegate', but they leave that code section and not the Method that contains the Automatic Transaction, and only a 'return;' statement that doesn't return a value is possible.
• The code section that becomes a 'delegate' must not use 'ref' or 'out' Arguments of the Method that they are contained in (as that will result in the C# Compiler Error CS1628)
  • Solution: Declare a local Variable and assign the value of a 'ref' or 'out' Argument to it, then use the local Variable in the code section that becomes a 'delegate'. (In case of a 'ref' Argument you will likely need to assign the value of that Variable to the Argument before you leave the Method in order for the Argument to have the same value than the Variable.)
• The DB Transaction will already be Rolled Back when any Exception Handler is entered that comes after the code section that becomes a 'delegate'
  • That is so that the programmer does not need to (and cannot forget to!) call DBAccess.GDBAccessObj.RollbackTransaction() manually!
  • Solutions in case you still need to have access to the DB Transaction:
    • Put the try/except code inside the code section that becomes a 'delegate' - that way the DB Transaction isn't yet rolled back, but is guaranteed to be rolled back once the Exception Handler is left;
    • Not using the Automatic DB Transaction Handling approach (not recommended, but might be needed in very specific circumstances)!
• Debugging:
  • There is some extra code to step through if one single-steps into/out of a code section that becomes a 'delegate' (compared to the code that follows the try/finally Policy and that doesn't use the Automatic DB Transaction Handling)
    • Solution if you don't want that to happen:
      • Use 'Step over' (F10) instead of 'Step into' (F11) on those code lines.
      • In case you use SharpDevelop: Put a breakpoint on the line in the delegate from where on you want to investigate as F10 steps over the entire code that is contained in the delegate if this isn't done!
  • When using SharpDevelop 5 to halt debug execution at the 'Automatic DB Handling' scope the whole code section is highlighted in yellow - very helpful, as the scope of the DB access logic is easily seen through that!

Implementing Automatic DB Transaction Handling

TDataBase Class Methods for Automatic DB Transaction Handling

The TDataBase Class has got the following Methods that facilitate the Automatic DB Transaction Handling:

Reading Data

• BeginAutoReadTransaction
  • Use the overloads of this Method instead of the overloads of BeginTransaction.
  • The DB Transaction is always Rolled Back!
• AutoReadTransaction
  • Use whenever you need to work with an already running DB Transaction and you need to handle the DB Transaction Rollback in your scope of code (which would be somewhat unusual, but it's a possible scenario).
  • The DB Transaction is always Rolled Back!

Writing Data

• BeginAutoTransaction
  • Use the overloads of this Method instead of the overloads of BeginTransaction.
    • The Arguments 'bool ASubmissionOK' and 'TSubmitChangesResult ASubmitChangesResult' (respectively) determine whether the DB Transaction gets Committed or Rolled Back when no Exception happened during the execution of the program code contained inside the Delegate.
    • An additional overload exists that has an extra Argument, 'bool ACommitTransaction = true'. It suppresses the Committing of the DB Transaction when no Exception happened if it set to false (that will be needed rarely).
• GetNewOrExistingAutoTransaction
  • Use the overloads of this Method instead of the overloads of GetNewOrExistingTransaction.
    • The Arguments 'bool ASubmissionOK' and 'TSubmitChangesResult ASubmitChangesResult' (respectively) determine whether the DB Transaction gets Committed or Rolled Back when no Exception happened during the execution of the program code contained inside the Delegate.
    • An additional overload exists that has an extra Argument, 'bool ACommitTransaction = true'. It suppresses the Committing of the DB Transaction when no Exception happened if it set to false (that will be needed rarely).
• AutoTransaction
  • Use whenever you need to work with an already running DB Transaction and you need to handle the DB Transaction Commit / DB Transaction Rollback in your scope of code (which would be somewhat unusual, but it's a possible scenario).
    • The Arguments 'bool ASubmissionOK' and 'TSubmitChangesResult ASubmitChangesResult' (respectively) determine whether the DB Transaction gets Committed or Rolled Back when no Exception happened during the execution of the program code contained inside the Delegate.

Please refer to XML Code Documentation for details of the Methods and their overloads!

Usage

The Automatic DB Transaction Handling Methods replace a try/catch/finally code block that would be used if manual programming would be done to ensure that DB Transactions are handled correctly and that no DB Transaction is left 'dangling' under any circumstance.

Code Example - Manual Programming

TDBTransaction MyTransaction = GetNewOrExistingTransaction(ADesiredIsolationLevel, 
    ATryToEnforceIsolationLevel, out NewTransaction);
             
try
{
   << usual program code that access the DB >>
}
except
{
   ...
   << DB Rollback Handling >>
   ...
}
finally
{
   ...
   << DB Commit / DB Rollback Handling >>
   ...
}

Code Example - Automatic

DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum,  
    ref Transaction, ref SubmissionOK, 
delegate
{
   << usual program code that access the DB >>
});

That Method call includes/spans the whole code that accesses the DB! For that reason please note two special things here: The 'delegate'

1. begins as an Argument to the Method call - ', delegate {'
2. ends with '});' - the closing curly brace ends the delegate, the closing parenthesis ends the Method, and the semicolon ends the C# Method statement as a whole.

Please follow the style presented here by writing the 'delegate' keyword and the opening curly brace '{' each on separate lines and the '});' character sequence on a separate line as well, as this makes things clearer than if those aren't put on separate lines. Please also keep the indentation as it is shown here for consistency.

No try/catch/finally (Usually)!

No try/catch/finally is needed in the automatic scenarios, unless

• you need to handle Exceptions manually for specific purposes, i.e. when you need to do more than just a DB Rollback in the Exception Handler (logging, for example);
• you need to have a finally clause that needs to do more than just the DB Commit / DB Rollback operations.

You are free to add try/catch, try/finally or try/catch/finally blocks either inside or outside the Automatic DB Transaction Handlings' delegate code block. If put inside, the blocks will have access to the running DB Transaction, if put outside the blocks won't (as the DB Transaction will already be Committed or Rolled Back in the latter case).

Please have a look at the examples below to get a better idea how this all works!

Examples

Reading Data

• \trunk\csharp\ICT\Petra\Server\lib\MCommon\Cacheable-generated.cs, Method 'GetCacheableTable' (auto-generated!)
  • utilises DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction

Writing Data

• \trunk\csharp\ICT\Petra\Server\lib\MCommon\Cacheable-generated.cs, Method 'SaveChangedStandardCacheableTable' (auto-generated!)
  • utilises DBAccess.GDBAccessObj.BeginAutoTransaction and TSubmitChangesResult.
• \trunk\csharp\ICT\Petra\Server\lib\MFinance\Common\Common.Posting.cs, two overloads Method 'CreateABatch'
  • utilise DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction and bool SubmissionOK.
• \trunk\csharp\ICT\Petra\Server\lib\MPartner\connect\Partner.PartnerEdit.cs
  • utilises DBAccess.GDBAccessObj.BeginAutoTransaction and TSubmitChangesResult.