Kbase P15662: How does the Optimistic Update technique work with Oracle DataServer?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
22/06/2009 |
|
Status: Verified
GOAL:
How does the Optimistic Update technique work with Oracle DataServer?
GOAL:
How do optimistic updates work with Oracle DataServer?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
Oracle DataServer
FIX:
The technique of optimistic updates to allow you to retrieve and update only the fields you need if you use field lists in your application needs to update records. The DataServer uses ORACLE handles the record-locking when the update occurs. To Activate this feature specify -Dsrv optimistic when you start the DataServer.
NOTE: Optimistic updates are not allowed for LONG, RAW, and LONG RAW columns.
Typically, an application has to obtain a record with an EXCLUSIVE-LOCK (either by explicitly specifying the EXCLUSIVE-LOCK or by a SHARE-LOCK that is upgraded to EXCLUSIVE-LOCK). Optimistic updates allow changes to be made to records that you retrieve NO-LOCK. Since field lists require that you obtain records with NO-LOCK, you can use field lists combined with optimistic updates to perform updates without retrieving the entire record. For example, the following code is acceptable if you specify the -Dsrv optimistic startup parameter:
FOR EACH Customer FIELDS (cust_num name) NO-LOCK:
UPDATE name.
The DataServer generates SQL similar to the following:
SELECT cust_num, name FROM sports.customer
UPDATE sports.customer SET name=:x1 WHERE cust_num=:rid AND name=:o1
The bind variable :x1 represents the new value for the name column and :o1 supplies the old value. The clause, WHERE cust_num=:rid, specifies which row to update (in this example cust_num supports the Progress ROWID function). The name=:o1 portion of the WHERE clause prevents the UPDATE from taking place if another client has changed the name column while your client was holding it NO-LOCK.
The DataServer instructs ORACLE to compare the old value of name to its present value. If the values are the same (indicating that no one changed the record while your client held the record NO-LOCK), ORACLE updates the field. This feature enhances performance by reducing concurrency problems resulting from locks held for long times and by reducing network traffic as you can send only those fields you want to update.