Comments embedded.
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Eric Fortin" <emfortin_at_attbi.com> wrote in message
news:LHWoa.317541$Zo.63196_at_sccrnsc03...
> Embedded:
>
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.190dd07bb03d338b989741_at_news.la.sbcglobal.net...
> > gooiditweg_at_sybrandb.demon.nl said...
> > > On Mon, 21 Apr 2003 14:44:43 GMT, "Eric Fortin" <emfortin_at_attbi.com>
> > > wrote:
> > >
> > > >
> > > >
> > > >"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> > > >news:79v7av8ldp39d5jpcv86l90lck6khs0ljg_at_4ax.com...
> > > >> On Mon, 21 Apr 2003 13:06:40 GMT, "Eric Fortin"
<emfortin_at_attbi.com>
> > > >> wrote:
> > > >>
> > > >> >
> > > >> >Again, I think this is clearer and easier to maintain than coded
> flags.
> > > >> >
> > > >>
> > > >> Good luck then with re-inventing the wheel and trying to screw up
> > > >> Oracle on a structural basis.
> > > >>
> > > >>
> > > >> Sybrand Bakker, Senior Oracle DBA
> > > >>
> > > >> To reply remove -verwijderdit from my e-mail address
> > > >
> > > >What's the better solution? And how is that re inventing the wheel.
> We
> > > >have rows that need to be worked on by a single user and only a
single
> user
> > > >for extended periods of time. And the table still needs to be
> accessible
> > > >for other processing. How would you problem solve this? I'd like to
> see an
> > > >example or doc on this. I searched tahiti.oracle.com, but kept
getting
> no
> > > >matches.
> > > >
> > > >(We've got users that take a handheld, go out into the field
> (literally, to
> > > >work on pipeline and/or oil wells), and come back 10 hours later. We
> don't
> > > >know whether or not they will get one of the jobs, half of the jobs,
or
> all
> > > >of the jobs done. And we don't know the onsite intangibles. What's
> wrong
> > > >with setting a column that essentially says, this row is being worked
> on and
> > > >must stay in this state until the worker is done). How do you handle
> this
> > > >scenario in the database without setting something? In this example,
> there
> > > >isn't any code variables that can be set. The devices are all
> disconnected
> > > >and their own entitiy.
> > > >
> > > >And what about a web data entry form that is accessed by many users?
> (Say
> > > >an order header form)How do you prevent additional users from over
> writing
> > > >the changes of the initial user. (user 1 brings up the header info
and
> > > >decides he wants to change the credit card information) User 2,
> working a
> > > >similar problem with all orders, brings up that order screen (and
> happens
> > > >upon that specific order) and saves it fifteen minutes after user 1
> (what
> > > >happens to the credit card information. Which is the correct credit
> card
> > > >number? the one saved by user 1 or the one saved by user 2).
> Certainly
> > > >others have come across this problem. (I would bet that the
solution
> for
> > > >this scenario would apply to the above hand held scenario). How do
> other
> > > >people do this (assuming that setting a column on that row is the
wrong
> way
> > > >to go about it, and I know that it causes havoc when the user closes
> the web
> > > >browser and the column stays populated)?
> > > >
> > > >What is the right way so I don't create any more problems.
> > > >
> > >
> > > First of all: Oracle has multi-versioning. This means the end-user
> > > will always get to see the correct version of the record. In Oracle
> > > readers don't block writers, in sqlserver readers do block writers.
> > > You seem to have been exposed to sqlserver.
> > > Get the Expert-on-one book by Thomas Kyte and read the first three
> > > chapters. It's all there!!!
> > > You should lock a row only when you really are going to change
> > > anything. NOT when you SELECT a row. We use a generic application
> > > which can run against any database implemented this way, and their
> > > 'locking' (by means of the flag mechanism you describe) is a REAL pain
> > > in the ass. It means no 2 readers can ever read the same record, even
> > > if they don't plan to save anything.
> > >
> > > Oracle has mechanisms to abort a session which is inactive for a
> > > prolonged period of time. Allowing a lock to stay for 10 hours is
> > > just plain ridiculous.
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> > >
> > PMFJI, Sybrand, but I think you might be overlooking one of the OP's
> > points.
> >
> > Yes, Oracle has all of that stuff built-in that is lacking in MS SQL
> > Server ... however, Oracle is expecting the "client" to remain connected
> > in order for its features to be effective.
>
>
> >When the OP says that his
> > customers might download the "current view" of the world to their PDA,
> > then go out into the field to update that current view, then come back
> > to the office to upload their changes ... then that's a very different
> > scenario than what Oracle's architecture is expecting.
>
> That's the exact scenario.
>
> We need to prevent two different technicians from going to/getting
assigned
> the same job. (it can be as far as a 200 mile drive in the middle of
> nowhere to fix a drill). We also need to track which technician is doing
> which job. And we cannot complete the job until it's done.
(Technically,
> we may/usually send two + technicians, each with a set of specific tasks
on
> each hand held. )
>
I would imagine this is an application problem not a DB lock problem. What
I mean is that if I am a technician and I download a list of jobs I am going
to do to my PDA then I imagine there is a table that holds the information
about who is assigned to the job. I also imagine I would only download to
my PDA jobs that are available - ones that have not been assigned to anyone
else. This is not a database lock this is an indication of the state of a
job - repair person assigned. Here is how I envision the work flow going:
1. I look at the avaiable list of unassigned jobs. (select jobs that have a
status of unassigned or similar criteria)
2. I select a list of jobs that it is highly likely I am going to do. (in a
particular area, I have that particular skill, I have the time to do the
task etc.) (update job x, y, and z with the state assigned and make sure I
am the assigned technician)
3. commit;
4. I disconnect my PDA from the system. (At this point others can choose
jobs that have a state of unassigned)
5. I go do most of the jobs.
6. That evening I synch up my PDA with the database (or my piece of paper)
and those jobs that I completed I move to a state of completed by me. Those
that I have not completed I either keep or I release (keep them assigned to
me or make them unassigned).
7. Commit;
I have not locked anything for an extended period of time; I don't need to-
I have indicated in the data what is going on in real life. This is data
modeling.
I don't want people not to be able to read the rows I updated, au contraire
I want them to see that I have assigned them to myself (or a dispatcher has
assigned them to someone). I think some basic business analysis of what is
going on and how to model it in the data would be most helpful instead of
some unusual locking scheme.
Jim
>
> >
> > The OP's other point about the statelessness of web apps is a constant
> > problem. If I connect, select, disconnect ... then Oracle thinks I am
> > done with my transaction. It has no way of knowing that later I'll
> > connect, update credit card info with out-of-date data, disconnect. If
> > I'm a legitimate updater of info, then Oracle will not prevent me from
> > updating a row with out-of-date data.
> >
>
> Yes, That's the exact scenario with which I'm faced. Exactly.
>
>
> > Remember, a lot of Oracle's protective architecture was developed for a
> > traditional client-server model ... not for a stateless model ...
>
>
> >like
> > me taking a copy of what I think is my personal data offline and later,
> > coming back online to update what I think is my personal data, only to
> > undo someone else's changes. I think that's what the OP is trying to
> > prevent.
>
> Bulls Eye.
>
> >
> > I might be wrong, however, in surmising the OP's intent ... in which
> > case I apologize.
> > --
> > /Karsten
> > DBA > retired > DBA
>
>
> Thanks. You've got my problems down exactly; now, what's my best
solution
> for these scenarios. I use the session_id as described for this task.
> What's the better way?
>
>
Received on Mon Apr 21 2003 - 21:07:22 CDT
