Statement level lock upgrade for JOIN query

OpenEdge ABL allows a user to specify a lock type in the OPEN QUERY statement of a JOIN query. An EXCLUSIVE-LOCK can be applied to individual buffers that are participating in a JOIN query. Opening a JOIN query when one or more participating buffers are opened with EXCLUSIVE-LOCK makes DataServer evaluate the query to be joined by the client. Such join queries are not evaluated on the server and their performance might be affected. OpenEdge MSS Dataserver supports statement level lock-upgrade for join query evaluated using server side join. To use this capability, Progress suggests that users should open a query with lock-type as NO-LOCK and specify the desired locking at the statement level. This allows DataServer to evaluate the query as server side join. DataServer caches the results set of ROWID index key values of participating join buffers depending on what lock type is specified when the first GET statement is executed. Users can now evaluate the server side solution to fetch the record with an EXCLUSIVE-LOCK on a join query opened with SHARE-LOCK/NO-LOCK and update the records.

For more information on how to run ABL queries and the syntax, see Progress OpenEdge ABL: ABL Reference guide.

The GET statement for FIRST, NEXT, and CURRENT are executed on the server side and for LAST and PREV are evaluated on the Client side and not the server side. To use SCROLLING capability and use GET LAST or GET PREV statement, an ABL application should use QUERY-TUNING switch (NO-JOIN-BY-SQLDB) to evaluate the statements on client side.

The MSS DataServer supports the following scenarios and can be evaluated on the server side. The following table provides the possible Lock type in each scenario:

When the first GET statement is executed with EXCLUSIVE-LOCK and the subsequent GET statement is executed with any kind of locking, DataServer prepares the ROWID index key values cache of the tables participating in the JOIN query. DataServer then fetches records from the data source by using its ROWID and locks the record in data source.

When the first GET statement is executed with a lock (NO-LOCK or SHARE-LOCK) other than EXCLUSIVE-LOCK, DataServer caches the whole record in the result set and subsequent fetches retrieves the records from the cache. If the subsequent GET specify exclusive-lock, then the record is locked in the data source.

Examples:

When the join keys contain the Unknown value (?) for OpenEdge, those record matches are included in the join results. This is in contrast to the NULL value for SQL databases whereby