Progress
DataServer for
Microsoft SQL Server
Guide


Locking Examples

The following example illustrates how the end of a transaction affects Progress and MSS 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 MSS data source share 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 SHARE–LOCK syntax is ignored because the share lock behavior is determined by SQL Server™ and the isolation level. If isolation level read uncommitted or read committed is selected, the first record might be updated by another user before the second FIND statement executes. In this 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 a SQL Server™ 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 MSS 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 share 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.

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 MSS 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 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 the table, page, or record that they want has 1) an exclusive lock on it, or, 2) one of the users needs to put an exclusive lock on it. Neither table, page, or record will give up its lock until the other table, page, or record is available. When an MSS data source detects this situation:

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


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