Re: Single database transaction across a multiple HTTP requests?
- From: gordonb.gm8gk@xxxxxxxxxxx (Gordon Burditt)
- Date: Sat, 07 May 2005 01:40:20 -0000
>Building up a list of changes and then executing all changes at once
>from a single PHP page is a good idea. This was my first choice, but in
>our application this turns out to be difficult because changes you make
>in one step effect the decision of how subsequent changes are made.
>For example, some steps invoke stored procedures which change rows
>based on values changed in previous steps. Changes have can have
>cascading effects. In effect I would have to make my script emulate the
>database engine.
One approach I have used on occasion is to query for a record,
present a page to edit values BUT WITH THE ORIGINAL VALUES IN HIDDEN
FIELDS. When the user submits the form, if any of the database
values have changed with respect to the original values in the form,
abort the change, otherwise accept it if the new values are acceptable.
Among other things, assuming the user actually made a change, is
that double-submitting the form is rejected. If anyone changed a
value which the user might have used to decide on his edits, the
change is also rejected.
What you could do for Page 2, for example, is to apply the changes
the admin submitted in Page 1, produce a Page 2 with the decisions
to be made, then roll back the changes. When the admin submits
page 2, make sure that applying the changes for Page 1 come out the
same, then apply the changes for page 2, produce a Page 3 with the
decisions to be made, then roll back the changes. This can get
very messy if you routinely end up with trivial differences like
the values of auto-increment primary keys that are otherwise
insignificant as long as the references all match up.
>A DBA said that I should avoid transactions that are open while waiting
>for user input, but considering how much of our system is built with
>stored procedures, I'm not sure how I can do that.
Good advice.
>One suggestion was
>to use temporary tables with a copy of the data I need, but those are
>also persistent only in a single database connection.
Another possibility is to use a temporary copy of the data you
need in a session, or in a update-in-progress table keyed to the
session. (Session data can be stored in a database rather than
temporary files by providing a handler).
>My limited understanding of transactions is that rows are locked only
>when the transaction is committed. At least this is how it works in
>PostgreSQL. Other users are isolated from my changes during my
>transaction.
So what happens two independent DB connections try to edit the same
record in incompatible ways? One has to wait? One gets aborted?
The user making the second change may NOT want to be "isolated"
from the first change, as the old data may be used to decide what
the new values are.
>It seems like I would have this dilema even if I were writing a desktop
>application. A user could start the application and then walk away from
>the desk. For that matter you would have the same problem if a user
>logged into a shell; started psql; typed "begin;" and then walked away.
No, I think you'd need to update a heavily-used record or many of
them, THEN walk away, to cause havoc. And if record-level locking
is done, you'd only tie up the records you changed. That might
still be enough to lock out the overnight billing process, leading
to some furious admins.
>At this point, I think I'm going to request a new database instance. I
>could copy the data I need from production to the scratch database;
>edit the data on the scratch database; then syncronize the data when
>the sysadmin is finished. The data I need is only a few megabytes, so
>performance shouldn't be an issue, but it seem like a lot of
>development overhead.
Is there a way to modify some of the stored procedures to optionally
return what they would do without actually doing it? Or doing
it to a different set of tables/databases?
Gordon L. Burditt
.
- References:
- Single database transaction across a multiple HTTP requests?
- From: noah
- Re: Single database transaction across a multiple HTTP requests?
- From: noah
- Re: Single database transaction across a multiple HTTP requests?
- From: Gordon Burditt
- Re: Single database transaction across a multiple HTTP requests?
- From: noah
- Single database transaction across a multiple HTTP requests?
- Prev by Date: Re: Single database transaction across a multiple HTTP requests?
- Next by Date: Re: Automatic refresh of screen.
- Previous by thread: Re: Single database transaction across a multiple HTTP requests?
- Next by thread: Re: Single database transaction across a multiple HTTP requests?
- Index(es):
Relevant Pages
|
