Re: lock Oracle db objects, but only a lock for others?

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:ut1pa.588839$S_4.641290_at_rwcrnsc53...
> 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

That's exactly what we do. We set the 'session_id' = to the handheld identifier. There is no locking (beyond the original session id assignment) except for application logic that will not allow someone to select a job to download to their ready that is already download to another the handheld. (based on that session_id being set. And there is an admin report to show which job is assigned to which handheld (and which user)) When the user comes back and uploads the data, regardless of what happens, the job is freed (or completed) for the next user. And there is always query capability in the scenario.

I guess I didn't explain well.

> >
> > >
> > > 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 - 22:06:18 CDT