Re: Transactions and HTTP

Raj Mathur wrote:

>Hi,
>
>Writing a web-based application using PostgreSQL as the back-end
>database. The master create functions look something like this:
>
>1. Accept ID from user.
>
>2. Verify that ID doesn't exist in database. Start transaction.
>Create blank record with ID as key.
>
>3. Accept remaining data for record from user.
>
>4. Enter data into newly-created blank record. End transaction.
>
>Firstly,is it possible to have a transaction spanning multiple
>instances of the CGI/mod_perl invocations?
>
Not easily. You are not always guaranteed the same httpd process when
connecting. HTTP is stateless.

>Second, there are issues if the user session terminates for some
>reason (e.g. user goes offline after the blank record has been
>created, etc.) and I end up with a locked blank record in the
>database. Would appreciate pointers to best practises in handling
>this paradigm.
>
It is basically a poor design when using HTTP operations. You are
better off using server-side session data to store all the information
you will need for the complete database operation and then at your final
stage of data entry do the record addtion inside of a transaction (i.e.
one POST operation), if you want to rollback on error. This should
eliminate your concerns over lost locks and hanging transactions.

>Unrelated, but I find at least 3 different interfaces to PostgreSQL
>from Perl: DBI, Pg and PgSQL. I'm using Pg at the moment, since that
>ships with PgSQL by default with Red Hat, but are there any reasons to
>choose one over the other?
>
>Regards,
>
>-- Raju
>
>