Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
> Sorry if I'm asking silly questions, but how does transactions and
> connection pooler's interact?
That depends a great deal on the pooler and its configuration, as
well as your client architecture. Our shop gathers up the
information needed for our database transaction and submits it to a
server application which has all the logic needed to use that data
to apply the transaction. We determined long ago that it is a Very
Bad Idea for us to have an open database transaction which is
waiting for a user to do something before it can proceed.
> Say if you have 100 clients all doing "fairly inactive" database
> work in transactions lasting a couple of minutes at the same time.
I'm not sure what you mean by that. If you mean that part of a
database transaction hits the database, and then it takes a while
for the rest of the statements for the transaction to be generated
(based on network latency or waits for user input), then it is hard
to see how you can effectively use a connection pool. I have yet to
see an environment where I think that's a good approach, but I
haven't seen everything yet. :-)
On the other hand, if the issue is that 100 transactions are fired
off at the same time and it takes two minutes for them all to
finish, unless you have *a lot* of CPUs and spindles, that's not
efficient use of your resources. A connection pool might indeed
help with that; you might start getting transactions back in one
second, and have them all done in a minute and a half if you used a
properly configured connection pool.
-Kevin