Re: PREPARE TRANSACTION and webapps

Greg Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> Greg Stark <gsstark(at)mit(dot)edu> writes:
>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>>> What this would actually be useful for is a fair question though
>>>> --- what's it do that you don't have now?
>>
>>> I think what they want to do is make the database concept of
>>> transactions match up 1-1 with their application's concept of
>>> transactions. Which may span multiple stateless http requests.
>>
>> [ itch... ] This seems to me to fly right in the face of the
>> oft-repeated advice that you don't hold a transaction open while the
>> user thinks about it, goes off to lunch, vacations in the Bahamas,
>> etc.
>
> Sure, I said that was the answer people get when they ask about this.
> And it's clearly better approach if it's available. But what if the
> locking or MVCC semantcis *are* what you need?

This problem is well understood and solutions are readily available. If
you have a small amount of data, you cache it in the web server's
session, then once the user "confirms" the transaction, you write it all
at once to the DB. If you have a significant amount of information, you
create a set of "shopping cart" tables and populate those as the end
user progresses through the transaction. Once the user confirms the
transaction, you read it from the shopping cart tables and write it all
at once into the final tables.

Having worked with web-based, transaction-oriented applications for
almost 10 years now, I don't see any justification for holding an actual
database transaction open between HTTP requests. As Tom correctly
points out, there is no guarantee whatsoever that the end user will ever
complete such a transaction.

>
> If you really do need to allow one user to edit the information and
> still present the existing information to others but not let them
> update it concurrently, etc. Reimplementing full ACID semantics is
> hard and easy to get wrong. We already have a tool that provides them
> properly.

--
Guy Rouillier