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

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.

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.

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.

I might be wrong, however, in surmising the OP's intent ... in which case I apologize.

-- 
/Karsten
DBA > retired > DBA