Working with multiple DB Connections

THIS PAGE IS WORK IN PROGRESS AND DESCRIBES FEATURES THAT ARE NOT YET IN TRUNK !!!

What is described here is subject to change without notice until this is in trunk !!!

Working with multiple DB Connections: Overview

The Limitations That it Overcomes

• Multiple Threads can run arbitrary DB commands against a single DB Connection in a safe way due to the introduction of the Co-ordinated DB Access (Thread-safe DB Access) feature, but there are limitations to this:
  • Each Thread must start a DB Transaction with the GetNewOrExistingTransaction or GetNewOrExistingAutoTransaction Methods as it usually not known which of the Threads will start the DB Transaction first;
  • Two Threads cannot take out/piggy-back on a DB Transaction when the IsolationLevels that are required don't match (as the Threads are essentially sharing the same DB Transaction in a thread-safe way);
  • True 'parallel' DB access is not achievable (as the Threads are sharing the same DB Transaction in a safe way by running DB commands one-after-the-other instead of truly parallel);
  • Handling the outcome of a Thread 'losing out' on obtaining Co-ordinated DB Access when a time-out occurs is either done automatically, which results in 'stock messages' being shown to the user (asking the user to retry the action that [s]he wanted to take a bit later), or - to avoid this - needs to be done intentionally by the software engineers. As the timing in which such situations happen cannot easily be foreseen and not easily be 'forced to happen' during development this can be a cumbersome aspect of the Co-ordinated DB Access approach;
• Historically, only a single DB Connection per connected Client was possible. (This 'Default DB Connection' gets established automatically when a Client connects and gets closed automatically when a Client disconnects or crashes.)
  • While an OpenPetra software engineer could make an attempt to open a separate DB Connection, start a DB Transaction and try to run DB commands against it this failed with a whole variety of Exceptions due to several bugs in the 'OpenPetra DB Access and Abstraction Layer' (in the TDataBase Class and related Classes).

From trunk commit Rev. XXXX (September xx, 2015) onwards it has become possible for OpenPetra software engineers to...

• reliably open (a) DB Connection(s) that are truly separate from the 'Default DB Connection' which exists for each Client connection (that is even possible in parallel in multiple Threads!);
• reliably start a new DB Transaction with the BeginTransaction / BeginAutoTransaction commands (or GetNewOrExistingTransaction/GetNewOrExistingAutoTransaction commands) on such a separate DB Connection, with any IsolationLevel;
• reliably run an arbitrary number of arbitrary SQL Commands that are enlisted in such a DB Transaction;
• Commit or Rollback such a DB Transaction completely independent from DB Transactions on other DB Connections (also from the 'Default DB Connection');
• Close such a separate DB Connection completely independent from other DB Connections (also from the 'Default DB Connection').

This paves the way to true parallel DB Access from multiple Threads where it is desired/needed!

The Solution

The primary solution was to find bugs in the TDataBase Class (OpenPetra's Database Access and Abstraction Layer) and related Classes that prevented reliable independent DB Connections, DB Transactions and DB Commands, and to fix them. This was done by extending our existing NUnit Tests for the TDataBase Class with multi-threaded NUnit Tests (found in the csharp/ICT/Testing/lib/Common/DB/tests.Multithreading.cs class file) and through those new NUnit Tests probing what ought to work, finding out through those what didn't work, and then addressing the bugs that prevented what ought to have worked.

Details of the Implementation

Requirements For Safe Multi-threaded DB Access

TODO

Writing (Library) Methods That Can Work With the Default DB Connection or a Separate DB Connection

When Such a Method Has Got a TDBTransaction instance available

TODO

When Such a Method Has NOT Got a TDBTransaction instance available

TODO

TODO

TODO

Extra Things that You Need to Know

Automatic (and Fully Transparent): Multi-threading and multi-connection 'Sanity Checks' in the TDataBase Class

TODO

Locking of DB Tables

TODO

Debugging

An option for very verbose logging got introduced that is helpful especially for pinning-down problems in multi-threading and multi-connection situations. To switch it on, turn the logging level ('DebugLevel') of the Server to 11 in the servers' config file.

Extra information you will get in the log files when it is turned on:

• unique DB Connection Identifiers (GUIDs) for each DB Connection;
• unique DB Transaction Identifiers (GUIDs) for each DB Transaction and the DB Connection Identifier of the DB Connection which the DB Transaction got started with;
• In case of GetNewOrExistingTransaction / GetNewOrExistingAutoTransaction commands if 'piggy-backing' on an existing DB connection occurs:
  • the DB Transaction Identifier (GUID) of the DB Transaction it is 'piggy-backing' on;
  • IsolationLevel of the DB Transaction it is 'piggy-backing' on;
  • Thread and AppDomain in which the DB Transaction that it is 'piggy-backing' on got started;
  • DB Connection Identifier (GUID) of the DB Connection that originally started the DB Connection;
  • Thread and AppDomain in which the GetNewOrExistingTransaction / GetNewOrExistingAutoTransaction command got called.

Tip: If the options to name DB Connections and DB Transactions are used then the name of each named DB Connection and named DB Transaction is included in the logging, too!

Caveat: This logging is indeed very verbose and hence will slow down execution of the program considerably. It is therefore conceivable that multiple threads will run with different timings when logging is turned up that much than if logging wouldn't be turned up that much...

TODO

Multi-threading: General Advice

General caution/advice: Programming with multiple threads is always much, much harder than programming with a single thread. When multiple threads access the same program data (Fields, static Fields, structs, etc.) one can easily get intermittent problems which can be very hard to pin-point and to resolve. Extreme diligence needs to be employed when multiple threads should access some shared program data in a writing fashion! Debugging a multi-threaded program is also considerably more difficult than debugging a single-threaded program. Whole books are devoted to the topic of multi-threaded programming as multi-threading is a quite difficult discipline that is hard to master...

Questions?

In case you have questions regarding working with multiple DB Connections please contact ChristianK, the developer that fixed the issues that prevented it from working in the past, introduced the advanced multi-threading and multi-connection 'sanity checks' in the TDataBase Class and who wrote this documentation.