Kbase P124276: Find with exclusive lock waits on records not matching WHERE clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/5/2009 |
|
Status: Verified
SYMPTOM(s):
Client executing a FIND ... EXCLUSIVE-LOCK statement on a database table
Not all fields in WHERE clause are indexed
No records that exactly match the WHERE clause are locked
Other client is locking records in table that partially satisfy WHERE clause used in FIND statement
FIND ... EXCLUSIVE-LOCK statement waits on record lock
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
This is expected behavior.
During a FIND operation, all records that need to be read to evaluate the WHERE clause will be read with the locking level specified. This needs to be done to ensure that the outcome of the FIND is correct according to that locking level. (Although the FIND will release the lock as soon as the record has been analyzed, if it was not the correct one to return).
This does mean that there are condition in which where a FIND EXCLUSIVE may be waiting on records that do not exactly match the criteria in the WHERE clause.
If not all the fields in the WHERE clause are in an index, the FIND needs to read multiple records to evaluate the non-indexed fields and thus it can end up waiting for a record for which the indexed fields in the WHERE clause match, but the non-indexed fields are different.
Even more obvious will be the case where the FIND statement can not be resolved using an index, and a full table scan must be performed to locate the record. In this case, the FIND ... EXCLUSIVE-LOCK will try to get a lock on all records in the table, and will thus wait on all record locks from other clients.
FIX:
Possible fixes:
1. Perform the initial FIND using the WHERE clause with NO-LOCK, then upgrade the locking level using a FIND CURRENT ... EXCLUSIVE-LOCK statement.
2. Ensure all fields in the WHERE clause are defined in a single index, either by modifying the WHERE clause or by adding the appropriate index to the table.