Best Practice for Dealing with Multiple Users Modifying the Same Record

[Scholli2000]

Hi forum,

in our existing application, we would rely on record locks to prevent a user from changing a record that is currently been changed by another user. Now I want to move from RPG native i/o to SQL, and I wonder what is the best approach to avoid conflicts when multiple users try to modify the same record at the same time.

How do you deal with the problem? Maybe someone can point me to an article or describe their practice.

Thanks,
Markus

[Vectorspace]

You can mimic that behaviour if you want - there is a commitment control option you can apply to a Select, that will lock the record just read so you can then Update it. A Commit or Rollback releases the lock (I don't know if an update releases it). Not ideal though.

An approach used at the place I used to work at (no idea if it's best practice) was to have a last changed timestamp on the record. When you read the record for your UI screen, you store the timestamp. When when you update the record to write the timestamp you:
A: always update the last changed timestamp
B: Condition the update on the timestamp being the same as when the record was read, and if SQLSTT = '02000' for not found report a "concurrent update error" to the user

That way if:
1. User A reads
2. User B reads
3. User A updates
4. User B updates

User A's update will work, user B will get a concurrent update error and be told to reload the screen and try again

[Scholli2000]

Thanks a lot, Vectorspace!

If not the best practice, at least it is a good practice, as it actually has been practiced in your company. That’s exactly what I was looking for.

Our newer tables have last changed timestamps and some of them also a last change user, so we could issue a message like "xy has changed the record in the meantime, do you want to overwrite their work?", and maybe even show the values they entered for comparison. Sounds great to me :-)

[Barbara Morris]

Hmm, that doesn't sound like a good idea to allow someone to overwrite an intervening change. Even if you put up a screen showing what they'd be overwriting, and maybe force them to "ok" every change they'd be overwriting, you'd still have to worry about the situation where yet another change got made while they were making their decision.

[Scott Klement]

It depends on the application, but if you only overwrite the fields that were actually changed, it can work pretty well.

For example, suppose there are 2 fields... delivery date & contact name.

Alan opens the record, and it's loaded on his screen without a lock.
Beth opens the record, and it's now loaded on her screen without a lock.

Alan decides to change the delivery date, The program detects that Alan changed only this field, so when it updates the record, it loads the record (with a lock), changes only the delivery date to what was on the screen, and then updates (releasing the lock.)

At the same time, Beth changes the contact name. Same deal, the program detects that she only changed this field, and only updates it. It doesn't matter that Alan updated the date in the meantime, because it's only updating the contact name because that's all that Beth changed.

Now consider the same scenario, except that Beth decides to change the delivery date -- the same field that Alan changed -- what will happen? Beth's change will overlay Alan's, it's true -- but since Beth's is slightly later, this can be considered an expected result. After all, whomever changed it last should have their value in the file. The only disadvantage being that the date that Beth saw before her changes wasn't accurate, but it's still accurate afterwards.

That is the method I typically use in stateless applications. For example, when creating a REST API that makes updates, or a web application where the front-end doesn't have a stateful connection to a program on the backend that can hold a lock on the record.

That works when a change is idempotent -- as is usually the case in a maintenance program. You wouldn't use this technique for a non-idempotent change. For example, if adding an invoice amount of a customer's total owed, this wouldn't work right because Beth's value might be adding to what was on her screen, whereas Alan might've already changed it. Since this is adding to the existing value (rather than replacing it) it is non-idempotent, and therefore problematic. For a non-idempotent change, it's better to find a way to retain a record lock, or else keep track of the "before" value when first loading the record, and read it again before updating and compare them, if there is a discrepancy, don't make the update and signal the change to the user.

[Scholli2000]

Oh, great comments.

If I show to the user what has happened, they can choose if to update anyway or cancel the operation.
Thanks to you pointing out that another change can occur while the user is contemplating to do or not to do the change, I tried a recursive solution:

// MAIN

dou chrNewValue = 'X';

  tmpStart = %Timestamp;

  exec sql
      select value
      into :chrOldValue
      from atestupd
      where id = 2;  ​

  dsply ('---------------------------------------');
  dsply ('ID2 - Existing value: ' + chrOldValue);
  dsply 'ID2 - Enter new value!' '' chrNewValue;

  if chrNewValue <> 'X';
    checkedUpdate(2:chrNewValue:tmpStart);
  endif;
      
enddo;
                                    
return;

// --------------------------------------------------
// Procedure name: checkedUpdate
// Purpose:        update with check on intermediate changes
// Returns:        *on, if record updated
// Parameter:      id        - id field in table
//                 newvalue  - new value
//                 timestamp - since record is "open"
// --------------------------------------------------
dcl-proc checkedUpdate;

  dcl-pi checkedUpdate ind;
    id        int(10)     const;
    newvalue  char(1)     const;
    chk_timestamp         timestamp;
  end-pi;

  dcl-s ask_timestamp timestamp;

  if not hasChangedSince(id:chk_timestamp);

    exec sql
       update atestupd set value = :newvalue where id=:id;
    return *on;

  else;

    ask_timestamp = %Timestamp;

    if showAndAsk(id:newvalue);
      checkedUpdate(id:newvalue:ask_timestamp);
    else;
      return *off;
    endif;

  endif;

  return *off;

end-proc;

// --------------------------------------------------
// Procedure name: showAndAsk
// Purpose:        shows change and asks if to overwrite
// Returns:        *on, if update is to be performed
// Parameter:      id        - id field in table
//                 newvalue  - new value
// --------------------------------------------------
dcl-proc showAndAsk;

  dcl-pi showAndAsk ind;
    id      int(10) const;
    newvalue char(1) const;
  end-pi;

  dcl-s chrAnswer      char(1);
  dcl-s chrValue       char(1);
  dcl-s chrCounterUser char(10);

  exec sql select value, aencuruser
           into :chrValue, :chrCounterUser
           from atestupd
           where id = :id;

  dsply ('Record updated by ' + chrCounterUser + ' to value: ' + chrValue);
  dsply ('overwrite with ' + newvalue + '? (Y/N)') '' chrAnswer;

  return chrAnswer = 'Y';

end-proc;

// --------------------------------------------------
// Procedure name: hasChangedSince
// Purpose:        checks if a record has been changed
//                 since a given timestamp
// Returns:        *on, if record has been changed
// Parameter:      id - id field of table
// Parameter:      timestamp - to check since
// --------------------------------------------------
dcl-proc hasChangedSince;
  dcl-pi hasChangedSince ind;
    id            int(10)   const;
    chk_timestamp timestamp const;
  end-pi;

  // Working Fields
  dcl-s tmpLastChanged timestamp;

  // "C-Specs"
  monitor;

    exec sql
       select aentmstmp
       into :tmpLastChanged
       from atestupd
       where id = :id;

    // has changed since chk_timestamp?
    return ( chk_timestamp < tmpLastChanged );

  on-error;
     // TODO: error handling
  endmon;

  dsply 'error'; // TODO: error handling
  return *off;

end-proc hasChangedSince;  ​

What do you think?