Re: Transactions and HTTP - Mailing list pgsql-admin

Hi Thomas,

>>>>> "Thomas" == Thomas Swan <tswan@idigx.com> writes:

    Thomas> 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?

    Thomas> Not easily.  You are not always guaranteed the same httpd
    Thomas> process when connecting.  HTTP is stateless.

Tell me about it :)

    >> 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.

    Thomas> It is basically a poor design when using HTTP operations.
    Thomas> You are better off using server-side session data to store
    Thomas> all the information you will need for the complete
    Thomas> database operation and then at your final stage of data
    Thomas> entry do the record addtion inside of a transaction
    Thomas> (i.e. one POST operation), if you want to rollback on
    Thomas> error.  This should eliminate your concerns over lost
    Thomas> locks and hanging transactions.

This is infeasible from the application point of view, since once the
user selects an ID to add to the database no other user must be
permitted to use that ID.  Hence the two-stage process: creating a
blank record with the desired key, and populating that record.  The
first phase ensures that no other user gets the key.

Off the top of my head I see two solutions to this:

1. Have a process that regularly goes through the database and deletes
blank records created more than (say) 30 minutes ago.  Contrived,
inefficient and unmaintainable.

2. Hand off all database operations to a server process using (say)
SOAP.  Then it's the responsibility of the server process to keep
track of created blank records and automatically rollback transactions
that exceed some fixed amount of time.  Elegant but requires more
programming.  I presume this would work...

Time to go learn SOAP, I guess.

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
                      It is the mind that moves