Multithread Update statement execution question

[frameworker2019]

I follow the recommendation here to create a connection pool. Then I have one or more threads which access the pool. I prepare an update statement in tasks which run in one of the available threads. In fact only one thread works Ok and the updates will delivered and executes on the database. All other tasks throw no exception but the execute statement returns false, so there's no error and the updates will not executed.

I can't give the minimal example for now due to the complexity of the application. So my first question is what means the gotData flag in the case of an update statement?

Backend: PostgreSQL Db Version 13.x Platform: macOS, brew

[vadz]

Statement objects are not MT-safe, they're associated with a connection and each of them can only be used from the thread using that connection. If you're trying to reuse the same statement from multiple threads, it's not going to work. And if you're trying to do something else, you really should explain what exactly are you doing.

[frameworker2019]

No, due to the problems to share the statement I hold the reference local but the pool is shared. The example of the pool doesn’t define a mutex so I assume that is done by SOCI internally?

[vadz]

The pool itself is MT-safe, but each connection is not, i.e. it should only be used from a single thread.

[frameworker2019]

Okay, i read the stated doc:

The working threads that need to lease a single session from the pool use the dedicated constructor of the session class - this constructor blocks until some session object becomes available in the pool and attaches to it, so that all further uses will be forwarded to the session object managed by the pool. As long as the local session object exists, the associated session in the pool is locked and no other thread will gain access to it. When the local session variable goes out of scope, the related entry in the pool's internal array is released, so that it can be used by other threads. This way, the connection pool guarantees that its session objects are never used by more than one thread at a time.

I understood the passus "As long as the local session object exists, the associated session in the pool is locked and no other thread will gain access to it." that the code will lock the session when I call

session sql(cPool)

from anywhere. I will try the first suggested approach to set the session individually in each thread. But in my case this could be a problem because dozens of threads come into play. It is planned to think about a thread save SOCI version?

[vadz]

This only means that access to the pool itself is MT-safe, nothing else.

And no, I don't think it makes sense to make a session MT-safe as the underlying database access APIs are not MT-safe, so it would just mean using a mutex to serialize all accesses which is something that

1. Could be done easily outside of the library.
2. Shouldn't be done at all (because why use threads if you serialize all of them anyhow).

So I don't think there is anything to do here.