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 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'.

Go to Usage to see how this looks like!

Policy: Use the 'Automatic DB Transaction Handling' wherever feasible! Only where this is not feasible use the 'Manual DB Exception Handling' as a fall-back.

Advantages

• Correct DB Transaction committing/rolling back is ensured - dangling transactions are always and automatically prevented (for write Transactions, or a mix of reading and writing 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, or a mix of reading and writing 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 do 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!
• Although 'automatic-ness' 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

• Read Transactions
  • Commit is never done.
  • Rollback automatically occurs if an Exception is thrown inside the code section that becomes a 'delegate'.
  • Rollback also occurs automatically if no Exception is thrown inside the code section that becomes a 'delegate' - except when the 'GetNewOrExistingAutoReadTransaction' Method is used: if the DB Transaction was already running when the delegate got entered and no Exception was thrown inside the code section that becomes a 'delegate' then this particular Method does nothing with the DB Transaction, i.e. the DB Transaction is neither Rolled Back nor Committed!
• 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!
    • Overloads of the GetNewOrExistingAutoTransaction Method exists that can be supplied with 'ACommitTransaction = false' to prevent the automatic DB Commit, even if 'ASubmissionOK = true' (or 'SubmissionResult = TSubmitChangesResult.scrOK')!
      • useful When DB Transactions for Writing Span Several Methods

Method overload relationships

• Overloads
  • There are many different method overloads to provide simple and flexible calling. The diagram to the right shows how these overloads fit together and how they are currently used in OpenPetra code (as of February 2017).

• 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 AutoTransaction methodology does not catch Exceptions, nor does it re-throw the same or throw different Exceptions, and it also doesn't 'swallow' Exceptions.)

• 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.)
    • Note: By assigning the value to the local Variable, the local Variable points to the same address in memory than the value of the 'ref' or 'out' Argument - hence they are referencing the same instance of the object. This is an implementation detail of C# (this is the same as with Java).
• 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!
  • As the software engineer can step into the program code of any of the new Methods that handle the automatic DB Transactions (s)he can see as to why the DB Commits/DB Rollbacks happen!
  • 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 when reading data.
  • DB Transaction interactions
    • Starts a DB transaction before the delegate is entered.
    • The DB Transaction is always Rolled Back (also in case an Exception was thrown inside the code section that becomes a delegate)!
• AutoReadTransaction
  • Use whenever you need to work with an already running DB Transaction when reading data 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).
  • DB Transaction interactions
    • Nothing is done with/to the already running DB Transaction before the delegate is entered.
    • The DB Transaction is always Rolled Back (also in case an Exception was thrown inside the code section that becomes a delegate)!
• GetNewOrExistingAutoReadTransaction
  • Use the overloads of this Method instead of the overloads of GetNewOrExistingTransaction when reading data.
  • DB Transaction interactions
    • Starts a DB transaction before the delegate is entered if no DB Transaction is running, otherwise nothing is done with/to the already running DB Transaction before the delegate is entered.
    • The DB Transaction is always Rolled Back if the DB Transaction got started through this Method call, otherwise it is Rolled Back only in case an Exception was thrown inside the code section that becomes a delegate!
      • That means that if the DB Transaction was already running when the delegate got entered and no Exception was thrown inside the code section that becomes a delegate then this particular Method does nothing with/to the DB Transaction, i.e. the DB Transaction is neither Rolled Back nor Committed!

Writing Data

• BeginAutoTransaction
  • Use the overloads of this Method instead of the overloads of BeginTransaction when writing data, or in case of a mix of reading and writing of data.
  • DB Transaction interactions
    • Starts a DB transaction before the delegate is entered.
    • 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.
    • The DB Transaction is always Rolled Back in case an Exception was thrown inside the code section that becomes a delegate!
• 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). Use when writing data, or in case of a mix of reading and writing of data.
  • DB Transaction interactions
    • Nothing is done with/to the already running DB Transaction before the delegate is entered.
    • 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.
    • The DB Transaction is always Rolled Back in case an Exception was thrown inside the code section that becomes a delegate!
• GetNewOrExistingAutoTransaction
  • Use the overloads of this Method instead of the overloads of GetNewOrExistingTransaction when writing data, or in case of a mix of reading and writing of data.
  • DB Transaction interactions
    • Starts a DB transaction before the delegate is entered if no DB Transaction is running, otherwise nothing is done with/to the already running DB Transaction before the delegate is entered.
    • 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.
    • Overloads of the GetNewOrExistingAutoTransaction Method exists that can be supplied with 'ACommitTransaction = false' to prevent the automatic DB Commit, even if 'ASubmissionOK = true' (or 'SubmissionResult = TSubmitChangesResult.scrOK')!
      • useful When DB Transactions for Writing Span Several Methods
    • The DB Transaction is always Rolled Back in case an Exception was thrown inside the code section that becomes a delegate!

Careful when a Mix of Reading Data and Writing Data is Performed When Using GetNewOrExistingAutoTransaction

The GetNewOrExistingAutoTransaction Method will be used in scenarios where the C# Method in which it is used in could be called in situations where a DB Transaction could already be running - or not. If you are using GetNewOrExistingAutoTransaction inside a given C# Method and in it you have a mix of code sections where data is just read from the DB, and other code sections that write data to the DB, and all these code sections are all enclosed by the same delegate then you need to be careful that there is no way that this C# method could accidentally Roll Back a DB Transaction that has been started outside of that Method: if the ASubmissionOK Argument is not set to true in any condition then a DB Transaction Rollback will occur when the delegate is left - no matter whether you were just reading data, or writing data! It is the DB data reading scenario where one could likely not be aware that the ASubmissionOK Argument must be set to true before the delegate is left!!! (If ASubmissionOK Argument is false when the delegate is left (through any means) then the DB Transaction that was started outside of this C# Method will be Rolled Back!)

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.