Change the default lock in Progress instead of Share-lock

Question

How can we change the default lock in Progress instead of Share-lock?

Answer 1

You can change it on each individual query (FIND, FOR EACH etc) by adding the lock status to the query using the [NO|EXCLUSIVE|SHARE]-LOCK modifier. If you leave this off, you get the default SHARE-LOCK.

You can also compile your code in a session started with the -NL parameter, which changes the default for that r-code into NO-LOCK.

Answer 2

If I understand correctly, Progress 4GL NO-LOCK is similar to the TSQL command WITH(NOLOCK). The idea is that you want as many of the records as quickly as possible, and that you want to avoid the resource contention that may come with SHARE-LOCK for records you are not going to edit anyway. Now, in TSQL you are warned that edits that are occurring as you do a TSQL query may be missed; I'm not really sure if that applies to Progress 4GL/ABL. My familiarity is with Progress 9 and OpenEdge 10 / 11. Progress Experts, please correct me if I'm too far off base here. I'm still a bit new to this.

In a FOR-EACH construct, do it before the BREAK or BY keywords (the by keyword is similar to order in SQL), but after the where clause:

FOR EACH TableName
  WHERE TableName.x > 10
    AND TableName.y
  NO-LOCK BREAK BY TableName.x:
  /* Do Code */
END.

IF using a FOR-EACH with an additional EACH / FIRST / LAST query (Assuming table Subscription / sub-table MailSubscription), you can set the level per subquery. In this example, I need to trawl through Subscription records, looking for related MailSubscription records, and editing the final, non-enddated one keeping all others out while working on it, but leaving the main sub record open for others to edit.

  FOR EACH Subscription
    WHERE ProductID = 'DB'
      AND Subscriber
      AND GetsPaper
   NO-LOCK,
   LAST MailSubscription OF Subscription 
     WHERE MailSubscription.EndDate = ?
   EXCLUSIVE-LOCK
   BREAK BY Subscription.SubscriptionID:
     /* Fix broken MailSubscription records */
  END.

In a FIND-FIRST / FIND-LAST construct, do it after the where, and before the NO-ERROR (assuming you are using the IF-AVAILABLE construct). Remember, if you need to grab two different records from the same table, to define a buffer for at least one of them.

FIND FIRST TableName WHERE TableName.x > 10 AND TableName.y NO-LOCK NO-ERROR.

IF AVAILABLE TableName THEN DO:
  /* Do Code */
END.

You may also replace NO-LOCK with EXCLUSIVE-LOCK interchangeably. I should probably throw a quote up here about great power / great responsibility, but if you have access to the Progress editor, you've probably already gotten it from your admin. Be safe.

I recommend Online backups if you have a new enough Progress version. Online in the sense of the DB isn't taken down for the backup online, not a cloud backup or anything.