Re: procedure based block locking

"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