Progress
DataServer
for ODBC Guide


Locking Examples

The following example illustrates how the end of a transaction affects Progress and ODBC data-source locks differently:

DO TRANSACTION:
		FIND customer WHERE cust-num = 10.
		UPDATE customer.
END.

/* Do a second transaction with the same customer record. */ 

This example illustrates how Progress and ODBC data-source shared locks differ in scope and duration:

FIND customer WHERE cust-num = 10 SHARE-LOCK NO-WAIT NO-ERROR.
IF AVAILABLE customer THEN DO:
  DISPLAY customer.
  PROMPT-FOR customer.
  
  tx:
  DO TRANSACTION ON ERROR UNDO tx, RETRY tx:
    FIND customer WHERE cust-num = 10 EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
    IF LOCKED customer THEN DO:
      Message "customer locked - retrying".
      UNDO tx, RETRY tx.
    END.
    ELSE DO:
      ASSIGN customer.
      LEAVE.
    END.
  END.
END. 

In this example, the first record is only share-locked within the ODBC data source if the isolation level setting requires it. (Recall that a share lock specified in a Progress statement is ignored by the DataServer.) As a result, the first record might be updated before the second FIND statement executes, in which case the record that the second FIND statement fetches might be different from the record fetched by the first FIND statement. This procedure might cause update information to be lost because the procedure applies updates based on the first record, and these updates will overwrite the values in the second record.

Using the DataServer to access an ODBC database ensures that locks are upgraded in the data source in the same way as in a Progress database. For example, the following procedure causes the same behavior whether you access a Progress database or an ODBC data source:

FIND customer WHERE cust-num = 10.
DISPLAY customer.
PROMPT-FOR customer.

DO TRANSACTION:
		ASSIGN customer.
END. 

The record is share-locked when it is fetched. The DataServer upgrades the shared lock to an exclusive lock inside the transaction by locking the record, reading it, and checking whether the record has changed since it was first fetched. If it has changed, the lock upgrade fails and you receive an error message.

If your data source uses record-level locking, you might have to wait to access a record in the following circumstances:

When this happens, Progress uses a time-out loop, checking periodically to see whether the record is available. You can choose Cancel at any time to abort the request.

The ODBC data source notifies the DataServer if it cannot perform a requested operation within a given period of time. Under unusual system or network loads, the DataServer might receive notification that a request has not been completed. In this case, it returns a message that the record that the request was accessing is locked, even though no other user has a lock on the record.

One type of locking behavior that you might encounter is a deadlock, or “deadly embrace.” A deadlock occurs when two users want to access each other’s table, page, or record, and either the table, page, or record that they want has an exclusive lock on it or they need to put an exclusive lock on it. Neither table, page, or record will give up its lock until the other is available. When an ODBC data source detects this situation:

NOTE: On DB2 for AIX, the DBA must set the variable LOCKTIMEOUT to some number of seconds in the database configuration to get beyond a deadlock condition. The default value (-1) causes a user to wait indefinitely for a lock to be released when attempting to update a locked record.

For details on how Progress locks work, see the Progress Programming Handbook. See ODBC and data-source documentation for more information about ODBC and data-source locks.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095