Re: data integrity

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.

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.

janning


> > In some scenarios, user B should be prevented from updating.
> >
> > Solutions (that I'm considering):
> >
> > (1) Timestamp: Have a timestamp for every row in every table,
> > which automatically changes when a row is inserted or updated, or
> >
> > (2) Cache: Similarly, cache the user's selected data on the
> > server side (every tuple from the original select) and compare
> > when the user attempts to update, or
> >
> > (3) Squash: modify only those fields in the row that need
> > updating (instead of the entire row) and whatever happens
> > happens.
> >
> > Has anyone solved this 'stale data' problem before?
> >
> > Does this fall into a family of problems that has an existing
> > (trivial) solution? Also, is it the responsibility of the
> > application server to deal with the scenario above or should the
> > application itself be responsible?
>
> saludos... ;-)

--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de