Working with multiple DB Connections: Difference between revisions
No edit summary |
|||
(56 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
==Working with multiple DB Connections: Overview== | ==Working with multiple DB Connections: Overview== | ||
===The Limitations That it Overcomes=== | ===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: | * ''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 | ** Each Thread must acquire a DB Transaction with the GetNewOrExistingXXXXTransaction Methods as it usually not known which of the Threads will start the DB Transaction first and which one will 'piggy-back on it'; | ||
** Two Threads cannot take out/piggy-back on a DB Transaction when the IsolationLevels that are required don't match (as the Threads are | ** Two Threads cannot take out/piggy-back on a DB Transaction when the IsolationLevels that are required don't match (as the Threads are ''sharing the same DB Transaction'' in a thread-safe way); | ||
** | ** 'Parallel' DB access is not achievable (as the Threads are ''sharing the same DB Transaction'' in a thread-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; | ** 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.) | * Historically, only a single DB Connection per connected OpenPetra 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 | ** While an OpenPetra software engineer could make an attempt to open a separate DB Connection, start a DB Transaction on that separate DB Connection and try to run DB commands against that DB Connection this failed with a whole variety of Exceptions due to several bugs in the 'OpenPetra DB Access and Abstraction Layer' (in the <code>TDataBase</code> Class and related Classes). | ||
From trunk commit Rev. | From trunk commit Rev. 2989 (September 28th, 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 ( | * reliably open (a) DB Connection(s) that is/are truly separate from the 'Default DB Connection' which exists for each Client connection (opening of DB Connections is OK even when it should occur in parallel in multiple Threads!); | ||
* reliably start a new DB Transaction with the | * reliably start a new DB Transaction with the BeginXXXXTransaction commands (or GetNewOrExistingXXXXTransaction 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; | * 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'); | * Commit or Rollback such a DB Transaction completely independent from DB Transactions on other DB Connections (also from the 'Default DB Connection'); | ||
* start and end an unlimited number of DB Transactions on such a separate DB Connection; | |||
* Close such a separate DB Connection completely independent from 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'). | ||
''True parallel DB Access from multiple Threads where it is desired/needed has become possible!'' | |||
===The Solution=== | ===The Solution=== | ||
The primary solution was to find bugs in the <code>TDataBase</code> 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. | The primary solution was to find bugs in the <code>TDataBase</code> 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 <code>TDataBase</code> 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. | This was done by extending our existing NUnit Tests for the <code>TDataBase</code> 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. (The feature Bug for this solution is Bug [https://tracker.openpetra.org/view.php?id=4364 4364]). | ||
==Relation to [[Co-ordinated DB Access (Thread-safe DB Access)]]== | |||
The ability to work with multiple parallel DB Connections does not away with the need for the [[Co-ordinated DB Access (Thread-safe DB Access)]] approach. The latter is needed everywhere where we don't deliberately start a new Thread for parallel DB Access (that is, in most situations in OpenPetra)! Rather, the ability to work with multiple parallel DB Connections opens up the way of true parallel DB command execution, and not just pseudoparallel execution, and for independent DB Transactions (both independent in their IsolationLevels and the timings of starting, committing and rolling back). | |||
==Details of the Implementation== | ==Details of the Implementation== | ||
=== Requirements For Safe Multi-threaded DB Access=== | ===Requirements For Safe Multi-threaded DB Access=== | ||
''' | ====Introduction==== | ||
'''In every 'deliberate multi-threading scenario' (that is, a scenario that we create on purpose and which we control) in which some form of DB Access is required, the creation of a private, independent <code>TDataBase</code> instance and working solely with that instance (and with ''no'' other <code>TDataBase</code> instance!) throughout the Threads' lifetime is ''required''.''' | |||
The reason for this is that ADO.NET providers (and specifically the PostgreSQL ADO.NET Provider 'npgsql') are not thread-safe. | |||
To be able to find violations of those rules early, our <code>TDataBase</code> Class blocks attempts to take out / work with parallel DB Transactions and raises specific Typed Exceptions (see [[Working with multiple DB Connections#Automatic:_Multi-threading_and_multi-connection_'Sanity_Checks'_in_the_TDataBase_Class |Automatic: Multi-threading and multi-connection 'Sanity Checks' in the TDataBase Class]]). Whenever the <code>TDataBase</code> Class raises such an Exception, various information is logged in the server log file, too. (See [[Working with multiple DB Connections#Verbose_Logging|Verbose Logging]] for influencing the verbosity of the logging information.) Would such Exceptions not be raised, a whole variety of Exceptions from various sources would be thrown 'further down the line' when parallel DB access through the same <code>TDataBase</code> instance would be attempted (see Bug [https://tracker.openpetra.org/view.php?id=4364| 4364]). | |||
====Rules That Need to be Adhered to==== | |||
* Server-side code that runs in a Thread that got started in a 'deliberate multi-threading scenario' (that is, a scenario that we create on purpose and which we control) must perform any DB access only through an instance of <code>TDataBase</code> that got created in that Thread (see next paragraph)! | |||
* Server-side code that doesn't run in a deliberately started Thread: | |||
** If the server-side code got invoked from the client side it ''DOES get run in a separate Thread'' which .NET Remoting creates for us 'under the hood'! When that server-side code access the DB it usually uses the 'globally available' <code>DBAccess.GDBAccessObj</code> <code>TDataBase</code> instance. | |||
*** [[Co-ordinated DB Access (Thread-safe DB Access)]] will be at work in this case and will provide pseudoparallel thread-safe DB access to that <code>TDataBase</code> instance. | |||
=====Server-side Code That Runs in a Deliberately Started Thread===== | |||
''The creation of a private, independent <code>TDataBase</code> instance and solely working with that from within that Thread throughout the Thread's lifetime is required.'' | |||
In any deliberately started Thread, ... | |||
* Create a private, independent <code>TDataBase</code> instance and store a reference to it in a Variable. If you store a reference to it in a Field you must make sure it isn't accessed from anywhere outside of that Thread, or if that should be needed, it must be accessed in a completely thread-safe manner. | |||
* Open a private, independent DB Connection for this Thread by calling the <code>EstablishDBConnection</code> Method on that Thread's <code>TDataBase</code> instance. | |||
** Supply a unique name for the DB Connection with the optional '<code>AConnectionName</code>' Argument of the <code>EstablishDBConnection</code> Method as this can be a big help in debugging (see [[Working with multiple DB Connections#Verbose_Logging|Verbose Logging]])! | |||
** Example: \csharp\ICT\Petra\Server\lib\MCommon\Main.cs, Method 'EstablishDBConnection'. | |||
* Start a private, independent DB Transaction on that Thread's <code>TDataBase</code> instance. | |||
** You can use any of our usual methods for that and you can choose any IsolationLevel! | |||
** Supply a unique name for the DB Transaction with the optional '<code>ATransactionName</code>' Argument of the Method you are using for this as this can be a big help in debugging (see [[Working with multiple DB Connections#Verbose_Logging|Verbose Logging]])! | |||
** An arbitrary number of private, independent DB Transactions can be started and ended on that Thread's <code>TDataBase</code> instance! | |||
* Run an arbitrary number of arbitrary SQL Commands on the private, independent DB Transaction(s) on that Thread's <code>TDataBase</code> instance. | |||
* Commit or Rollback the private, independent DB Transaction(s) on that Thread's <code>TDataBase</code> instance. | |||
* Close the private, independent DB Connection by calling <code>CloseDBConnection</code> on that Thread's <code>TDataBase</code> instance. | |||
That way, that specific Thread that needs to execute commands against the DB is completely independent of what any other running Thread(s) might be doing in any (of their) DB Transaction(s) (with whatever IsolationLevel) against the same DB (using the common <code>DBAccess.GDBAccessObj</code> or its/their own separate <code>TDatabase</code> instance) at roughly the same time! However, a limiting factor for the 'data throughput' and the parallelism achieved could be [[Working with multiple DB Connections#Locking_of_DB_Tables| DB Table Locks]] as they could impede multi-threading performance and parallelism. | |||
=== | ===Changing (Library) Methods so That They Can Work With the Default DB Connection or a Separate DB Connection=== | ||
====When Such a Method Has Got a <code>TDBTransaction</code> instance available==== | ====When Such a Method Has Got a <code>TDBTransaction</code> instance available==== | ||
''' | Replace any occurrence of <code>DBAccess.GDBAccessObj</code> with a call to <code>DBAccess.GetDBAccessObj(ATransaction)</code>, passing the reference of the <code>TDBTransaction</code> instance that the Method has got available. | ||
The overloaded <code>DBAccess.GetDBAccessObj(...)</code> Method acts as a 'set of points' (using figurative railway language here) and will yield the 'globally available' <code>DBAccess.GDBAccessObj</code> in case a null value for the <code>ATransaction</code> gets passed, otherwise it will yield the <code>TDataBase</code> instance which started the DB Transaction that got passed in in its <code>TDBTransaction ATransaction</code> Argument. | |||
====When Such a Method Has NOT Got a <code>TDBTransaction</code> instance available==== | ====When Such a Method Has NOT Got a <code>TDBTransaction</code> instance available==== | ||
''' | * Either... | ||
** add a new optional Argument that allows the passing-in of a <code>TDataBase</code> instance; | |||
*** you are encouraged to use '<code>TDataBase ADataBase = null</code>' at the very end of the Method's argument list for the sake of consistency; | |||
** or add a new optional Argument that allows the passing-in of a <code>TDBTransaction</code> instance; | |||
*** you are encouraged to use '<code>TDBTransaction ATransaction = null</code>' at the very end of the Method's argument list for the sake of consistency. | |||
* Then replace any occurrence of <code>DBAccess.GDBAccessObj</code> with a call to either <code>DBAccess.GetDBAccessObj(ADataBase)</code> or <code>DBAccess.GetDBAccessObj(ATransaction)</code>. | |||
The overloaded <code>DBAccess.GetDBAccessObj(...)</code> Method acts as a 'set of points' (using figurative railway language here) and will yield the 'globally available' <code>DBAccess.GDBAccessObj</code> in case a null value for the <code>ADataBase</code> or <code>ATransaction</code> gets passed, otherwise it will either yield the <code>TDataBase</code> instance passed in in its <code>TDataBase ADataBase</code> Argument, or the <code>TDataBase</code> instance which started the DB Transaction that got passed in in its <code>TDBTransaction ATransaction</code> Argument (depending on which overload of the Method is used). | |||
====Caveat: Public WebConnector or UIConnector Methods - Beware of Introducing Dependency on DBAccess.cs in Interfaces ==== | |||
If you add one of the two Arguments to an existing or a new Method (either '<code>TDataBase ADataBase</code>' or '<code>TDBTransaction ATransaction = null</code>') and the Method is a <code>public</code> Method in a WebConnector or UIConnector you will get a compiler error like this: | |||
c:\openpetraorg\trunk\csharp\ICT\Petra\Shared\lib\Interfaces\Partner.Interfaces- | |||
generated.cs(819,57): error CS0246: The type or namespace name 'TDBTransaction' could not be | |||
found (are you missing a using directive or an assembly reference?) | |||
This is because Methods that end up in Interfaces must not have Arguments that reference anything in DBAccess.cs (and both <code>TDataBase</code> and <code>TDBTransacion</code> are defined in that Class File). The solution to this looks like this: | |||
# Duplicate the method in code and make the original Method <code>private</code> (or, alternatively <code>internal</code>) and remove any '<code>RequireModulePermission</code>' Attribute; | |||
# In the new, <code>public</code> copy of the Method you remove the newly introduced Argument again and simply call the private Method, passing null for the Argument; | |||
# run <code>nant quickClean generateSolution</code> to generate the 'glue' again and have it compiled. | |||
Now it should work! | |||
====Standardised XML Comments==== | |||
For the sake of consistency you are encouraged to use the following XML Comments when adding an Argument as described above to existing Methods: | |||
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null | |||
/// gets passed then the Method executes DB commands with the 'globally available' | |||
/// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this | |||
/// Argument!</param> | |||
and | |||
/// <param name="ATransaction">An instantiated <see cref="TDBTransaction" /> object, or null (default = null). | |||
/// If null gets passed then the Method executes DB commands with the 'globally available' | |||
/// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the the <see cref="TDataBase" /> instance | |||
/// which started the DB Transaction that gets passed in with this Argument!</param> | |||
====Some C# Files That Likely Contain Methods That Could Benefit From Being Changed to Work With the Default DB Connection or a Separate DB Connection==== | |||
''MFinance\Common'' | |||
* Common.Tools.*.cs | |||
* ServerLookups.cs | |||
''MFinance\data'' | |||
* DataAggregates.cs | |||
''MFinance\Setup'' | |||
* GL.Setup.cs | |||
* GL.AccountingPeriods.cs | |||
''MFinance\GL'' | |||
* GL.Transactions.cs | |||
* Reporting.GiftReports.UIConnector.cs | |||
* Reporting.UIConnector.cs | |||
''MFinance\AP'' | |||
* AP.Find.cs | |||
''MPartner\Common'' | |||
* DataAggregates.ContactDetails.cs | |||
* Mailing.cs | |||
* ServerLookups.cs | |||
''MPartner\web'' | |||
* AddressTools.WebConnector.cs | |||
''MReporting'' | |||
MReporting\M*\Functions.cs | |||
''MReporting.connect'' | |||
* UIConnector.cs - KEY to making XML Reports run on parallel DB connections and DB transactions! | |||
''*\ServerLookups.DataReader.cs'' | |||
'' | --> see example in: csharp/ICT/Petra/Server/lib/MCommon/ServerLookups.DataReader.cs | ||
''* *.Cacheable.ManualCode.cs'' | |||
--> see example in: csharp/ICT/Petra/Server/lib/MPartner/Partner.Cacheable.ManualCode.cs | |||
This list is not exhaustive! | |||
===Writing new (Library) Methods That Can Work With the Default DB Connection or a Separate DB Connection=== | |||
Refer to the section [[Working with multiple DB Connections#Changing_(Library)_Methods_so_That_They_Can_Work_With_the_Default_DB_Connection_or_a_Separate_DB_Connection|Changing (Library) Methods so That They Can Work With the Default DB Connection or a Separate DB Connection]] on how to achieve this and use the overloaded <code>DBAccess.GetDBAccessObj(...)</code> Method to work with either the Default DB Connection or a separate DB Connection right from the start. | |||
==Extra Things that You Need to Know== | ==Extra Things that You Need to Know== | ||
=== | ===Using 'Central Server-side' Methods With Separate DB Connections=== | ||
''' | ====Using Methods of our <code>TTypedDataAccess</code> Class and Auto-generated 'Typed DataStore' Methods With Separate DB Connections==== | ||
* '''<code>TTypedDataAccess</code> Class''': All Methods of the <code>TTypedDataAccess</code> Class that access the DB now work with the DB Connection that started the DB Transactions that gets passed into those Methods. That means that you can utilise those Methods also on separate DB Connections that got started on a separate Thread. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
* '''Auto-generated 'Typed DataStore' Methods''': All Methods of the auto-generated 'Typed DataStore' now work with the DB Connection that started the DB Transactions that gets passed into those Methods. That means that you can utilise those Methods also on separate DB Connections that got started on a separate Thread. Example for such a Method: <code>PPartner.LoadByPrimaryKey(...)</code> in Partner.Access-generated.cs. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
* '''Auto-generated <code>SubmitChanges</code> Methods of 'Typed DataSets'''': All these auto-generated Methods now have an optional Argument <code>TDataBase ADataBase = null</code>. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
* '''Auto-generated <code>GetCacheableRecordReferenceCount</code> / <code>GetNonCacheableRecordReferenceCount</code> Methods''': All these auto-generated Methods now have an optional Argument <code>TDataBase ADataBase = null</code>. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
* '''Auto-generated server-side code for Cacheable DataTables''': All server-side Methods of the auto-generated Cacheable DataTable code now have an optional Argument <code>TDataBase ADataBase = null</code>. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
* '''*Cacheable.ManualCode.cs files''': The hand-written Methods in such files ''need to be adapted manually'' so that the DB Connection that started <code>ATransaction</code> runs the commands and no longer the hard-coded DBAccess.GDBAccessObj's DB Connection! Example: \csharp\ICT\Petra\Server\lib\MPartner\Partner.Cacheable.ManualCode.cs. | |||
====Using <code>TCommonDataReader.GetData</code> and <code>SaveData</code> Methods With Separate DB Connections==== | |||
An overload has been added to both the <code>TCommonDataReader.GetData</code> and <code>TCommonDataReader.SaveData</code> Methods. That overload has got an Argument <code>TDataBase ADataBase</code>. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>) | |||
====Using <code>TSequenceWebConnector.GetNextSequence</code> With Separate DB Connections==== | |||
An overload of the <code>TSequenceWebConnector.GetNextSequence</code> Method has got an optional Argument <code>TDataBase ADataBase</code>. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (That Method previously always used <code>GDBAccess.DBAccessObj</code>.) | |||
====Using <code>TPagedDataSet.ExecuteQuery</code> With Separate DB Connections==== | |||
The <code>TPagedDataSet.ExecuteQuery</code> Method now has an optional Argument <code>TDataBase ADataBase = null</code>. Pass in the separate DB Connection that got started on a separate Thread to make that Methods execute on that DB Connection. (The <code>ExecuteFullQuery</code> Method that it calls was previously hard-coded to always use <code>GDBAccess.DBAccessObj</code>.) | |||
===Using FastReport Reports Server-side Code With Separate DB Connections=== | |||
To avoid problems with DB access that may be happening concurrently, reports based on FastReport should be converted to run on a separate DB Connection. To do so, the Report must use an instance of <code>TReportingDbAdapter</code> created with the Argument '<code>ASeparateDBConnection</code>' set to true. By default the <code>TReportingDbAdapter</code> will execute all DB commands on the 'globally available' DB Connection! | |||
Before converting a given FastReport Report to the new 'separate DB Connection' style, the Report Method must be checked to ensure that all the database access methods it uses are explicitly given the database connection of the <code>TReportingDbAdapter</code>, rather than being allowed to use the 'globally available' DB Connection. This is done by 'scanning' all program code that gets run inside the Report Method - and in any library methods that it calls - for occurrences of <code>DBAccess.GDBAccessObj</code>. In case such occurrences are found... | |||
* inside the Report Method: these need to be replaced with <code>DbAdapter.FPrivateDatabaseObj</code>; | |||
* in any library methods: these Methods need to be changed to allow them to either run their DB Commands on the 'globally available' DB Connection or on a separate DB Connection (see [[Working with multiple DB Connections#Changing_.28Library.29_Methods_so_That_They_Can_Work_With_the_Default_DB_Connection_or_a_Separate_DB_Connection |here]] for how to do this). | |||
Once a FastReport Report got checked and potentially changed it is a good idea to turn on [[Working with multiple DB Connections#Verbose_Logging|Verbose Logging]] and to run the Report in all possible variations that might result in varying DB Commands to be run (due to different Report Options). Scrutinise the Server.log file after that Report comes up on screen and check that all DB commands ''really ran only on the separate DB Connection''. Once you have established this you will know that this particular FastReport truly runs with a separate DB Connection! | |||
===Automatic: Multi-threading and multi-connection 'Sanity Checks' in the <code>TDataBase</code> Class=== | |||
ADO.NET providers (and specifically the PostgreSQL ADO.NET Provider 'npgsql') are not thread-safe. Guidance on how to make sure that DB access is done in a way that is thread-safe is given in the section [[Working with multiple DB Connections#Requirements_For_Safe_Multi-threaded_DB_Access |Requirements For Safe Multi-threaded DB Access]]. In case attempts are made that would mean that non-thread-safe DB access would occur, the <code>TDataBase</code> Class blocks attempts to take out / work with parallel DB Transactions and raises specific Typed Exceptions. This is done because finding violations of those rules early is much better than to run into various Exceptions from various sources 'further down the line', which can be very obscure. | |||
These checks that the <code>TDataBase</code> Class employs are fully automatic and can't be switched off. They are 'invisible' as long as multi-threading-safe DB access is not at risk. However, in case DB access occurs that is not thread-safe, these checks will log information to the server log file (independent of the set 'DebugLevel' of the Server) and will throw specific Typed Exceptions. The following list shows these Exceptions and links to the FAQ section where the reason as to why a certain Exception gets raised is answered. | |||
====Typed Exceptions That Can be Raised in Multi-threading DB Access Scenarios==== | |||
Note: 'FAQ 1' next to one Exception doesn't necessarily point to the same FAQ than 'FAQ 1' next to another Exception. | |||
* <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code>: [[Working with multiple DB Connections#Instead_of_opening_a_new_DB_Connection_for_.27Thread_A.27:_Could_I_not_simply_.27make_a_peek.27_to_see_if_a_DB_Transaction_is_running_on_a_given_DB_Connection_in_another_Thread_and_if_so.2C_use_that.3F |FAQ 1]], [[Working with multiple DB Connections#Could_calling_GetNewOrExistingXXXTransaction_every_give_.27Thread_A.27_a_running_DB_Transaction_from_another_Thread.3F |FAQ 2]], [[Working with multiple DB Connections#Could_.27Thread_A.27_.28accidentally.29_Commit_or_Rollback_a_DB_Transaction_that_got_started_on_another_Thread_.28and_vice_versa.29.3F |FAQ 3]] | |||
* <code>EDBAttemptingToCreateCommandThatWouldRunCommandOnDifferentThreadThanThreadOfTheTransactionThatGotPassedException</code>: [[Working with multiple DB Connections#Instead_of_opening_a_new_DB_Connection_for_.27Thread_A.27:_Could_I_not_simply_.27make_a_peek.27_to_see_if_a_DB_Transaction_is_running_on_a_given_DB_Connection_in_another_Thread_and_if_so.2C_use_that.3F |FAQ 1]] | |||
* <code>EDBAttemptingToCreateCommandOnDifferentDBConnectionThanTheDBConnectionOfOfTheDBTransactionThatGotPassedException</code>: [[Working with multiple DB Connections#Could_.27Thread_A.27_.28accidentally.29_execute_a_DB_Command_that_it_enlists_in_a_DB_Transaction_of_its_own_on_a_DB_Connection_that_is_owned_by_another_Thread.3F |FAQ 1]] | |||
* <code>EDBAttemptingToCloseDBConnectionThatGotEstablishedOnDifferentThreadException</code>: [[Working with multiple DB Connections#Could_.27Thread_A.27_.28accidentally.29_close_a_DB_Connection_that_is_owned_by_another_Thread.3F |FAQ 1]] | |||
All these Typed Exceptions derive from one 'base' Exception, <code>EDBAccessLackingCoordinationException</code>. | |||
(Typed Exceptions that are raised by the [[Co-ordinated DB Access (Thread-safe DB Access)]] feature derive from the same Exception.) | |||
The client-side 'Unhandled Exception Handler' presents 'stock messages' to users in case an Exception that derives from the <code>EDBAccessLackingCoordinationException</code> Exception makes it as far as that (that is, if a programmer didn't catch it server-side or client-side). This is a much better option than letting the Exception escalate and showing the Unhandled Exception Dialog as a result of that. | |||
In case any of the Exceptions listed above occur and make it that far, the MessageBox that is shown to the user presents a message that contains the text ''"Reason: Parallel data access could not be performed."''. Should a user get this message it is a clue for us software engineers that an attempt to work with multiple Threads on DB Connections (either deliberately or accidentally) has happened and that it wasn't co-ordinated sufficiently/done properly. In that case the Server Log should be consulted for details. Additionally, when the appSetting '<code>Client.DebugLevel</code>' is set to a minimum of 3 in the Client.config file the Unhandled Exception handler also logs the full Exception details to the Client.log. ''This is useful for detailed checks by the developer''. At that DebugLevel the developer also gets information in the Client.log about the various retry attempts, and if they were exceeded, for cases where screens handle those using the in an automatic retry fashion by means of the <code>TServerBusyHelper.CoordinatedAutoRetryCall</code> Method (see next paragraph). | |||
=====Performing Automatic Retries When <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code> Gets Thrown===== | |||
The <code>CoordinatedAutoRetryCall</code> Method of the <code>TServerBusyHelper</code> got extended to handle that specific Exception and to perform automatic retries should it occur. This works in the same way as it does with the [[Co-ordinated DB Access (Thread-safe DB Access)#Utilising_the_TServerBusyHelper.CoordinatedAutoRetryCall_Method |Co-ordinated DB Access (Thread-safe DB Access)]]. | |||
That manual approach can be used where 'hot spots' of multi-threading DB activity are known and can serve as a mitigation when <code>GetNewOrExistingXXXTransaction</code> Methods are used - either until those 'hot spots' are fixed, or 'forever' (the latter option is not ideal, though!). Any code sections that already utilise the <code>CoordinatedAutoRetryCall</code> Method benefit from this automatically! | |||
===Locking of DB Tables=== | ===Locking of DB Tables=== | ||
''' | A 'side effect' of multiple Threads having their own DB Connections and running DB Transactions independently from each other and DB Commands potentially in parallel is that ''DB Locks'' are taken out, ''potentially in parallel''. For the RDBMS this scenario is essentially the same as the scenario where multiple OpenPetra users are connected (each having their own 'globally available' DB Connection) and each of them running DB Transactions and DB Commands independently and potentially in parallel. In both scenarios ''DB Locks are taken out, according to the IsolationLevels of the DB Transactions involved'', and in both situations DB Locking needs to be taken into account. | ||
'''In the scenario where multiple Threads run DB Transactions and in which DB Commands can potentially get executed in parallel we need to get used to thinking about DB Locking''' and the effects it can have, for the other scenario (multiple users having separate DB Connections) that thinking should be ingrained in us already. | |||
One effect of DB Locking that may well be felt when multiple Threads run multiple DB Commands in parallel is that the DB Locking might reduce the performance of the multiple DB-accessing Threads because the concurrency that the multiple Threads provide might be impeded by DB Locking. In the worst case DB Locks could reduce the outcome of our multi-Threading attempts to '''serial processing instead of parallel processing''', resulting in what could essentially look like the outcome of [[Co-ordinated DB Access (Thread-safe DB Access)]] because the RDBMS might allow only one Thread to perform DB queries at once due to the DB Tables involved and DB Locking being at work!!! | |||
===Debugging=== | ===Debugging=== | ||
====Verbose Logging==== | |||
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. | 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: | 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; | ||
* | * For <code>BeginTransaction</code> / <code>BeginAutoTransaction</code>: | ||
** IsolationLevel of the new DB Transaction (if it was started with a specific IsolationLevel); | |||
** DB Transaction Identifier (GUID) of the new DB Transaction; | |||
** DB Connection Identifier (GUID) of the DB Connection that started the DB Transaction; | |||
** StackTrace at the point of DB Transaction start and the Thread and AppDomain in which the DB Transaction got started; | |||
* In case of <code>GetNewOrExistingXXXXTransaction</code> commands ''if'' 'piggy-backing' on an existing DB Connection occurs: | |||
** the DB Transaction Identifier (GUID) of the DB Transaction it is 'piggy-backing' on; | ** the DB Transaction Identifier (GUID) of the DB Transaction it is 'piggy-backing' on; | ||
** IsolationLevel 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; | ** 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 | ** DB Connection Identifier (GUID) of the DB Connection that originally started the DB Transaction; | ||
** Thread and AppDomain in which the <code> | ** Thread and AppDomain in which the <code>GetNewOrExistingXXXXTransaction</code> command got called. | ||
* For <code>CommitTransaction</code> or <code>RollbackTransaction</code> the following gets logged ''after'' the command got executed, but with information that was gathered ''before'' the command executed: | |||
** the DB Transaction Identifier (GUID) of the DB Transaction; | |||
** whether the DB Transaction was still valid (i.e. not already Committed / Rolled back); | |||
** IsolationLevel of the DB Transaction; | |||
** whether the DB Transaction was reused; | |||
** Thread and AppDomain in which the DB Transaction got started; | |||
** Thread and AppDomain in which the <code>CommitTransaction</code> or <code>RollbackTransaction</code> command got called. | |||
* For any DB Command that gets executed: | |||
** Thread and AppDomain in which the DB Command gets executed; | |||
** DB Transaction Identifier (GUID) of the DB Transaction that the DB Command is enlisted in; | |||
** DB Connection Identifier (GUID) of the DB Connection that originally started the DB Transaction that the DB Command is enlisted in. | |||
Tips: | |||
# If the (new) 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! This is helpful as GUIDs - though they are unique - are not very readable. | |||
# This very verbose logging is '''extremely helpful''' when one wants to ascertain that a certain function in OpenPetra, e.g. a Report, Extract, etc. (including any DB access that any library/helper functions that it might call!) ''really'' performs ''every'' DB access it does on the separate DB Connection in a separate Thread that you purposefully created for it! Method: | |||
## Turn the very verbose logging on before executing the function in OpenPetra; | |||
## Scrutinise the log output after the execution of the function has finished. Check whether all DB Commands that it ran were indeed run only on the separate DB Connection and on the separate Thread. (Naming of DB Connections and DB Transactions pays off here!) | |||
## This is quite some work but the only proof that things are indeed happening as they ought to! | |||
''Caveat'': This logging is indeed very verbose and hence will slow down execution of the program. 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... | |||
====Finding Out Which Queries Are Run (in Parallel) on Which RDBMS Connection (with PostgreSQL)==== | |||
It can be quite helpful to see from the RDBSMs' end what queries are running against which DB Connection. For PostgreSQL this is possible by issuing the following SQL query: | |||
PostgreSQL before version 9.2: | |||
SELECT datname, usename, application_name, procpid, client_addr, client_port, waiting, query_start, current_query | |||
FROM pg_stat_activity | |||
WHERE datname='demo' AND usename='petraserver' AND current_query LIKE '%in transaction' | |||
ORDER BY procpid; | |||
PostgreSQL from version 9.2 onwards: | |||
SELECT datname, usename, application_name, pid, client_addr, client_port, waiting, query_start, query | |||
FROM pg_stat_activity | |||
WHERE datname='demo' AND usename='petraserver' AND state LIKE '%in transaction%' | |||
ORDER BY pid; | |||
'' | Replace 'demo' with the name of the DB that the PetraServerConsole instance is connected to and run this query in pgAdmin III repeatedly (press <F5> for that) ''while the function of OpenPetra that you want to monitor for its DB connection use is executing''. | ||
How to read the output of that query: PostgreSQL starts a separate DB Server process for each DB connection. Each DB Server process has got its own 'pid' (Process Identification). This means that if several DB commands are executing in parallel on several DB connections you will get a list of all the processes that are currently running DB queries - one result line for each process/DB Connection (the 'query' column shows the SQL statement of the query that is currently running against that DB Connection). If you intended the function of OpenPetra to run solely on a specific DB connection and you are seeing multiple lines at times it isn't doing this! In that case refer to the section [[Working with multiple DB Connections#Verbose_Logging|Verbose Logging]]. | |||
====Finding out the Number of Currently Open DB Connections (with PostgreSQL)==== | |||
One can use the combination of the static Methods <code>TDataBase.ClearAllConnectionPools()</code> and <code>TDataBase.GetNumberOfDBConnections</code> to get the number of currently open DB connections on a RDBMS (using just the latter Method on its own is not reliable because of connection pooling)! Use the static Method <code>TDataBase.ClearConnectionPoolAndGetNumberOfDBConnections</code> to call both Methods at once. For an example on how to use this see Unit Tests in \csharp\ICT\Testing\lib\Common\DB\test.Multithreading.cs! | |||
===Multi-threading: General Advice=== | ===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... | 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.) - and indeed data from a DB - 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 - and indeed data that resides in a DB - 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... | ||
==FAQ's== | |||
===Instead of opening a new DB Connection for 'Thread A': Could I not simply 'make a peek' to see if a DB Transaction is running on a given DB Connection in another Thread and if so, use that?=== | |||
'''No.''' While it is possible to inquire the '<code>Transaction</code>' Property of a given <code>TDataBase</code> instance that one has got access to (e.g. of <code>DBAccess.GDBAccessObj</code>) (and that inquiry is thread-safe) and in doing so it is possible to find out whether a DB Transaction is running on that <code>TDataBase</code> instance (if the 'Transaction' Property yields null then no DB Transaction is running, otherwise there will be one running), ''it would not be safe to use that DB Transaction...'' | |||
* as there is no guarantee that the ''other'' Thread wouldn't be committing/rolling back that very DB Transaction any time (which would have unknown consequences for 'Thread A'!); | |||
* as it got started on another Thread, and ADO.NET is not thread-safe. | |||
To prevent that if such an attempt should happen / occur, the <code>TDataBase</code> class will throw... | |||
* an <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code> if one tries to 'piggy-back' on its running DB Transaction by calling <code>GetNewOrExistingXXXXTransaction</code> from a different Thread, and | |||
* an <code>EDBAttemptingToCreateCommandThatWouldRunCommandOnDifferentThreadThanThreadOfTheTransactionThatGotPassedException</code> if one tries to run a DB Command on a <code>TDBTransaction</code> instance that one obtained by using the <code>TDBTransaction</code> instance that is returned by the '<code>Transaction</code>' Property of a <code>TDataBase</code> instance which got created in another Thread. (The '<code>Transaction</code>' Property of the <code>TDataBase</code> Class hasn't got XML Comments that explicitly warn against doing anything on and with that Transaction for no reason...!) | |||
===Could calling <code>GetNewOrExistingXXXTransaction</code> ever give 'Thread A' a running DB Transaction from ''another'' Thread?=== | |||
'''No.''' When a Method in 'Thread A' calls <code>GetNewOrExistingXXXTransaction</code> it must do that on the <code>TDataBase</code> instance that got instantiated on this Thread otherwise a <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code> gets thrown. That way 'Thread A' will never (be able to) get a DB Transaction that belongs to a <code>TDataBase</code> instance of a different Thread ('Thread B') using any of the <code>GetNewOrExistingXXXTransaction</code> Methods! | |||
Conversely, 'Thread B' (e.g. an intentionally started 'worker thread') will never (be able to) get a DB Transaction with <code>GetNewOrExistingXXXTransaction</code> that belongs to a <code>TDataBase</code> instance of a different Thread ('Thread A', which might e.g. use the 'globally available' <code>DBAccess.GDBAccess</code> instance) because it must not call <code>GetNewOrExistingXXXTransaction</code> on the instance of 'Thread A' - otherwise a <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code> gets thrown! | |||
===Could calling <code>BeginXXXTransaction</code> ever give 'Thread A' a new DB Transaction from ''another'' Thread?=== | |||
'''''Yes''''', and you must make sure that this isn't happening by only ever calling <code>BeginXXXTransaction</code> on the separate instance of <code>TDataBase</code> that gets purposefully created for 'your' Thread! | |||
Reason why <code>TDataBase</code> needs to allow this and cannot not throw an Exception should that happen: If server-side code got invoked from the client side it ''DOES get run in a separate Thread'' which .NET Remoting creates for us 'under the hood'! When that server-side code accesses the DB it usually uses the 'globally available' <code>DBAccess.GDBAccessObj TDataBase</code> instance. That instance got created when the user logged in to the OpenPetra server, and it got created on a different Thread than the auto-created Thread that .NET Remoting manages. | |||
===Could 'Thread A' (accidentally) Commit or Rollback a DB Transaction that got started on ''another'' Thread (and vice versa)?=== | |||
'''No.''' We must not do this because ADO.NET is not thread-safe. To prevent that if such an attempt should happen / occur, the <code>TDataBase</code> class will throw an <code>EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException</code>. | |||
===Could 'Thread A' (accidentally) execute a DB Command that it enlists in a DB Transaction ''of its own'' on a DB Connection that is owned by ''another'' Thread?=== | |||
'''No.''' We must not do this because (1) ADO.NET is not thread-safe, (2) it really doesn't make sense, if you think about it what the RDBMS should do with it! To prevent that if such an attempt should happen / occur, the <code>TDataBase</code> class will throw an <code>EDBAttemptingToCreateCommandOnDifferentDBConnectionThanTheDBConnectionOfOfTheDBTransactionThatGotPassedException</code>. | |||
(The check that the <code>TDataBase</code> class performs is even more general: it simply disallows the execution of a Command that is to be enlisted in a DB Transaction that got created on a different DB Connection.) | |||
===Could 'Thread A' (accidentally) close a DB Connection that is owned by ''another'' Thread?=== | |||
'''No.''' We must not do this because ADO.NET is not thread-safe. To prevent that if such an attempt should happen / occur, the <code>TDataBase</code> class will throw an <code>EDBAttemptingToCloseDBConnectionThatGotEstablishedOnDifferentThreadException</code>. | |||
===What is the use of the <code>GetNewOrExistingXXXTransaction</code> Methods if They Cannot be Used When Multiple Threads are Involved?=== | |||
Calling one of the <code>GetNewOrExistingXXXTransaction</code> Methods permits 'nesting' of DB calls without the need of passing a reference to the running DB Transaction everywhere. This is especially handy for 'library-like' Methods, which might get called in situations where there is - or where there isn't - a DB Transaction running. | |||
Although this is very handy, that 'nesting' is only permitted if the chain of Methods are all called in the same Thread (i.e. all the Methods are in the same call stack) because ADO.NET isn't thread-safe! In a multi-threading scenario only the use of a separate DB Connection with a separate DB Transaction is acceptable - see [[Working with multiple DB Connections#Rules_That_Need_to_be_Adhered_to| Rules That Need to be Adhered to]]. | |||
===How can a 'library-like' Method be written that could utilise a running DB Transaction of a certain <code>TDataBase</code> instance if it is currently not busy, but would create its own DB Transaction on a separate, new <code>TDataBase</code> instance if it is busy?=== | |||
* While this is possible, this must be done | |||
** within the same Thread as ADO.NET is not thread-safe; | |||
** only when the IsolationLevel is exactly the one that is needed; | |||
** in a way that ensures that no other Thread could utilise the <code>TDataBase</code> instance that has started the DB Transaction in question while the DB access code in the 'library-like' Method runs. | |||
The <code>TDataBase</code> Class has got the Method <code>CheckRunningDBTransactionIsCompatible</code> that performs checks to make sure that the first conditions are met and returns 'true' only if they both are met. If you can ascertain that the third condition will ''always'' be met then you can use that Method and use the DB Transaction in question, otherwise the 'library-like' Method must create a separate, new <code>TDataBase</code> instance, establish a separate DB Connection and start a new DB Transaction. (You could use thread-synchronisation methods such as utilising a <code>ManualResetEvent</code> to ensure that the third condition will ''always'' be met - see how it is used in \csharp\ICT\Testing\lib\Common\DB\test.Multithreading.cs. In addition - and only in addition - you should call the <code>ConnectionReady</code> Method of the <code>TDataBase</code> instance that has started the DB Transaction in question to be sure that the DB Connection isn't busy at the moment.) | |||
==Questions?== | ==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 | 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 [[Working with multiple DB Connections#Automatic:_Multi-threading_and_multi-connection_.27Sanity_Checks.27_in_the_TDataBase_Class |multi-threading and multi-connection 'sanity checks']] in the <code>TDataBase</code> Class and who wrote this documentation. |
Latest revision as of 13:56, 3 December 2015
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 acquire a DB Transaction with the GetNewOrExistingXXXXTransaction Methods as it usually not known which of the Threads will start the DB Transaction first and which one will 'piggy-back on it';
- Two Threads cannot take out/piggy-back on a DB Transaction when the IsolationLevels that are required don't match (as the Threads are sharing the same DB Transaction in a thread-safe way);
- 'Parallel' DB access is not achievable (as the Threads are sharing the same DB Transaction in a thread-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 OpenPetra 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 on that separate DB Connection and try to run DB commands against that DB Connection 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).
- While an OpenPetra software engineer could make an attempt to open a separate DB Connection, start a DB Transaction on that separate DB Connection and try to run DB commands against that DB Connection this failed with a whole variety of Exceptions due to several bugs in the 'OpenPetra DB Access and Abstraction Layer' (in the
From trunk commit Rev. 2989 (September 28th, 2015) onwards it has become possible for OpenPetra software engineers to...
- reliably open (a) DB Connection(s) that is/are truly separate from the 'Default DB Connection' which exists for each Client connection (opening of DB Connections is OK even when it should occur in parallel in multiple Threads!);
- reliably start a new DB Transaction with the BeginXXXXTransaction commands (or GetNewOrExistingXXXXTransaction 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');
- start and end an unlimited number of DB Transactions on such a separate DB Connection;
- Close such a separate DB Connection completely independent from other DB Connections (also from the 'Default DB Connection').
True parallel DB Access from multiple Threads where it is desired/needed has become possible!
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. (The feature Bug for this solution is Bug 4364).
Relation to Co-ordinated DB Access (Thread-safe DB Access)
The ability to work with multiple parallel DB Connections does not away with the need for the Co-ordinated DB Access (Thread-safe DB Access) approach. The latter is needed everywhere where we don't deliberately start a new Thread for parallel DB Access (that is, in most situations in OpenPetra)! Rather, the ability to work with multiple parallel DB Connections opens up the way of true parallel DB command execution, and not just pseudoparallel execution, and for independent DB Transactions (both independent in their IsolationLevels and the timings of starting, committing and rolling back).
Details of the Implementation
Requirements For Safe Multi-threaded DB Access
Introduction
In every 'deliberate multi-threading scenario' (that is, a scenario that we create on purpose and which we control) in which some form of DB Access is required, the creation of a private, independent TDataBase
instance and working solely with that instance (and with no other TDataBase
instance!) throughout the Threads' lifetime is required.
The reason for this is that ADO.NET providers (and specifically the PostgreSQL ADO.NET Provider 'npgsql') are not thread-safe.
To be able to find violations of those rules early, our TDataBase
Class blocks attempts to take out / work with parallel DB Transactions and raises specific Typed Exceptions (see Automatic: Multi-threading and multi-connection 'Sanity Checks' in the TDataBase Class). Whenever the TDataBase
Class raises such an Exception, various information is logged in the server log file, too. (See Verbose Logging for influencing the verbosity of the logging information.) Would such Exceptions not be raised, a whole variety of Exceptions from various sources would be thrown 'further down the line' when parallel DB access through the same TDataBase
instance would be attempted (see Bug 4364).
Rules That Need to be Adhered to
- Server-side code that runs in a Thread that got started in a 'deliberate multi-threading scenario' (that is, a scenario that we create on purpose and which we control) must perform any DB access only through an instance of
TDataBase
that got created in that Thread (see next paragraph)! - Server-side code that doesn't run in a deliberately started Thread:
- If the server-side code got invoked from the client side it DOES get run in a separate Thread which .NET Remoting creates for us 'under the hood'! When that server-side code access the DB it usually uses the 'globally available'
DBAccess.GDBAccessObj
TDataBase
instance.- Co-ordinated DB Access (Thread-safe DB Access) will be at work in this case and will provide pseudoparallel thread-safe DB access to that
TDataBase
instance.
- Co-ordinated DB Access (Thread-safe DB Access) will be at work in this case and will provide pseudoparallel thread-safe DB access to that
- If the server-side code got invoked from the client side it DOES get run in a separate Thread which .NET Remoting creates for us 'under the hood'! When that server-side code access the DB it usually uses the 'globally available'
Server-side Code That Runs in a Deliberately Started Thread
The creation of a private, independent TDataBase
instance and solely working with that from within that Thread throughout the Thread's lifetime is required.
In any deliberately started Thread, ...
- Create a private, independent
TDataBase
instance and store a reference to it in a Variable. If you store a reference to it in a Field you must make sure it isn't accessed from anywhere outside of that Thread, or if that should be needed, it must be accessed in a completely thread-safe manner. - Open a private, independent DB Connection for this Thread by calling the
EstablishDBConnection
Method on that Thread'sTDataBase
instance.- Supply a unique name for the DB Connection with the optional '
AConnectionName
' Argument of theEstablishDBConnection
Method as this can be a big help in debugging (see Verbose Logging)! - Example: \csharp\ICT\Petra\Server\lib\MCommon\Main.cs, Method 'EstablishDBConnection'.
- Supply a unique name for the DB Connection with the optional '
- Start a private, independent DB Transaction on that Thread's
TDataBase
instance.- You can use any of our usual methods for that and you can choose any IsolationLevel!
- Supply a unique name for the DB Transaction with the optional '
ATransactionName
' Argument of the Method you are using for this as this can be a big help in debugging (see Verbose Logging)! - An arbitrary number of private, independent DB Transactions can be started and ended on that Thread's
TDataBase
instance!
- Run an arbitrary number of arbitrary SQL Commands on the private, independent DB Transaction(s) on that Thread's
TDataBase
instance. - Commit or Rollback the private, independent DB Transaction(s) on that Thread's
TDataBase
instance. - Close the private, independent DB Connection by calling
CloseDBConnection
on that Thread'sTDataBase
instance.
That way, that specific Thread that needs to execute commands against the DB is completely independent of what any other running Thread(s) might be doing in any (of their) DB Transaction(s) (with whatever IsolationLevel) against the same DB (using the common DBAccess.GDBAccessObj
or its/their own separate TDatabase
instance) at roughly the same time! However, a limiting factor for the 'data throughput' and the parallelism achieved could be DB Table Locks as they could impede multi-threading performance and parallelism.
Changing (Library) Methods so That They Can Work With the Default DB Connection or a Separate DB Connection
When Such a Method Has Got a TDBTransaction
instance available
Replace any occurrence of DBAccess.GDBAccessObj
with a call to DBAccess.GetDBAccessObj(ATransaction)
, passing the reference of the TDBTransaction
instance that the Method has got available.
The overloaded DBAccess.GetDBAccessObj(...)
Method acts as a 'set of points' (using figurative railway language here) and will yield the 'globally available' DBAccess.GDBAccessObj
in case a null value for the ATransaction
gets passed, otherwise it will yield the TDataBase
instance which started the DB Transaction that got passed in in its TDBTransaction ATransaction
Argument.
When Such a Method Has NOT Got a TDBTransaction
instance available
- Either...
- add a new optional Argument that allows the passing-in of a
TDataBase
instance;- you are encouraged to use '
TDataBase ADataBase = null
' at the very end of the Method's argument list for the sake of consistency;
- you are encouraged to use '
- or add a new optional Argument that allows the passing-in of a
TDBTransaction
instance;- you are encouraged to use '
TDBTransaction ATransaction = null
' at the very end of the Method's argument list for the sake of consistency.
- you are encouraged to use '
- add a new optional Argument that allows the passing-in of a
- Then replace any occurrence of
DBAccess.GDBAccessObj
with a call to eitherDBAccess.GetDBAccessObj(ADataBase)
orDBAccess.GetDBAccessObj(ATransaction)
.
The overloaded DBAccess.GetDBAccessObj(...)
Method acts as a 'set of points' (using figurative railway language here) and will yield the 'globally available' DBAccess.GDBAccessObj
in case a null value for the ADataBase
or ATransaction
gets passed, otherwise it will either yield the TDataBase
instance passed in in its TDataBase ADataBase
Argument, or the TDataBase
instance which started the DB Transaction that got passed in in its TDBTransaction ATransaction
Argument (depending on which overload of the Method is used).
Caveat: Public WebConnector or UIConnector Methods - Beware of Introducing Dependency on DBAccess.cs in Interfaces
If you add one of the two Arguments to an existing or a new Method (either 'TDataBase ADataBase
' or 'TDBTransaction ATransaction = null
') and the Method is a public
Method in a WebConnector or UIConnector you will get a compiler error like this:
c:\openpetraorg\trunk\csharp\ICT\Petra\Shared\lib\Interfaces\Partner.Interfaces- generated.cs(819,57): error CS0246: The type or namespace name 'TDBTransaction' could not be found (are you missing a using directive or an assembly reference?)
This is because Methods that end up in Interfaces must not have Arguments that reference anything in DBAccess.cs (and both TDataBase
and TDBTransacion
are defined in that Class File). The solution to this looks like this:
- Duplicate the method in code and make the original Method
private
(or, alternativelyinternal
) and remove any 'RequireModulePermission
' Attribute; - In the new,
public
copy of the Method you remove the newly introduced Argument again and simply call the private Method, passing null for the Argument; - run
nant quickClean generateSolution
to generate the 'glue' again and have it compiled.
Now it should work!
Standardised XML Comments
For the sake of consistency you are encouraged to use the following XML Comments when adding an Argument as described above to existing Methods:
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null /// gets passed then the Method executes DB commands with the 'globally available' /// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this /// Argument!</param>
and
/// <param name="ATransaction">An instantiated <see cref="TDBTransaction" /> object, or null (default = null). /// If null gets passed then the Method executes DB commands with the 'globally available' /// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the the <see cref="TDataBase" /> instance /// which started the DB Transaction that gets passed in with this Argument!</param>
Some C# Files That Likely Contain Methods That Could Benefit From Being Changed to Work With the Default DB Connection or a Separate DB Connection
MFinance\Common
- Common.Tools.*.cs
- ServerLookups.cs
MFinance\data
- DataAggregates.cs
MFinance\Setup
- GL.Setup.cs
- GL.AccountingPeriods.cs
MFinance\GL
- GL.Transactions.cs
- Reporting.GiftReports.UIConnector.cs
- Reporting.UIConnector.cs
MFinance\AP
- AP.Find.cs
MPartner\Common
- DataAggregates.ContactDetails.cs
- Mailing.cs
- ServerLookups.cs
MPartner\web
- AddressTools.WebConnector.cs
MReporting MReporting\M*\Functions.cs
MReporting.connect
- UIConnector.cs - KEY to making XML Reports run on parallel DB connections and DB transactions!
*\ServerLookups.DataReader.cs --> see example in: csharp/ICT/Petra/Server/lib/MCommon/ServerLookups.DataReader.cs
* *.Cacheable.ManualCode.cs --> see example in: csharp/ICT/Petra/Server/lib/MPartner/Partner.Cacheable.ManualCode.cs
This list is not exhaustive!
Writing new (Library) Methods That Can Work With the Default DB Connection or a Separate DB Connection
Refer to the section Changing (Library) Methods so That They Can Work With the Default DB Connection or a Separate DB Connection on how to achieve this and use the overloaded DBAccess.GetDBAccessObj(...)
Method to work with either the Default DB Connection or a separate DB Connection right from the start.
Extra Things that You Need to Know
Using 'Central Server-side' Methods With Separate DB Connections
Using Methods of our TTypedDataAccess
Class and Auto-generated 'Typed DataStore' Methods With Separate DB Connections
TTypedDataAccess
Class: All Methods of theTTypedDataAccess
Class that access the DB now work with the DB Connection that started the DB Transactions that gets passed into those Methods. That means that you can utilise those Methods also on separate DB Connections that got started on a separate Thread. (These Methods were previously hard-coded to always useGDBAccess.DBAccessObj
.)- Auto-generated 'Typed DataStore' Methods: All Methods of the auto-generated 'Typed DataStore' now work with the DB Connection that started the DB Transactions that gets passed into those Methods. That means that you can utilise those Methods also on separate DB Connections that got started on a separate Thread. Example for such a Method:
PPartner.LoadByPrimaryKey(...)
in Partner.Access-generated.cs. (These Methods were previously hard-coded to always useGDBAccess.DBAccessObj
.) - Auto-generated
SubmitChanges
Methods of 'Typed DataSets': All these auto-generated Methods now have an optional ArgumentTDataBase ADataBase = null
. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always useGDBAccess.DBAccessObj
.) - Auto-generated
GetCacheableRecordReferenceCount
/GetNonCacheableRecordReferenceCount
Methods: All these auto-generated Methods now have an optional ArgumentTDataBase ADataBase = null
. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always useGDBAccess.DBAccessObj
.) - Auto-generated server-side code for Cacheable DataTables: All server-side Methods of the auto-generated Cacheable DataTable code now have an optional Argument
TDataBase ADataBase = null
. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always useGDBAccess.DBAccessObj
.) - *Cacheable.ManualCode.cs files: The hand-written Methods in such files need to be adapted manually so that the DB Connection that started
ATransaction
runs the commands and no longer the hard-coded DBAccess.GDBAccessObj's DB Connection! Example: \csharp\ICT\Petra\Server\lib\MPartner\Partner.Cacheable.ManualCode.cs.
Using TCommonDataReader.GetData
and SaveData
Methods With Separate DB Connections
An overload has been added to both the TCommonDataReader.GetData
and TCommonDataReader.SaveData
Methods. That overload has got an Argument TDataBase ADataBase
. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (These Methods were previously hard-coded to always use GDBAccess.DBAccessObj
)
Using TSequenceWebConnector.GetNextSequence
With Separate DB Connections
An overload of the TSequenceWebConnector.GetNextSequence
Method has got an optional Argument TDataBase ADataBase
. Pass in the separate DB Connection that got started on a separate Thread to make these Methods execute on that DB Connection. (That Method previously always used GDBAccess.DBAccessObj
.)
Using TPagedDataSet.ExecuteQuery
With Separate DB Connections
The TPagedDataSet.ExecuteQuery
Method now has an optional Argument TDataBase ADataBase = null
. Pass in the separate DB Connection that got started on a separate Thread to make that Methods execute on that DB Connection. (The ExecuteFullQuery
Method that it calls was previously hard-coded to always use GDBAccess.DBAccessObj
.)
Using FastReport Reports Server-side Code With Separate DB Connections
To avoid problems with DB access that may be happening concurrently, reports based on FastReport should be converted to run on a separate DB Connection. To do so, the Report must use an instance of TReportingDbAdapter
created with the Argument 'ASeparateDBConnection
' set to true. By default the TReportingDbAdapter
will execute all DB commands on the 'globally available' DB Connection!
Before converting a given FastReport Report to the new 'separate DB Connection' style, the Report Method must be checked to ensure that all the database access methods it uses are explicitly given the database connection of the TReportingDbAdapter
, rather than being allowed to use the 'globally available' DB Connection. This is done by 'scanning' all program code that gets run inside the Report Method - and in any library methods that it calls - for occurrences of DBAccess.GDBAccessObj
. In case such occurrences are found...
- inside the Report Method: these need to be replaced with
DbAdapter.FPrivateDatabaseObj
; - in any library methods: these Methods need to be changed to allow them to either run their DB Commands on the 'globally available' DB Connection or on a separate DB Connection (see here for how to do this).
Once a FastReport Report got checked and potentially changed it is a good idea to turn on Verbose Logging and to run the Report in all possible variations that might result in varying DB Commands to be run (due to different Report Options). Scrutinise the Server.log file after that Report comes up on screen and check that all DB commands really ran only on the separate DB Connection. Once you have established this you will know that this particular FastReport truly runs with a separate DB Connection!
Automatic: Multi-threading and multi-connection 'Sanity Checks' in the TDataBase
Class
ADO.NET providers (and specifically the PostgreSQL ADO.NET Provider 'npgsql') are not thread-safe. Guidance on how to make sure that DB access is done in a way that is thread-safe is given in the section Requirements For Safe Multi-threaded DB Access. In case attempts are made that would mean that non-thread-safe DB access would occur, the TDataBase
Class blocks attempts to take out / work with parallel DB Transactions and raises specific Typed Exceptions. This is done because finding violations of those rules early is much better than to run into various Exceptions from various sources 'further down the line', which can be very obscure.
These checks that the TDataBase
Class employs are fully automatic and can't be switched off. They are 'invisible' as long as multi-threading-safe DB access is not at risk. However, in case DB access occurs that is not thread-safe, these checks will log information to the server log file (independent of the set 'DebugLevel' of the Server) and will throw specific Typed Exceptions. The following list shows these Exceptions and links to the FAQ section where the reason as to why a certain Exception gets raised is answered.
Typed Exceptions That Can be Raised in Multi-threading DB Access Scenarios
Note: 'FAQ 1' next to one Exception doesn't necessarily point to the same FAQ than 'FAQ 1' next to another Exception.
EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
: FAQ 1, FAQ 2, FAQ 3EDBAttemptingToCreateCommandThatWouldRunCommandOnDifferentThreadThanThreadOfTheTransactionThatGotPassedException
: FAQ 1EDBAttemptingToCreateCommandOnDifferentDBConnectionThanTheDBConnectionOfOfTheDBTransactionThatGotPassedException
: FAQ 1EDBAttemptingToCloseDBConnectionThatGotEstablishedOnDifferentThreadException
: FAQ 1
All these Typed Exceptions derive from one 'base' Exception, EDBAccessLackingCoordinationException
.
(Typed Exceptions that are raised by the Co-ordinated DB Access (Thread-safe DB Access) feature derive from the same Exception.)
The client-side 'Unhandled Exception Handler' presents 'stock messages' to users in case an Exception that derives from the EDBAccessLackingCoordinationException
Exception makes it as far as that (that is, if a programmer didn't catch it server-side or client-side). This is a much better option than letting the Exception escalate and showing the Unhandled Exception Dialog as a result of that.
In case any of the Exceptions listed above occur and make it that far, the MessageBox that is shown to the user presents a message that contains the text "Reason: Parallel data access could not be performed.". Should a user get this message it is a clue for us software engineers that an attempt to work with multiple Threads on DB Connections (either deliberately or accidentally) has happened and that it wasn't co-ordinated sufficiently/done properly. In that case the Server Log should be consulted for details. Additionally, when the appSetting 'Client.DebugLevel
' is set to a minimum of 3 in the Client.config file the Unhandled Exception handler also logs the full Exception details to the Client.log. This is useful for detailed checks by the developer. At that DebugLevel the developer also gets information in the Client.log about the various retry attempts, and if they were exceeded, for cases where screens handle those using the in an automatic retry fashion by means of the TServerBusyHelper.CoordinatedAutoRetryCall
Method (see next paragraph).
Performing Automatic Retries When EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
Gets Thrown
The CoordinatedAutoRetryCall
Method of the TServerBusyHelper
got extended to handle that specific Exception and to perform automatic retries should it occur. This works in the same way as it does with the Co-ordinated DB Access (Thread-safe DB Access).
That manual approach can be used where 'hot spots' of multi-threading DB activity are known and can serve as a mitigation when GetNewOrExistingXXXTransaction
Methods are used - either until those 'hot spots' are fixed, or 'forever' (the latter option is not ideal, though!). Any code sections that already utilise the CoordinatedAutoRetryCall
Method benefit from this automatically!
Locking of DB Tables
A 'side effect' of multiple Threads having their own DB Connections and running DB Transactions independently from each other and DB Commands potentially in parallel is that DB Locks are taken out, potentially in parallel. For the RDBMS this scenario is essentially the same as the scenario where multiple OpenPetra users are connected (each having their own 'globally available' DB Connection) and each of them running DB Transactions and DB Commands independently and potentially in parallel. In both scenarios DB Locks are taken out, according to the IsolationLevels of the DB Transactions involved, and in both situations DB Locking needs to be taken into account.
In the scenario where multiple Threads run DB Transactions and in which DB Commands can potentially get executed in parallel we need to get used to thinking about DB Locking and the effects it can have, for the other scenario (multiple users having separate DB Connections) that thinking should be ingrained in us already.
One effect of DB Locking that may well be felt when multiple Threads run multiple DB Commands in parallel is that the DB Locking might reduce the performance of the multiple DB-accessing Threads because the concurrency that the multiple Threads provide might be impeded by DB Locking. In the worst case DB Locks could reduce the outcome of our multi-Threading attempts to serial processing instead of parallel processing, resulting in what could essentially look like the outcome of Co-ordinated DB Access (Thread-safe DB Access) because the RDBMS might allow only one Thread to perform DB queries at once due to the DB Tables involved and DB Locking being at work!!!
Debugging
Verbose Logging
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;
- For
BeginTransaction
/BeginAutoTransaction
:- IsolationLevel of the new DB Transaction (if it was started with a specific IsolationLevel);
- DB Transaction Identifier (GUID) of the new DB Transaction;
- DB Connection Identifier (GUID) of the DB Connection that started the DB Transaction;
- StackTrace at the point of DB Transaction start and the Thread and AppDomain in which the DB Transaction got started;
- In case of
GetNewOrExistingXXXXTransaction
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 Transaction;
- Thread and AppDomain in which the
GetNewOrExistingXXXXTransaction
command got called.
- For
CommitTransaction
orRollbackTransaction
the following gets logged after the command got executed, but with information that was gathered before the command executed:- the DB Transaction Identifier (GUID) of the DB Transaction;
- whether the DB Transaction was still valid (i.e. not already Committed / Rolled back);
- IsolationLevel of the DB Transaction;
- whether the DB Transaction was reused;
- Thread and AppDomain in which the DB Transaction got started;
- Thread and AppDomain in which the
CommitTransaction
orRollbackTransaction
command got called.
- For any DB Command that gets executed:
- Thread and AppDomain in which the DB Command gets executed;
- DB Transaction Identifier (GUID) of the DB Transaction that the DB Command is enlisted in;
- DB Connection Identifier (GUID) of the DB Connection that originally started the DB Transaction that the DB Command is enlisted in.
Tips:
- If the (new) 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! This is helpful as GUIDs - though they are unique - are not very readable.
- This very verbose logging is extremely helpful when one wants to ascertain that a certain function in OpenPetra, e.g. a Report, Extract, etc. (including any DB access that any library/helper functions that it might call!) really performs every DB access it does on the separate DB Connection in a separate Thread that you purposefully created for it! Method:
- Turn the very verbose logging on before executing the function in OpenPetra;
- Scrutinise the log output after the execution of the function has finished. Check whether all DB Commands that it ran were indeed run only on the separate DB Connection and on the separate Thread. (Naming of DB Connections and DB Transactions pays off here!)
- This is quite some work but the only proof that things are indeed happening as they ought to!
Caveat: This logging is indeed very verbose and hence will slow down execution of the program. 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...
Finding Out Which Queries Are Run (in Parallel) on Which RDBMS Connection (with PostgreSQL)
It can be quite helpful to see from the RDBSMs' end what queries are running against which DB Connection. For PostgreSQL this is possible by issuing the following SQL query:
PostgreSQL before version 9.2:
SELECT datname, usename, application_name, procpid, client_addr, client_port, waiting, query_start, current_query FROM pg_stat_activity WHERE datname='demo' AND usename='petraserver' AND current_query LIKE '%in transaction' ORDER BY procpid;
PostgreSQL from version 9.2 onwards:
SELECT datname, usename, application_name, pid, client_addr, client_port, waiting, query_start, query FROM pg_stat_activity WHERE datname='demo' AND usename='petraserver' AND state LIKE '%in transaction%' ORDER BY pid;
Replace 'demo' with the name of the DB that the PetraServerConsole instance is connected to and run this query in pgAdmin III repeatedly (press <F5> for that) while the function of OpenPetra that you want to monitor for its DB connection use is executing.
How to read the output of that query: PostgreSQL starts a separate DB Server process for each DB connection. Each DB Server process has got its own 'pid' (Process Identification). This means that if several DB commands are executing in parallel on several DB connections you will get a list of all the processes that are currently running DB queries - one result line for each process/DB Connection (the 'query' column shows the SQL statement of the query that is currently running against that DB Connection). If you intended the function of OpenPetra to run solely on a specific DB connection and you are seeing multiple lines at times it isn't doing this! In that case refer to the section Verbose Logging.
Finding out the Number of Currently Open DB Connections (with PostgreSQL)
One can use the combination of the static Methods TDataBase.ClearAllConnectionPools()
and TDataBase.GetNumberOfDBConnections
to get the number of currently open DB connections on a RDBMS (using just the latter Method on its own is not reliable because of connection pooling)! Use the static Method TDataBase.ClearConnectionPoolAndGetNumberOfDBConnections
to call both Methods at once. For an example on how to use this see Unit Tests in \csharp\ICT\Testing\lib\Common\DB\test.Multithreading.cs!
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.) - and indeed data from a DB - 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 - and indeed data that resides in a DB - 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...
FAQ's
Instead of opening a new DB Connection for 'Thread A': Could I not simply 'make a peek' to see if a DB Transaction is running on a given DB Connection in another Thread and if so, use that?
No. While it is possible to inquire the 'Transaction
' Property of a given TDataBase
instance that one has got access to (e.g. of DBAccess.GDBAccessObj
) (and that inquiry is thread-safe) and in doing so it is possible to find out whether a DB Transaction is running on that TDataBase
instance (if the 'Transaction' Property yields null then no DB Transaction is running, otherwise there will be one running), it would not be safe to use that DB Transaction...
- as there is no guarantee that the other Thread wouldn't be committing/rolling back that very DB Transaction any time (which would have unknown consequences for 'Thread A'!);
- as it got started on another Thread, and ADO.NET is not thread-safe.
To prevent that if such an attempt should happen / occur, the TDataBase
class will throw...
- an
EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
if one tries to 'piggy-back' on its running DB Transaction by callingGetNewOrExistingXXXXTransaction
from a different Thread, and - an
EDBAttemptingToCreateCommandThatWouldRunCommandOnDifferentThreadThanThreadOfTheTransactionThatGotPassedException
if one tries to run a DB Command on aTDBTransaction
instance that one obtained by using theTDBTransaction
instance that is returned by the 'Transaction
' Property of aTDataBase
instance which got created in another Thread. (The 'Transaction
' Property of theTDataBase
Class hasn't got XML Comments that explicitly warn against doing anything on and with that Transaction for no reason...!)
Could calling GetNewOrExistingXXXTransaction
ever give 'Thread A' a running DB Transaction from another Thread?
No. When a Method in 'Thread A' calls GetNewOrExistingXXXTransaction
it must do that on the TDataBase
instance that got instantiated on this Thread otherwise a EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
gets thrown. That way 'Thread A' will never (be able to) get a DB Transaction that belongs to a TDataBase
instance of a different Thread ('Thread B') using any of the GetNewOrExistingXXXTransaction
Methods!
Conversely, 'Thread B' (e.g. an intentionally started 'worker thread') will never (be able to) get a DB Transaction with GetNewOrExistingXXXTransaction
that belongs to a TDataBase
instance of a different Thread ('Thread A', which might e.g. use the 'globally available' DBAccess.GDBAccess
instance) because it must not call GetNewOrExistingXXXTransaction
on the instance of 'Thread A' - otherwise a EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
gets thrown!
Could calling BeginXXXTransaction
ever give 'Thread A' a new DB Transaction from another Thread?
Yes, and you must make sure that this isn't happening by only ever calling BeginXXXTransaction
on the separate instance of TDataBase
that gets purposefully created for 'your' Thread!
Reason why TDataBase
needs to allow this and cannot not throw an Exception should that happen: If server-side code got invoked from the client side it DOES get run in a separate Thread which .NET Remoting creates for us 'under the hood'! When that server-side code accesses the DB it usually uses the 'globally available' DBAccess.GDBAccessObj TDataBase
instance. That instance got created when the user logged in to the OpenPetra server, and it got created on a different Thread than the auto-created Thread that .NET Remoting manages.
Could 'Thread A' (accidentally) Commit or Rollback a DB Transaction that got started on another Thread (and vice versa)?
No. We must not do this because ADO.NET is not thread-safe. To prevent that if such an attempt should happen / occur, the TDataBase
class will throw an EDBAttemptingToWorkWithTransactionThatGotStartedOnDifferentThreadException
.
Could 'Thread A' (accidentally) execute a DB Command that it enlists in a DB Transaction of its own on a DB Connection that is owned by another Thread?
No. We must not do this because (1) ADO.NET is not thread-safe, (2) it really doesn't make sense, if you think about it what the RDBMS should do with it! To prevent that if such an attempt should happen / occur, the TDataBase
class will throw an EDBAttemptingToCreateCommandOnDifferentDBConnectionThanTheDBConnectionOfOfTheDBTransactionThatGotPassedException
.
(The check that the TDataBase
class performs is even more general: it simply disallows the execution of a Command that is to be enlisted in a DB Transaction that got created on a different DB Connection.)
Could 'Thread A' (accidentally) close a DB Connection that is owned by another Thread?
No. We must not do this because ADO.NET is not thread-safe. To prevent that if such an attempt should happen / occur, the TDataBase
class will throw an EDBAttemptingToCloseDBConnectionThatGotEstablishedOnDifferentThreadException
.
What is the use of the GetNewOrExistingXXXTransaction
Methods if They Cannot be Used When Multiple Threads are Involved?
Calling one of the GetNewOrExistingXXXTransaction
Methods permits 'nesting' of DB calls without the need of passing a reference to the running DB Transaction everywhere. This is especially handy for 'library-like' Methods, which might get called in situations where there is - or where there isn't - a DB Transaction running.
Although this is very handy, that 'nesting' is only permitted if the chain of Methods are all called in the same Thread (i.e. all the Methods are in the same call stack) because ADO.NET isn't thread-safe! In a multi-threading scenario only the use of a separate DB Connection with a separate DB Transaction is acceptable - see Rules That Need to be Adhered to.
How can a 'library-like' Method be written that could utilise a running DB Transaction of a certain TDataBase
instance if it is currently not busy, but would create its own DB Transaction on a separate, new TDataBase
instance if it is busy?
- While this is possible, this must be done
- within the same Thread as ADO.NET is not thread-safe;
- only when the IsolationLevel is exactly the one that is needed;
- in a way that ensures that no other Thread could utilise the
TDataBase
instance that has started the DB Transaction in question while the DB access code in the 'library-like' Method runs.
The TDataBase
Class has got the Method CheckRunningDBTransactionIsCompatible
that performs checks to make sure that the first conditions are met and returns 'true' only if they both are met. If you can ascertain that the third condition will always be met then you can use that Method and use the DB Transaction in question, otherwise the 'library-like' Method must create a separate, new TDataBase
instance, establish a separate DB Connection and start a new DB Transaction. (You could use thread-synchronisation methods such as utilising a ManualResetEvent
to ensure that the third condition will always be met - see how it is used in \csharp\ICT\Testing\lib\Common\DB\test.Multithreading.cs. In addition - and only in addition - you should call the ConnectionReady
Method of the TDataBase
instance that has started the DB Transaction in question to be sure that the DB Connection isn't busy at the moment.)
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 multi-threading and multi-connection 'sanity checks' in the TDataBase
Class and who wrote this documentation.