DB Access Exception Handling Policy

Overview

(This policy is an extension of the Error and Exception Handling Policy!)

The correct handling of Exceptions that occur because of database access is very important for the following reasons:

1. DB Exception Handling must ensure that
   1. Any DB Transaction that was started in the same program scope (e.g. C# Method, or a particular 'chain' of C# Method calls) and that was used for the DB command that resulted in an Exception is rolled back (whether read or write access to the DB occurs). That way a DB transaction can never be left running ('dangling') where it shouldn't. How to do that
2. DB Exceptions must not get 'swallowed'. While suppressing an Exception (by 'swallowing' it) is never a good idea and is actively discouraged, suppressing a DB Exception is especially bad as the caller of a Method that suppressed the Exception that occurred because of the DB access has no way of knowing of problems that really happened while accessing the DB and hence can not know whether the DB access succeeded, or not.

The Danger of 'Dangling DB Transactions'

DB Transactions that are left running where they shouldn't (sometimes referred to as 'dangling DB Transactions') will very likely cause problems further down the execution path of the program code, or with the next program code path that is executed by the user (esp. after an Exception happened), as other program code...

• might attempt to start a new DB Transaction, which will fail as the 'dangling DB Transaction' is still running and only one DB Transaction can be running at a given time;
• might be written in such a way that it can re-use an existing Transaction, but ...
  • that 'piggybacking on a running DB Transaction' might fail if the Isolation Level that is required by that program code is higher (=more restrictive) than the Isolation Level of the running DB Transaction. Also, that program code could ask for a specific, exact Isolation Level and the the Isolation Level of the running DB Transaction is different. In both cases an Exception is raised (either EDBTransactionIsolationLevelTooLowException or EDBTransactionIsolationLevelWrongException).
  • in case the re-using of the DB Transaction doesn't fail when the DB Transaction is requested by that program code then the Isolation Level of the running DB Transaction could be higher than the Isolation Level of the DB Transaction of that program code. That will lead at least to unnecessary DB Locks taken out and can potentially lead to concurrency problems that would otherwise not happen. Both of those issues are very hard to pin-point once they happen as it is often not obvious what program code has initially taken out a DB Transaction that then causes the mentioned problems later on when the DB Transaction is re-used (esp. once the application is used by users)!

DB Exception Handling

Scenario 1: Only Reading Data

While there are several possible solutions to making sure that a DB Transaction that is used for reading gets ended correctly, only the following solution is permitted because a Rollback can never be forgotten about - this is also true for the case where a 'return' statement is executed in the 'try' code block of the try-(catch-)finally Clause:

Policy: DB Transaction Rollback only in Finally Clause which encloses try Clause or try-catch Clause.

The DB Transaction must not get Rolled Back before the Finally Clause - in other words, the DB Transaction must not get Rolled Back before the first Catch Clause and must also not get Rolled Back in any Catch Claus(es) (if present).

Example: XXX

To Rollback or to Commit? There are discussions as to what is the right action to finish a DB Transaction that was used exclusively for reading of data: issue a Rollback or issue a Commit. Some people are in favour of Commiting, but the danger with that approach is that if the DB Transaction that was used for reading was re-used and if in earlier code paths data was written to the DB using that DB Transaction then that data will get committed in the code section that was only reading, and that code section has no knowledge of 'what' it is unknowingly committing to the DB! Some people argue that a Rollback might cause more overhead in the RDBMS than a Commit, but we are still favouring Rollback over Commit because the danger of committing data to the DB unknowingly is not there.

--> New approach that simplifies the implementation of the policy for the software engineers: Automatic DB Transaction Handling <--

Scenario 2: Writing Data / Mix of Reading and Writing of Data

While there are several possible solutions to making sure that a DB Transaction that is used for writing, or for a mix of reading and writing, gets ended correctly, only the following solution is permitted because a DB Transaction Commit or Rollback can never be forgotten about - this is also true for the case where a 'return' statement is executed in the 'try' code block of the try-(catch-)finally Clause:

Policy: DB Transaction Commit or Rollback only in Finally Clause which encloses try Clause or try-catch Clause.

The DB Transaction must not get Committed or Rolled Back before the Finally Clause - in other words, the DB Transaction must not get Committed before the first Catch Clause and must also not get Rolled Back in any Catch Claus(es) (if present).

Whether a DB Transaction Commit or a DB Transaction Rollback is done in the Finally Clause depends on the value of a boolean Variable ('SubmissionOK') that needs to be declared outside of the try-(catch-)finally block (best done at the beginning of a Method).

• The SubmissionOK Variable ought to be initialised to false in the Variable declaration. The purpose of doing this is twofold:
  • The Finally Clause will not throw an Exception on reading the Variable in case the Variable wasn't assigned on purpose before the Finally Clause gets entered;
  • It ensures that a DB Transaction Rollback will always occur in case the software engineer forgot to set the Variable to false in an 'error condition'.
    The software engineers are still encouraged to set it to false specifically in an 'error condition' - although this is superfluous, it is clearer that in this case the DB Transaction will be Rolled Back. Put simply, this is the place in program code where one would issue a DB Rollback, and that is substituted with the setting of the SubmissionOK Variable to false.
• The value of that Variable needs to be set to true in a 'success condition' in the flow of the program code that is 'inside' the 'try' code section. This ensures that the DB Transaction will be Committed in the Finally Clause.

Example: XXX

The way how the DB Transaction handling in Scenario 1 and 2 is to be done was made 'policy' upon agreement in the OpenPetra Developers' Conference call on April 29th, 2014.

However, quite a lot of DB Transaction handling code in OpenPetra does not meet that policy at present (April 2014). For that reason we agreed that whenever a developer changes anything in a Method that contains a code section that deals with DB Transaction handling code, the developer will change the DB Transaction handling code to meet this policy in case it doesn't already meet this policy. That way we the policy will be implemented more and more across the server-side code base.

For any new server-side code that contains DB Transaction handling code the policy is to be implemented right from the start, of course!

--> New approach that simplifies the implementation of the policy for the software engineers: Automatic DB Transaction Handling <--

DB Transaction Handling When DB Transactions Span Several Methods

TODO

• handling of long running DB Transaction over many Method Calls ('Control Method' / 'Super Structure')
  • Discussion: http://sourceforge.net/apps/phpbb/openpetraorg/viewtopic.php?p=364#p364
    • Starting of a local DB Transaction or use of a Session Object?
    • Exception handling