Re: esql/c multithreaded connection handling question...

Gregory Block wrote: > It seems to me... that there's a major inefficiency with the handling > of connections in a multithreaded environment under the current > Informix e/sqlc development environment. > > Specifically, we've been told by a consultant (and documentation) that > the way one would handle this is to simply try setting the connection > name, and if it fails with a -1802, to try again. That seems to be a very stupid way of doing it. However, ESQL/C certainly does not keep a record of which thread is using which database connection so that you can have ESQL/C allocate a (currently unused) connection to the current thread from the pool of connections. That is a facility that you are expected to provide. In my opinion, which has not been tested in the heat of actual practice, there are perhaps 3 scenarios: S1: N threads, N connections. S2: N threads, 1 connection S3: N threads, 1 < x < N connections (N > 2) Under scenario S1, each thread has its own connection, and uses that connection in perpetuity. The master thread allocates a connection to each thread, and each thread knows which connection it is to use, so it does. There are no obvious dormancy issues. AFAIK, the threads would not even have to do a SET CONNECTION whenever they restart operations. (Or I may be lacking horribly in experience). If each thread needs to access two databases, then the same simple minded approach of using two permanently allocated connections for each thread should still be usable. If the second connection is only needed transiently, then the thread which needs it would generate a unique name (that needs to be handled in a thread-safe manner) and the connection would be opened with the generated name. Under scenario S2, all the threads share the same connection. Therefore, when each thread has finished with it for the time being, the thread will need to set the connection to dormant and then yield. Unless it does so, all the other connections will run into -1802 errors (an error; I assume -1802 is connection in use by another thread or some variant of that). Here, the threads all know which name to use -- it's the same for all of them -- and the threads need to keep a record of which one currently owns the connection. Scenario S3 is the most complex, and requires the application to keep tabs on which connections exist and which thread has each connection at the moment. There is no way for a thread T2 to preempt a connection held by thread T1, so the threads will have to cooperate. In my discussion of S1, I discussed threads needing a second connection. You could also treat the extra connections using a variant on the S3 solution. The key point is that ESQL/C cannot manage the connections; the application has to manage the connections for the threads. The documented recommendation seems rather ludicrous. I'm not sure whether there is a way to get a list of all current connections. There should be; the DISCONNECT ALL statement requires that ESQL/C maintain such a list. > For obvious reasons, we've got some issues with this. On one hand, > we're capable of opening <n> connections to SAP; why can't this be done > with something like signals or semaphores instead? Que? Surely not... Well, maybe you could use a semaphore for each connection, but... > Surely *someone* > out there has solved, or at least addressed, this problem. > > Is the only way to solve this to do one's own pool management, I think so. > taking that role out of the e/sql and coding it by hand? I don't see it as taking that role out of ESQL/C; I don't see it as ESQL/C's role to arbitrate on the connection strategy that you want to use -- you need to decide which connection strategy you need to use, and if you are using scenario S3, how you will handle resource exhaustion (all current connections already in use -- do I wait for one to become available, or do I allocate a new one). This stuff is all enabled by ESQL/C, but no policy is set by ESQL/C. In terms of the X11 mantra -- ESQL/C (or X11) provides mechanism and not policy. > If so, how is this best done? Is there a more efficient API to use than > the e/sql would > generate? My ideas are outlined above. There may be better ones available. I don't think the ESQL/C API is especially at fault -- the suggested mechanisms are, but that can be fixed by using your own. It would be nice to have a generic set of procedures for handling a set of connections according to any of a number of possible policies, but actually writing that requires knowledge of the threads package in use. For example, the simple way of generating a new connection name is: void new_connection_name(char *buffer, size_t buflen) { static int counter = 0; assert(buflen > 12); sprintf(buffer, "x_%d", counter++); } However, this is not thread safe -- two threads could read counter at the same value, and then separately increment it. That needs to be resolved by appropriate multi-threading techniques (a mutex, probably). Issues like which of the scenarios S1..S3 are in force etc and how to handle resource exhaustion (and if creating a new connection isn't allowed, dealing with recalcitrant threads which won't yield their connection) are hard to generalize. > Does anyone have any experience with this that they could enlighten me > with? We've got a situation where we need to share a pool of n > connections across a great deal many more threads (roughly 100, with > many more than just 1 in a wait mode as a result) and this continuous > looping is a noticeable impact on our system. While we could add > delays to the loop, that isn't a very efficient solution, as we'd still > like these transactions to take place at their earliest possible time > to maximise the use of these connections. This sounds awfully like S3, the hardest case. Your application needs to manage the connections actively. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>