Kbase P161574: FIND and GET FIRST|NEXT|PREV|LAST differences when connected remotely vs locally
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/13/2010 |
|
Status: Verified
SYMPTOM(s):
FIND and GET FIRST|NEXT|PREV|LAST differences when connected remotely vs locally
Differences between shared memory(local) and remote connections when attempting to lock a record held by another user when fetching a record by position rather than using a unique reference
Different behavior from FIND by position EXCLUSIVE-LOCK for local vs remote connection
Differing behavior between local and remote connections when using FIND LAST and incrementing a field value rather than using a sequence
FIND LAST EXCLUSIVE-LOCK difference when client connection is local versus remote
FIND NEXT EXCLUSIVE-LOCK difference when client connection is local versus remote
FIND PREV EXCLUSIVE-LOCK difference when client connection is local versus remote
FIND FIRST EXCLUSIVE-LOCK difference when client connection is local versus remote
GET LAST EXCLUSIVE-LOCK difference when client connection is local versus remote
GET NEXT EXCLUSIVE-LOCK difference when client connection is local versus remote
GET PREV EXCLUSIVE-LOCK difference when client connection is local versus remote
GET FIRST EXCLUSIVE-LOCK difference when client connection is local versus remote
Code to demonstrate the problem:
DEFINE BUFFER b<table> FOR <table>.
/* client #2 gets a 2624 message here and is prompted whether they wish to wait for the record lock to be released */
FIND LAST <table> EXCLUSIVE-LOCK NO-ERROR.
CREATE b<table>.
b<table>.<keyfield> = <table>.<keyfield> + 1.
/* local client #2 gets a duplicate record validation message here */
** <file-name> already exists with <field/value...>. (132)
<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
CAUSE:
When using FIND {LAST|NEXT|PREV|FIRST} EXCLUSIVE-LOCK ? or FIND {LAST|NEXT|PREV|FIRST} EXCLUSIVE-LOCK ? there may be an apparent difference in record returned depending upon how the client is connecting to the database. This is expected behavior.
Local/Shared Memory: When using FIND or GET {LAST|NEXT|PREV|FIRST} with an EXCLUSIVE-LOCK, if the record that is being attempted to be fetched is already locked (i.e. by another client) then in local/shared memory connections the client will attempt to re-FIND/GET the current (i.e. currently locked) record until that record becomes available. Once available, the originally locked record will be fetched EXCLUSIVE-LOCK. The FIND/GET statement is not re-executed, so if new records have been added to the table, they will not be considered by the currently executing statement.
Remote connection: When using FIND or GET {LAST|NEXT|PREV|FIRST} with an EXCLUSIVE-LOCK, if the record that is being attempted to be fetched is already locked (i.e. by another client) then in remote connections (-H ?N ?S) the remotely connected client does not attempt to re-find the originally locked record, rather it re-executes the FIND/GET statement. Therefore if new records have been added to table, the newly added {LAST|NEXT|PREV|FIRST} record would be returned if they meet the appropriate criteria.
Both of these behaviors are correct and expected due to the nature of the agent handling the request.
Note that for Unique FINDs and FOR EACH operations the nature of your connection to the database should does not matter because there is no cursor movement implied when attempting to lock a specific record. If a record has been re-keyed or deleted in the meantime an error will be returned indicating that the record doesn't exist or skipped as the case may be.
FIX:
One could use sequences and allow the ABL to handle the details of locking and waiting. Alternatively perform the record retrieval operation in a loop with NO-WAIT to avoid contention.
/* example */
DEFINE BUFFER b<table> FOR <table>.
REPEAT:
FIND LAST <table> EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF LOCKED <table> THEN NEXT.
LEAVE.
END.
CREATE b<table>.
b<table>.<keyfield> = <table>.<keyfield> + 1.