Re: data integrity

I deal with it in an application specific manner.

In one case- row contains FAQ style item - I mark the row as locked by a
particular user/session. If other users try to update the same row, a copy
is made. So the end users have to combine their work.

In another case - rows are given an application level transaction ID, only
rows from committed transactions are visible (did this in MySQL some years
back). The transactions are only for a very limited controlled scope.

Trying to fully implement application level MVCC/transaction might be
possible but difficult coping with cases where a user opens multiple
browser windows or jumps from state to state (history/back button), or goes
for lunch...

Can an application server really deal with this problem? If anyone can show
how, I'd like to know!

Keeping a database connection open between webpages won't work well
(multiple windows = self conflicting transaction) or scale (many
users/sessions per timeout period= run out of DB connections) .

As a kludge you can hold open a web connection to keep track of stuff - a
download that takes a very long time- displaying very little data at a
time. But it's ugly and sometimes certain browsers just randomly disconnect
after a while..

Cheerio,
Link.

At 04:06 PM 11/5/01 -0500, Jelle Ouwerkerk wrote:
>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.
>
>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?