Re: Transactions and HTTP - Mailing list pgsql-admin

since its not possible/simple to have transactions spanning
multiple HTTP requests may be (if requirements permits)
1,2,3 can be merged in a single step (transaction).

regds
mallah.

On Wednesday 22 January 2003 11:04 am, Raj Mathur wrote:
> 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

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.