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.
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?
Thanks in advance
...Jelle