procedure based block locking

[Chris Boyle]

Good morning,



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?



Thanks

[Mark C. Stock]

"Chris Boyle" <cboyle@hargray.com> wrote in message
news:zPZ6c.11763$TV6.2585@lakeread02...
| Good morning,
|
|
|
| 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?
|
|
|
| Thanks
|
|

the procedure you call in your on-lock should check if the row has been
changed since the user last retrieved it

forms usually does this by comparing all columns -- a better way would be to
have a row version id or a modified_date column that you could compare with
the value the user is expecting

;-{ mcs

[Chris Boyle]

Thanks for the idea, i will try it today
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:CeidnTN6c5eU7MHdRVn-gw@comcast.com... "Chris Boyle" <cboyle@hargray.com> wrote in message news:zPZ6c.11763$TV6.2585@lakeread02... | Good morning, | | | | 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? | | | | Thanks | | the procedure you call in your on-lock should check if the row has been changed since the user last retrieved it forms usually does this by comparing all columns -- a better way would be
to have a row version id or a modified_date column that you could compare
with the value the user is expecting ;-{ mcs

[Billy Verreynne]

"Chris Boyle" <cboyle@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

[Chris Boyle]

"Billy Verreynne" <vslabs@onwe.co.za> wrote in message
news:1a75df45.0403250009.74c060b0@posting.google.com... "Chris Boyle" <cboyle@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.