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.
Note: GET statements can be used with different locking types at statement level.
Table 1. Syntax 
GET { FIRST | NEXT | PREV | LAST | CURRENT } query [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-WAIT ]

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:

Table 2. Locking Scenarios
QUERY-TUNING Startup Parameter
NO-LOCK and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
SHARE-LOCK and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
Default lock and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
NO-LOCK, SHARE-LOCK and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
NO-LOCK, default lock (ABL), and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
SHARE-LOCK, default lock (ABL), and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock
NO-LOCK, SHARE-LOCK, default lock (ABL), and fetched with EXCLUSIVE-LOCK
SHARE-LOCK
NO-LOCK
Default Query-level lock

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.

Note: In all the scenarios mentioned in the above table, if the user specifies QUERY-TUNING(NO-JOIN-BY-SQLDB), join by client is applied and the Join is evaluated on the client side.

Examples:

  • In a scenario where the query is opened with NO-LOCK and the first GET statement uses EXCLUSIVE-LOCK, the query syntax is as below:
    OPEN QUERY cust-query FOR EACH Customer NO-LOCK, EACH order of customer NO-LOCK.
GET NEXT cust-query EXCLUSIVE-LOCK.
GET NEXT cust-query NO-LOCK.
  • In a scenario where the query is opened with NO-LOCK and the first GET statement uses NO-LOCK, the query syntax looks like this:
    OPEN QUERY cust-query FOR EACH Customer NO-LOCK, EACH order of customer NO-LOCK.
GET NEXT cust-query NO-LOCK.
GET NEXT cust-query EXCLUSIVE-LOCK.
    Note: The default lock for the above ABL statements is NO-LOCK as both participating buffer has NO-LOCK as query-level lock types. The default for participating buffer is the respective lock type specified at query-level.

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