Keith,
On Mon, 26 Jun 2000, you wrote:
> Hello,
>
> In a CGI type of web environment, user "A" wants to update a certain row of
> data, so he fetches it for display in his browser. User "B" fetches the same
> data shortly after user "A" fetches the data (but before user "A" updates).
> User "A" updates. Then user "B" updates, resulting in user "B" overwriting
> the update of user "A", but never seeing user "A" updates. This is because
> in a typical web environment, the database connection is closed (OR randomly
> pooled) with each http request, thus not taking advantage of oracles
> "A.C.I.D." transaction qualities (if you need to ask, then you are not
> qualified to answer)....
So you're putting a mission critical system together visa vi a web
interface?..., hmm, that's unusual. Well then you've probably got a whole
different subset of conditions underwhich your application is running. (IE
this sounds intranet as opposed to internet)
The two suggestions by John & Frank were good. I'll give you mine, but it
sounds like you and I are working with a whole different spectrum of audiences.
Okay, what I would do is similar to what was aforementioned and that's a
timestamp in the users browser that says when the information was lasted
viewed. (IE when they are getting the data that they are requesting to make an
update) Then check the tables altered timestamp (automagically set via a
pl/sql trigger, or someother clever mechanism) and see if it's before that
date. If it is, send the user the updated information to his browser and tell
him he's going to have to be quicker next time :-).
>From my perspective access to data is almost always more important than the
ability to modify the data. You need for everyone to be able to view the data,
but only for people to modify it one at a time, taking into account others
changes. My view or outlook may be askew to what your doing in this project
though. YMMV. (This is sort of similar to a modification level semaphore. It's
pretty common in applications, and that is you have a bunch of threads
accessing shared data. You allow as many threads as they want access to read
the data, but only one can write, on a request to write it has to wait until
all others are done reading. Of course we're dealing with humans and not
threads so we can do things a bit more intuitively with the timestamp business.)
>
> What should happen is something like ===> user "A" does a "select for
> update", and then when user "B" goes to fetch the data he is informed that
> the data is locked. But to do this you need to keep the session (whether
> apache:session or not) bound to a single, exclusive db connection across
> http requests..... or so I think....
No you don't need a session, just a timestamp. As simple as putting it as a
hidden field in the form.., i.e. popping a good old seconds since Jan 1 1970
aka unixdate.
> Does anyone have any experience with this?
In the thread sense were I have to make sure that multiple threads don't trash
eachothers data, yes. But in an interface sense, no..., but it's all the same
at some level.
Have a good one Keith,
Shane
