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

From: Eric Fortin <emfortin_at_attbi.com>
Date: Mon, 21 Apr 2003 18:24:43 GMT
Message-ID: <LHWoa.317541$Zo.63196@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. )

>

> 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 - 13:24:43 CDT