Обсуждение: Transactions and HTTP

Поиск
Список
Период
Сортировка

Transactions and HTTP

От
Raj Mathur
Дата:
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?

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.

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
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
                      It is the mind that moves

Re: Transactions and HTTP

От
Thomas Swan
Дата:
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
>
>



Re: Transactions and HTTP

От
Raj Mathur
Дата:
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

Re: Transactions and HTTP

От
"Rajesh Kumar Mallah."
Дата:

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.