Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: lock Oracle db objects, but only a lock for others?

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

From: Bob Kilmer <rprgrmr_at_yahoo.com>
Date: Sun, 20 Apr 2003 17:25:46 GMT
Message-ID: <uKAoa.56990$D15.1625984@twister.tampabay.rr.com>


Jim,
Thanks for your response. I am new to the app and the company. I didn't invent the locking and unlocking chaos, I am trying to cure it. I do read the docs online and I will look at what you suggest, but it takes a while for it all to sink in and to find just the right answer and not just a generic lesson in locking database objects.

Now that I've finished being defensive, let me say the locking that the code uses is a 'select for update', sometimes with and without a nowait, between a begin trans and commit or rollback. But the code places a lock that gets in its own way! So it commits (or rollsback - who knows - On Error Goto!) to release the lock when it passes control to whatever code it thinks might need access to the data that it would prefer to remain locked. And that code sets a lock again. (Or should. Or maybe not?) And this happens again and again as the client opens and closes form after form getting or posting data, locking and unlocking data, starting and ending transactions, before the finally the process is through.

Besides all that, the app doesn't really lock all the data up for grabs. The app really just locks a mutually agreed upon row in a particular table. This lock then serves as a flag to other instances of the app not to fool with the data. Not that one couldn't fool with the other data that is not locked. So if all the clients are using this as a flag, they may as well use an explicit flag, i.e., a flag table value, unless the app can set a reasonable lock that won't get in its own way, don't you think?. Intuition tells me that this lock, unlock, lock unlock madness is bogus, especially when it encumbers the code so, and isn't really locking all the data that is exposed.

Should the client be using db locking at all?

Can the client set a legitimate lock that won't get in its own way?

Would it be just as well to set a flag in a table of flags? (That is, in effect, what is being done now - except it is a table of real data!)

Is there another option that is cleaner, easier and more professional that I have yet to discover?

I would prefer to be able to set a lock or a flag exactly once at the beginning of a client input or edit process, do the input or editing machinations, then unlock or lower the flag (once) when I am out of there.

Regards,
Bob

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:T0Aoa.12552$Si4.6065_at_rwcrnsc51.ops.asp.att.net...
> Fix the application.
> Oracle!=SQLServer. Read about Oracle's locking and multi versioning
scheme
> in the docs. (www.otn.oracle.com, sign up for free and access all the docs
> on line). At worst I could see a select for update statement. I think
the
> problem is you are trying to get around SQLServer's limitations.
> Don't do the same thing in Oracle you do in SQLServer; doesn't make sense
> usually.
> Jim
>
> --
> 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.
> "Bob Kilmer" <rprgrmr_at_yahoo.com> wrote in message
> news:h7zoa.56244$D15.1616373_at_twister.tampabay.rr.com...
> > My question concerns locks.
> >
> > I am a developer new to a VB6/Oracle 9i client-server application and
> > somewhat new to Oracle although I have developed against SQL Server and
> > Access quite a bit. One to several (3,4,...,10?) clients located on
> separate
> > machines use this Oracle database - no more. No other application or
> process
> > out of our control accesses this DB.
> >
> > The practice has been to explicitly lock particular rows in particular
> > tables under certain circumstances - while some input is pending, for
> > instance. Other clients trying to perform similar operations on the same
> > data may try to lock the same rows, only to be turned away. This
'turning
> > away" is acceptable - it just means that other clients should not
> interfere
> > with what is going on with those data and the data locking those records
> > implies.
> >
> > The problem arises in managing the code and the locks. In the course of
> some
> > user input process at the client GUI, many sub routines might need to
> access
> > the data that will have been locked when the input process was
initiated.
> > The locks are released and reinstated repeatedly as each bit of code
does
> > its thing. The code is not well modularized, so often code drops a lock,
> > anticipating a need for data access by subsequent code somehwere else,
or
> > reinstates a lock, anticipating that it should in case the other
spaghetti
> > code forgot to, or whatever - who knows? There are occasionally module
> level
> > or global booleans, but IMHO, this just adds to the quagmire.
> >
> > Can the client set a lock on Oracle db objects that is only a lock for
> > others?
> >
> > Wouldn't it be just as effective if each client set a flag in some table
> or
> > other that, in effect said, "do not disturb - I am working here", then
> > cleared it when done?
> >
> > Something like this would help make this "lock-unlock-lock-unlock-(hope
no
> > one jumps in!)-lock?-lock?-unlock?-..." nightmare go away.
> >
> > Help, please!
> >
> > Bob Kilmer
> >
> >
> >
>
>
>
Received on Sun Apr 20 2003 - 12:25:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US