Обсуждение: Transactions and HTTP
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
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 > >
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
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.