Re: Database "Transactions" and HTTP statelessness

On Tue, 28 Aug 2001 23:55:32 -0300, mlemos@acm.org (Manuel Lemos) wrote: >The solution is to pass data between pages and only commit it when you >are done with all the data that you need to grab from the user. >Transactions can't be kept between HTTP requests, but you can pass data >between pages so you keep the choices of the user that reaches to the >end of your process. that's not really addressing my concern. i'm not worried about the coding details of multiple forms and such. i'm concerned about data consistency. in case i wasn't clear enough in the original post: let's say each row has an identifying serial number, id: User A loads and edits the row with id=1. row is not yet saved. lastname and state fields modified but only in the browser for now, not yet POSTed to the server. state is set to SC. lastname is, ah, "Lemos". User B loads and edits the row with id=1. row is not yet saved. state field is set to NC. no other fields edited. User B SAVES his edits. on disk, the row iwth id=1 now has state set to NC. User A SAVES his edits. on disk, the row with id=1 has the changed lastname (maybe a problem, but probably not), and state is set to SC. the lack of row locking has made B's change of the state disappear. and it might be that B's change is the correct one. so now A has the wrong state data. ideally, if B tries to save his edits, the system would tell him that A (or someone, possibly unspecified) has the lock on the row and the edit cannot be saved. B can then decide what to do (wait before trying to save again? reload the data so that he gets latest data? before loading the record, try to get a select for update, so that the select won't even return until the row is available for editing and B OWNS the lock (the select won't return until the lock is available, or it may return but with error so that B knows that he doesn't have the lock). an ugly alternative i've considered is keeping the original (unedited) data in session variables and BEFORE WRITING THE EDITED DATA, first get a select for update (which locks the row), check if the data has changed, and only if it hasn't, do the write. if the data is not the same as the original unedited data, then someone else has modified the data and we reload the record and allow edits by the user before trying to write again (possibly with some smart features to minimize retyping of edits). there are also non-transaction based hacks one of which would involve having a lock file that stores locks owned by particular logical "sessions". all database access would then first check to see if there is a lock record for the particular row being requested. if it's locked, then the row can still be viewed, but it would be read-only. if a 'session' owns the lock, then the row would be editable (there would be a "Submit" or "Update Database" button, which would not be there ifthe row is read-only because some other session owns the lock). there would then be some sort of regular lock clearing program which would clear out locks which had timed out. >You may want to take a look at this multi-page forms that may help you >to keep data between forms that can be walked back and forth. thanks for the reply, but that's not the problem i'm worried about :). tiger