Co-ordinated DB Access (Thread-safe DB Access)

THIS PAGE IS WORK IN PROGRESS

This page is currently being worked on. Please come back later to this page when you have been notified that it is finished!

Co-ordinated DB Access: Overview

The Problem it Solves

Before we had Co-ordinated DB Access in place, users ran into various Exceptions when multi-threaded DB access occurred - no matter whether that multi-threaded DB access was done intentionally/deliberately by the programmer, or whether it happened as something that resulted 'accidentally' because of an action the user took (and which we didn't prevent from happening). Co-ordinated DB Access not only prevents that from happening, but also provides new options to safely run program code that access the DB 'in parallel'.

The Solution

The primary solution was to make the TDataBase Class (OpenPetra's Database Access and Abstraction Layer) thread-safe (this got addressed by solving Bug #3852), meaning that we are employing pseudo-parallel Execution execution to prevent 'collisions' on DB Access. That in itself prevented the mentioned Exceptions from happening!

Building on that, ...

• provisions have been put in place to allow the OpenPetra software engineers to react programmatically to various new situations where the now co-ordinated DB Access can raise specific Typed Exceptions in case the desired 'parallel-ity' cannot be achieved automatically in a given situation;
• provisions have been made in the client-side 'Unhandled Exception Handler' to automatically show 'friendly and helpful' messages to the user when the software engineers didn't react programmatically to various new situations (yet).

It is expected that the automatic 'friendly and helpful' messages may well be enough for many situations - the ability to react programmatically to various new situations is merely there to allow for 'better' provisions for the user, e.g. to give Retry/Cancel options, or to prevent the user from taking certain actions in the first place that could (later) lead to the inability to take certain actions (e.g. disallowing the opening of a screen under certain circumstances because the circumstances would mean that any entered data could not be saved by the user later on).

Pseudo-parallel Execution

Pseudo-parallelism Definition

What happens is that we allow only one Thread at any given time access to DB-related functionality that is exposed through the TDataBase Class - that means that other Threads need to wait until one Thread is finished. Sadly we can't offer 'true' parallelism, the reasons for that are:

• ADO.NET doesn't support this;
• PostgreSQL allows only one running DB Transaction per DB Connection.

Details of the Implementation

What is Done Automatically And What Needs to be Handled Manually

Automatic (and Fully Transparent): Thread-safe DB Access through the TDataBase Class

Automatic Thread-Safety

The thread-safety is fully transparent to the Software Engineer, that is, the software engineer doesn't need to do anything to make sure it works, and doesn't even know how it works.

(For the curious: Thread-safety is achieved by using a SemaphoreSlim Object (FCoordinatedDBAccess), two Methods that 'flip' the switch between protection/non-protection (WaitForCoordinatedDBAccess and ReleaseCoordinatedDBAccess) and by calling those two Methods appropriately in all places where it is required to achieve thread-safety across everything the TDataBase Class can do.)

Automatic Timeout (Stalling/Deadlock Protection)

If the first thread that is to take out an 'exclusive lock' on the TDataBase</code< Class to prevent other threads from doing something with the DB in parallel should run for a very long time, or even 'stall', then all other threads of a user that were waiting for DB Access would 'stall' too. This not only would give the user the impression that 'OpenPetra isn't responding/has crashed', but would also mean they couldn't save work. To prevent that a timeout is in place. This timeout applies for the 'next' thread that wants to use TDataBase to become available and has to wait. TODO Server.DBWaitingTimeForCoordinatedDBAccess

Configuration (Optional): Timeout

TODO

Semi-Automatic: (DB-)Call Retries

TODO

Configuration (Optional): Number of Retries

TODO

Automatic: Exception Handling in Case of Timeout

TODO

Examples

TODO

Manual: Exception Handling in Case of Timeout

TODO

Examples / Implementations

TODO

Challenges

TODO

The Future: A Safe-to-use, Multi-threading Enabled OpenPetra

TODO