Re: data integrity - Mailing list pgsql-general

Keep reading, I have commented below.

--- Janning Vygen <vygen@gmx.de> wrote:
> Am Dienstag,  6. November 2001 00:17 schrieb Mart�n
> Marqu�s:
> > On Lun 05 Nov 2001 18:06, you wrote:
> > > Hi
> > >
> > > I'm using postgresql to manage a web site.
> > >
> > > Problem: How are people dealing with data
> integrity issues such
> > > as stale data when writing web-based apps? For
> instance:
> > >
> > > The value of X is 7.
> > >
> > > Step (1) User A selects X.
> > > Step (2) User B selects X.
> > > Step (3) User A updates X to 8.
> > > Step (4) User B updates X to 10 under the
> assumption that X is
> > > still 7.
> >
> > Lock it with a SELECT ...... FOR UPDATE. The rows
> that are going to
> > be updated will be locked, but not the entire
> table. :-)
>
> I dont think that FOR UPDATE helps in this
> situation, but it might be
> a misunderstanding.
>
> FOR UPDATE locks the rows of the table for an
> update. right. but it
> removes the lock if the transaction commits.
>
> In a web based application you cant enforce a
> transaction between the
> sending of a form and the exceuting of a submit.

And even if you could, you probably wouldn't want to.
If you did, it would allow folks to lock up rows in
your database indefinitely simply by requestin a form
and closing their web browser.  Not only that, but it
would require that each database user get their own
PostgreSQL connection, and your middle ware layer
would have to map requests to the correct connection.

In the end it is *much* easier to simply make each
request a transaction.  If an error is raised, then
everything is rolled back, if not then everything is
committed and the page is served up.

If you *really* need something like this it is better
not to use PostgreSQL's transaction mechanism and
instead create an extra table that holds the primary
key of the records you want locked and a timestamp
(and probably the user id or session id of the person
who has created the lock).  That way you can easily
check this table for which records are being updated
and you can also easily time out locks.

This is especially useful in those cases where
updating a record takes a lot of time.  If all you are
doing is updating a simple integer then a locking
table is not likely to help.  Each individual will
hold the lock for such a short period of time that the
chances of collision are low.

> Its an application problem: the data i see in my
> frontend are never
> realtime. they might have change in the last
> seconds. But who cares.
> If i want to have a value of 8 and another one wants
> a value of 10
> its a question of whom granting write access.

This sort of a setup is certainly easiest.  However,
sometimes it is useful to alert the user to the fact
that someone else is also working on this particular
row.  Creating a separate table to track which rows
are being accessed is fairly straightforward and not
particular expensive.  Trying to use PostgreSQL's
locking mechanisms (on the other hand) is a short road
to disaster.

Janning is certainly correct, however, when he says
that ultimately it comes down to who has write access.
 Even using a locking table there is nothing to stop
someone changing that row right after I finish
changing it.  They won't be able to change it *while*
I am working on it, but they'll be able to change it
the second I hit "submit."

Jason

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com