Re: procedure based block locking

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0403250009.74c060b0_at_posting.google.com...
> "Chris Boyle" <cboyle_at_hargray.com> wrote in
>
> > I have a form block that is based on a procedure that passes a table of
> > records in and out. This form is going to be used by multiple user
looking
> > at the same data so an on-lock procedure has been included as part of
the
> > package. This is working when user B tries to update the same record
that
> > user A is in the process of updating. I can catch the ora 00054
exception
> > and prevent B from doing anything as long as the record is locked. What
I
> > have not figured out is how to force B to re-query the record after A
has
> > updated and committed it. Since the block data is stored in a pl/sql
table
> > it does not automatically pick up the changes so I can have a phantom
update
> > occur. Is there a standard way to handle this type of processing that I
> > have not yet found? If so, could you point me in the right direction or
> > provide some ideas on how you handle this?
>
> Chris, I'm not exactly clear as to what and why you are doing this...
> ideally one should not re-invent the wheel. Oracle comes standard with
> a very well behaved and scalable transaction/locking feature for data
> processing. I will be very hard pressed to decide not to use this.
>
> If you need to store data outside an Oracle table... that does not
> sound to me like a great idea. Why forego on the best feature of
> Oracle - data processing & management?
>
> If there is really and truly no other way to do this, and I need to
> create a shareable PL/SQL resource of sorts that requires locking, I
> would use DBMS_LOCK for doing my lock management. If clients are
> interested in knowing when the resource have been unlocked and is
> available, I would register a DBMS_ALERT that interested clients can
> subscribe to.
>
> I would also however try implement some form of scalability by
> preventing as far as possible, serialisation to my custom PL/SQL
> resource - serialisation is the next biggest performance issue after
> i/o. However in Oracle, I have found this to be very seldom an issue
> (unlike when dealing with some other commercial database products).
>
> For example, using DBMS_PIPE it is possible to implement a
> client-server architecture inside Oracle that allows you to have
> several servers servicing client requests, thus eliminating a single
> server process as the sole resource by providing a pool of server
> processes as the resource.
>
> --
> Billy

there are actually two tables of data involved with a many to many relationship that we handle with an intersection table. That kills the master detail relationship. We have also thought of using updateable views but those are new to us also. Thanks for the suggestions, we will try them next week. Received on Sun Mar 28 2004 - 14:59:32 CST