Search code examples
updatingprogress-4glopenedge

Progress OpenEdge how to prevent someone from updating a record

I need an alternative way to prevent someone from accessing a particular piece of code.

I'll explain the scenario.

There are two programs.

In the first program an end-user creates a proforma invoice. When he/she then views the details on the invoice. The code displays the details with the main table's record in EXCLUSIVE-LOCK. This is to prevent other end-users from changing anything while the first user is busy viewing the details. So even when the proforma invoice is completed and can no longer be changed. The main table's record is still in EXCLUSIVE-LOCK. which is wrong, but it prevents other users from messing with it while the first user is still busy updating it. However, the people who work in this program leave the program in the detail view. They don't go out.

The problem is when the second program is used to dispatch the items on the proforma invoice. It uses the same main table's record. And therefore can't do anything because the first program still has it in EXCLUSIVE-LOCK.

My question is...

How can I prevent users changing data in the first program as if the main table's record was in EXCLUSIVE-LOCK, but without actually having it in exclusive-lock? Over multiple sessions...

Solution

  • This might be better a comment, but I don't have enough reputation points to make comments. Sorry.

    Some notes:

    1. Optimistic locking -- if it is viable for your situation -- is almost certainly the best solution.

    2. If you are going to add an isLocked field to the table, you will probably want several other fields:

      • Date/Time the record was locked -- or else an expiration timestamp
      • LockHolder -- so you know whether or not you've got it.

    3. The expiration can be automatic (as with a cron sweep), or can be ignored unless someone else wants the record. The program which sets the lock must also be smart enough to check to see if it still holds it. It gets complicated.

    4. There are times when it is not convenient to make schema changes to a table, or there are too many tables that need changes. In those cases, you can add these fields to a separate LockIt table. One table can handle these locks for all your other tables.

    Aside:
    We also use our LockIt table for another purpose: to make sure that only one copy of a given program can run at a time. (Usually this is for cron jobs or a batch daemon.) The program exclusive-locks a particular record in the LockIt table (but DOES NOT start a transaction!), and it holds that lock as long as the program is running.