IBX 2.3.4 + firebird 3.x embedded + multi-threaded application

[devEric69]

Hello,

AFAIK, since Firebird 2.5, it's said that the client libraries, including the embedded one, can now be used in multi-threaded applications without any application-level synchronization. That said, I'm planning to have to create background threads (launched by timers), inside the same process\application. So, what would be most optimal (yet feasible), using Firebird 3.x, at least:
- each "thread" (i.e. "session") must create (in its Execute method) it's own object IBDatabase and it's own object IBTransaction (already managed parallel accesses are possible with\through the 3.0 clent library driver)?
- each "thread" (i.e. "session") can only share (reference parameters, in its Create method) with mutex access in read\write the same singleton-object i.e. an already serialized and already created object IBTransaction and its associated singleton-object oIBTransactionsingleton fundamental already serialized and already created too?
- should it be a mix, i.e. is IBDatabase already thread safe (can we call - "as it" - its open\read methods without TCriticalSection code, having a sort of inner lock\unlock)? Is IBTransaction already thread safe (can we call - "as it" - its startTransaction\read and commitTransaction\write methods without TCriticalSection code, having a sort of inner lock\unlock)? Each thread should need its own\created\owned fondamental transaction's sub-transaction (it's said that the database creates\recreates 1 thread per different transaction-ID, on the server side)?
- have I got it all wrong ?

Any technical explanation, concerning multi-threaded application, would be really appreciated.

Regards.

[alpine]

Although I can't give very good advice on Firebird LCL components with regards to the MT, using several IBDatabase's and/or IBTransaction's in one application can introduce multiple and probably undesirable effects.
   
The MVCC model of the Firebird can be source of some confusion when dealing with multiple connections.

Would you please share some more details for the application? Why multiple threads were needed? Will they only write or will also modify data from another threads?

IMHO, it is always better to keep minimal number of connections and use them to isolate GUI from the worker threads (i.e. two) or to organize some kind of connections pool. After all, their features  (isolation, serialization) come with a price.

[devEric69]

Hello @y.ivanov,

The MVCC model of the Firebird can be source of some confusion when dealing with multiple connections.

I would like to use the already same and shared and thread-safe connection, between all threads-queries, of course .
Hence this question: the IBX IBXDatabase (connection) is already shareable, or have I to recreate it for each thread if it make sense, or I have to make it (program singleton) thread-safe with a shared access by each thread-query between all the existing threads-queries.

Would you please share some more details for the application? Why multiple threads were needed? Will they only write or will also modify data from another threads?

For example, I would like to be able to do "data-pumping" by a computer-automated thread-query, which should therefore work in parallel with the other threads-queries  triggered by the events in the main-UI-thread.

IMHO, it is always better to keep minimal number of connections and use them to isolate GUI from the worker threads (i.e. two) or to organize some kind of connections pool. After all, their features  (isolation, serialization) come with a price.

So, your advice would be to create a "pool" of working thread sessions (=~ the "old" TSessionList), with 2 IBXDatabases a.k.a. 2 connections - one dedicated for the main-GUI-thread, and one dedicated for the background thread-queries; each IBXDatabase-connection permitting one TTransaction [startTransaction..commitTransaction] a.k.a. InTransaction, at a time (=~ 2 "old" TSession). So, to resume:
- one dedicated to the interface (it is true that the user cannot duplicate himself in parallel ).
- another one, isolated from the main-GUI-thread, serialization among the threads-queries in the background. Hence this question: this last one IBX IBXDatabase (connection) is already sharable between background multiple threads-queries.


To resume, the software layer of the background threads-queries could be like that:

<Lazarus soft.'s IBX layer...
┌---------------┐┌---------------┐
| transaction 1|| transaction 2  |                                                                                                                                                      
└---------------┘└---------------┘
       ↖↘              ↗↙
┌---------------┐┌---------------┐
|           connection 1               |     
|        a singleton,in this case   |                                                                        
|   (thread safe and sharable)   |                                                                           
└---------------┘└---------------┘
...Lazarus soft.'s IBX layer>

                    ↑↓

<FireBird client library layer...
┌---------------┐┌---------------┐
|          libfbclient                    |                                                                           
|      (black box, for me)          |      
└---------------┘└---------------┘
...FireBird client library layer>

                    ↑↓

<FireBird server embedded, or not...
┌---------------┐┌---------------┐
|      firbird 3.x server              |                                                                           
|      (black box, for me)          |      
└---------------┘└---------------┘
       ↗↙                   ↖↘               
┌---------------┐┌---------------┐
| transaction 1 || transaction 2 |                                                                                                                                                      
| reification     ||  reification     |
└---------------┘└---------------┘
...FireBird server embedded, or not>


So, displayed differently, IBX background thread_1-query should\could create\manage these (?)...:
┌---------------┐
| transaction 1 |
└---------------┘
       ↖↘       
┌---------------┐┌---------------┐
|            connection 1              | 
|        a singleton,in this case   |                                                                        
|   (thread safe and sharable)   |                                                                           
└---------------┘└---------------┘

... and IBX background thread_2-query should\could create\manage these (?)...:
                       ┌---------------┐
                       | transaction 2 |                                                                                                                                                      
                       └---------------┘
                       ↗↙
┌---------------┐┌---------------┐
|             connection 1             |   
|        a singleton,in this case   |                                                                        
|   (thread safe and sharable)   |                                                                                                                                                   
└---------------┘└---------------┘

Said differently, in the <Lazarus soft.'s IBX layer>, is there a software resource, which is a non-thread-safe shareable resource\object between multiple background threads-queries (a bottleneck, that must be protected in read and in write access by a critical section between all background threads-queries during their execute method)?

Does it make sense to think like this, with IBX components + firebird 3.x client library? Can it be improved?

[devEric69]

Found on the internet:

Currently, the highest level of concurrency supported by any version of the Firebird client library is thread-safety at the connection level.
When we say that the Firebird client library is thread-safe at the connection level, we mean that it is safe to use a particular connection in only one thread at a time, [snip]

Well, that is affirmative: seems for me thet each thread must create its own connection!

[snip] although the same connection can be manipulated by different threads in a serial fashion, and different connections can be manipulated by different threads in parallel. For example, in a multithreaded application server, it is safe for a particular connection to be leased from a connection pool by Thread A, used, and returned to the pool for later lease by Thread B. It is not safe for Thread A and Thread B to use the same connection at the same time.

Sharing the same connection between threads seems to be evil .

(original link) If you are using multiple threads, then you should not share a single connection. Your problem is not one of multiple connections, but of multiple threads using the same connection, and trying to create their own transactions, which is explicitly not supported by the driver. Your code is likely to suffer from other race conditions as well. Stop using that singleton connection. Instead obtain a new connection for each unit of work and close it when you're done with it.

Same thing as above: race conditions (appearing  with the same connection) means - for me - that, inside the driver, a single instancialized "connection pipe" object isn't thread safe i.e. it's a bottleneck, potentially creating deadlocks; seems for me that each thread could and should create its own new connection (but it's maybe an overkill); hence a pool of 2 managed connections with IBX components, not less, but at least to start with... No?

Always found on the internet: seems that "Classic Server can now be multi-threaded" and is called "Super Classic server"; "Embedded server, now is Superclassic"; "If you have two connections you can have two queries running at the same time", i.e. inside their own TThread;

So, Firebird client library layer seems to be multi-threaded, if it is used like this:
<FireBird client library layer...
                  ↑↓     ↑↓
┌---------------┐┌----------------┐
|               libfbclient                |                                                                           
|                                             |
|                     ||                      |
| connection 1  || connection 2  |
| transaction 1 || transaction 2  |
|                     ||                      |
|                                             |
|               libfbclient                |
└---------------┘└----------------┘
                 ↑↓     ↑↓
...FireBird client library layer>

[alpine]

Wow! It is getting quite serious 
As I wrote, I can't give advice on IBX because of my limited experience with it, but I doubt it is written with MT in mind. After all, it is part of a "visual" library.

IMHO, one can not rely on other than connection-level safety when dealing with RDBMS, hence I usually do a wrapper object for the DB access/connection, with strong mutual exclusion. It can be instantiated multiple times, of course, but I'd never go for more than few of them.

As long for the Firebird, the transactions should be lightweight, because of the MVCC. But there is another issues related to the CommitRetaining thing: http://www.firebirdfaq.org/faq13/ , 2-nd pt.

If you intend to make a massively MT app, perhaps some type of DAQ, wouldn't it be better to use something lighter for pre-buffering, sqlite for example (it can be used even in-memory), and then to insert buffered data into the Firebird, possibly in Blobs?
 

[devEric69]

If you intend to make a massively MT app, perhaps some type of DAQ, wouldn't it be better to use something lighter for pre-buffering, sqlite for example (it can be used even in-memory), and then to insert buffered data into the Firebird, possibly in Blobs?

Good idea: but no need for pre-caching using SQLite located between Firebird and some eventual application's heavy threads. It already exists: it's called IBX memory "CachedUpdate" (a kinf of incorporated TBuffDataset), which can be indeed used by such heavy (meaning potentially transactionally long) background threads-queries (a SAS - for the best - hard disk could appreciate, to write only once and serially such a long continuous but already prepared\formated field's information).

[tonyw]

The first point is that neither IBX nor the underlying Firebird Pascal API make use of critical sections. The assumption is that the user is either single threading or knows what they are doing. Gratuitous use of critical sections would after all have a performance penalty.

The exception is for event handling, which uses its own helper thread, and for SQL Monitoring, which is also multi-process.

If you want to use IBX for a multi-threaded application, as general good practice:

1. Ensure that calls that change the state of the database connection or a transaction are always from the same thread or use critical sections.

2. Do not execute statements from different threads without protecting with critical sections.

3. Do not fetch or read from cursors from different threads without protecting with critical sections.

4. Any transactions or datasets used in the main (LCL) thread should not be used in other threads.

Ideally, each thread uses its own transaction(s) and cursors and executes its own statements.

[devEric69]

Hello @tonyw,

thank you for the reply.

(All this - below - is said primarily from the perspective of avoiding deadlocks.)

The first point is that neither IBX nor the underlying Firebird Pascal API make use of critical sections. The assumption is that the user is either single threading or knows what they are doing. Gratuitous use of critical sections would after all have a performance penalty.
The exception is for event handling, which uses its own helper thread, and for SQL Monitoring, which is also multi-process.

Indeed, I saw that the SQL monitoring uses a shared memory, and semaphore objects.

_1. Ensure that calls that change the state of the database connection or a transaction are always from the same thread or use critical sections.

This is the case of the human interface, which I call the main-GUI-thread, which can share the same (singleton TIBConnection + its associated singleton TIBTransaction) between its datamodules, since (human work + modal windows) allows to impose a sequential work ", a chained work, thus mutually exclusive with itself.
Regarding the extra threads, which I call backgroud-threads, if each backgroud-thread creates its working session (own created TIBConnection + its own created TIBTransaction) in its Execute, and free its working session in its OnTerminate event, then it's consistent with this advice, amho.

_2. Do not execute statements from different threads without protecting with critical sections..

In other words, AMHO, it's rather a tip about the Execute method, which is used to avoid concurrent writes (INSERT or UPDATES or DELETE) of the same records.
Symmetrically, it's more of a non-advice about the Open method, which allows for concurrent reads (SELECT) of the same records.
Overall, it's a hint to emulate server-side behaviour of reading the same location-records by several, but writing by only one to the same location-records.

_3. Do not fetch or read from cursors from different threads without protecting with critical sections.

Put differently, AMHO: the results of the same instantiated singleton object-query (via Open, or via a cursor from a stored procedure), cannot be shared among the different threads. Better to recreate the same object-query in each thread (otherwise, you have to protect the access of the singleton object-query by a mutually excusive critical section between the threads wanting to share its records).

_4. Any transactions or datasets used in the main (LCL) thread should not be used in other threads.

Said in another way, AMHO: it's similar to what has just been said above, but it reminds us that the interface, with its TDBGrid, ..., linked to TIBQuery, TIBTable, ... instantiated objects which are already used by the LCL interface, is also ...a thread. It's even the main thread of the application. So, we must not use its TIBConncetion, TIBTransaction and TIBdatasets objects in the background-threads.

Ideally, each thread uses its own transaction(s) and cursors and executes its own statements.

Ok: put another way, each background thread must create and destroy its specialized datamodule instance containing its TIBdatabase + TIBTransaction + TIBdatasets components, telling the main-GUI-thread before where its TIBdatasets are doing UPDATE or INSERT or DELETE, i.e. that they will do an adequate "table locking" or "row locking" (better) for writing purposes, using the USING (https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-transacs-statements) SQL clause (it leads us to study server side locking, thus to study Firebird. It's normal...).

As a complement (already said before), making use of the IBX cached updates by the background threads, it allows to stay a bit more confortable in the client side :-) .
Indeed, using the metaphor that a background-thread is like the traveling salesman and his model briefcase, then it remains consistent to think that once it has finished its work (a isolated work tour, it were), being returned to its firm's central office, he just have to notify his boss - the main-GUI-thread - that his stamped\cached work is on his desk, and that his boss himself can apply it, or even resolve it, inside the real SQL tables of the firm, without race condition now: because the boss, alone, decides everything now.
After that, the specialized traveling salesman will be able to go back in order to see his customers for another round of peregrinations...

Any comments appreciated.