Re: Transactions and web applications - Mailing list pgsql-general

----- Original Message -----
From: "Lincoln Yeoh" <lylyeoh@mecomb.com>
> What are the recommended ways to do transactions in web applications?
>
> Right now I have persistent database connections, but currently they are
> shared by multiple sessions. So I make sure that database level
> transactions are closed after each page/http request.
>
> In theory I could tie database connections to sessions, but then if I have
> 1000 outstanding (not necessarily active) sessions that would mean 1000
> database connections, and 1000 postgres backend processes right? Ouch!

    It depends on what form of caching you use.  If you are using
AOLserver-style pooling, then only those connections that require the use of
a database will get one from the pool.  Thus, you can have 50 active
AOLserver threads, but only 3 database handles in use if only 3 threads
require it.

    If you are using Apache-style pooling, each pool is kept on a per-child
basis.  So, the minimum number would be 1 for each child, which means if you
have 1000 children, you have 1000 database connections.  Partly for this
reason, really big sites that use Apache normally have separate computers
that serve static, dynamic, and database requests.

    However, are you sure that you're not overestimating your requirements?
Assuming the average database connection takes 2 seconds to complete, it
would require a load of 500 hits/sec to reach that 1000 figure you
state...only the largest of sites get that kind of traffic.  If your site
does get that popular, it is likely you will have the $$ to invest in enough
hardware to support it.

> Because of this it looks like I may actually have to do application level
> transactions instead of being able to rely solely on database level
> transactions.

    I fail to see how application-level tranactions will help you here.
Even if you do app-level transactions, you will still need a database engine
to hold and query whatever data you have.  Maybe you are suggesting building
your own database engine built into your app.  IMHO, this would be a rather
bad idea.  Do you really think you can duplicate most of the functionality
in PostgreSQL, so that it is reasonably stable, and reasonably fast, in a
reasonable amount of time?

    Hardware is generally cheaper than programmer time...personally, I would
need a better reason than "Apache::DBI requires more resources than I would
like" to embark upon a project like this.  But maybe I am misinterpeting
your question.

> Am I missing something? Are there better/good alternatives?
>
> Would it be possible to "name" transactions and continue them from other
> database connections? Sounds impractical tho- what happens if two
> connections try to continue a transaction at overlapping times :) (user
> opens up multiple browsers from one page).

    What would be the purpose anyway?  I don't see how it would help
performance or
the # of needed database connections.

            -Mike