When you read records from a database table, the AVM applies a level of locking to the record that you can control, so that you can prevent conflicts where multiple users of the same data are trying to read or modify the same records at the same time. This locking does not apply to temp-tables since they are strictly local to a single ABL session and never shared between sessions. There are three locking levels:
  • A NO-LOCK is a read-only option that might read incomplete transactional data.
  • A SHARE-LOCK is held until the end of the transaction or the record release, whichever is later.
  • An EXCLUSIVE-LOCK is held until the end of the transaction. It is then converted to a SHARE-LOCK if the record scope is larger than the transaction and the record is still active in any buffer. It is best to explicitly release the record after the update is complete.

When you read records using a FIND statement, a FOR EACH block, or the GET statement on a query, by default the record is read with a SHARE-LOCK. Another user can also read the same record using another SHARE-LOCK.

You can read records using a different lock level. If you intend to change a record, you can use the EXCLUSIVE-LOCK keyword. This marks the record as being reserved for your exclusive use. If any other user has a SHARE-LOCK on the record, an attempt to read it with an EXCLUSIVE-LOCK fails. Thus, a SHARE-LOCK assures you that while others can read the same record you have read, they cannot change it. You can also read a record using NO-LOCK.

When a transaction is undone, locks acquired within the transaction are released or they are changed to SHARE-LOCK if it locked the records prior to the transaction.

Optimistic locking

In a traditional host-based or client/server application, you can enforce what is referred to as a pessimistic locking strategy. This means that your application always obtains an EXCLUSIVE-LOCK when it first reads any record that might be updated, to make sure that no other user tries to update the same record.

In a distributed application, this technique simply does not work. If you read and pass records to a client session, your server-side session cannot easily hold locks on the records while the client is using them. When the server-side procedure ends and returns the temp-table of records to the client, the server-side record buffers are out of scope and the locks released. In addition, you would not want to maintain record locks for this extended duration, as it would lead to likely record contention.

The best way to make sure you get the locking you want is to be explicit about it. Follow these two guidelines for using locks:
  • Always start a transaction before reading records, even with NO-LOCK, if you are going to update it inside the transaction.
  • Release records explicitly when you are done updating them with the RELEASE statement

The following example shows first retrieving a record from the Customer table using NO-LOCK. The record is later retrieved using EXCLUSIVE-LOCK.

DEFINE VARIABLE custrid AS ROWID NO-UNDO.

FIND FIRST Customer NO-LOCK WHERE Customer.Balance > 1000.
/* Get the rowid and save it, so it can be refetched. */
custrid = ROWID(Customer).  

IF balance > 0 THEN DO TRANSACTION:
  FIND Customer WHERE ROWID(Customer) = custrid EXCLUSIVE-LOCK.
  /* Update the Comments field in the Customer record */
  Customer.Comments = "Balance remaining".   
  DISPLAY Customer.Name Customer.Balance Customer.Comments FORMAT "X(20)".
  RELEASE Customer.
END.

See also

Handling Data and Locking Records