Kbase P16788: How do share-locks and exclusive-locks work with Oracle Data
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/25/2003 |
|
Status: Verified
GOAL:
How do share-locks and exclusive-locks work with Oracle DataServer?
GOAL:
How do share-locks work?
GOAL:
How do exclusive-lock work?
FACT(s) (Environment):
Oracle DataServer
FIX:
The DataServer log file inserts in this example was demonstrated using sports2000 database with the shadow column support.
1. When used SHARE-LOCK with the DataServer to update the oracle database, there are three SQL being generated:
The Progress 4GL update statement is:
FOR EACH customer WHERE customer.name= "j-sport":
UPDATE customer EXCEPT comment.
END.
a. With the first SQL the DataServer retrieves all the rows from the specified table in the oracle
database including the PROGRESS_RECID values with the where clause having the shadow column
if shadow column support was used with NO-LOCK.
10:34:08 OCI call oparse <4> sqlcrc = 8662
10:34:08 SELECT /*+ INDEX_ASC(T0 CUSTOMER##NAME) */ PROGRESS_RECID unique
10:34:08 _id_0,CUSTNUM,U##COUNTRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRESS2,CI
10:34:08 TY,STATE,U##POSTALCODE,POSTALCODE,CONTACT,PHONE,U##SALESREP,SALES
10:34:08 REP,CREDITLIMIT,BALANCE,TERMS,DISCOUNT,COMMENTS,FAX,EMAILADDRESS,
10:34:08 PROGRESS_RECID FROM CUSTOMER T0 WHERE (U##NAME = upper(:1))
b. With the second SQL the dataserver selects the only rows needed to be updated in the oracle database
with the exclusive-lock with where clause having the PROGRESS_RECID=:rid (the PROGRESS_RECID values that
were retrieved with the first SQL) for the updates.
10:34:26 OCI call oparse <6> sqlcrc = 37939
10:34:26 SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUSTNUM,U##COUNT
10:34:26 RY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRESS2,CITY,STATE,U##POSTALCODE
10:34:26 ,POSTALCODE,CONTACT,PHONE,U##SALESREP,SALESREP,CREDITLIMIT,BALANC
10:34:26 E,TERMS,DISCOUNT,COMMENTS,FAX,EMAILADDRESS,PROGRESS_RECID FROM C
10:34:26 USTOMER T0 WHERE PROGRESS_RECID = :rid FOR UPDATE OF CUSTNUM NOWA
10:34:26 IT
After retrieving the values for the second SQL with the lock upgraded from SHARE-LOCK to EXCLUSIVE-LOCK, the dataserver compares the rows between the results from both of the above queries and sends the rows that needed to be updated to the progress client. This requires extra time to process the transactions, hence, lower the performance.
c. The final updates is performed when the client submits the changes to the oracle. The dataserver generates
the following update SQL to the oracle to update the customer's name and address:
10:34:27 OCI call oparse <7> sqlcrc = 59177
10:34:27 UPDATE /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUSTOMER T0 SET
10:34:27 U##NAME = :4,NAME = :5,ADDRESS = :6 WHERE PROGRESS_RECID = :rid
2. When used EXCLUSIVE-LOCK the dataserver also generates three SQL but does not select all the rows from oracle with the NO-LOCK:
The Progress 4GL update statement is:
FOR EACH customer WHERE customer.NAME= "j-sports"EXCLUSIVE-LOCK:
UPDATE customer EXCEPT comment.
END.
a. With the first SQL the dataserver retrieves only the PROGRESS_RECID from the specified table for only those
rows that were needed to be updated in the where clause having the shadow column if shadow column support was
used with NO-LOCK.
11:27:05 OCI call oparse <4> sqlcrc = 61734
11:27:05 SELECT /*+ INDEX_ASC(T0 CUSTOMER##NAME) */ PROGRESS_RECID unique
11:27:06 _id_0 FROM CUSTOMER T0 WHERE (U##NAME = upper(:1))
b. With the second SQL the dataserver selects all the rows needed to be updated with the PROGRESS_RECID =:rid
(the PROGRESS_RECID values that were retrieved with the first SQL) in the where clause for the updates in the
oracle database with EXCLUSIVE-LOCK and sends the rows to the progress client. In this transaction the
comparison between first and second queries was not performed. This causes the improvement in the
dataserver performance.
11:27:06 OCI call o.parse <5> sqlcrc = 37939
11:27:06 SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUSTNUM,U##COUNT
11:27:06 RY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRESS2,CITY,STATE,U##POSTALCODE
11:27:06 ,POSTALCODE,CONTACT,PHONE,U##SALESREP,SALESREP,CREDITLIMIT,BALANC
11:27:06 E,TERMS,DISCOUNT,COMMENTS,FAX,EMAILADDRESS,PROGRESS_RECID FROM C
11:27:06 USTOMER T0 WHERE PROGRESS_RECID = :rid FOR UPDATE OF CUSTNUM NOWA
11:27:06 IT
c. The final updates is performed when the client submits the changes to the oracle. The dataserver generates
the following update SQL to the oracle to update the customer's name and address:
11:27:18 OCI call oparse <7> sqlcrc = 59177
11:27:18 UPDATE /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ CUSTOMER T0 SET
11:27:18 U##NAME = :4,NAME = :5,ADDRESS = :6 WHERE PROGRESS_RECID = :rid
.