Solving “Record has been updated by another user. Refetch and try again.” Problems

Originally posted at Interloc Solutions' Blog.

We’ve all seen the dreaded “Record has been updated by another user. Refetch and try again.” It can happen when the record has been updated by another user, but it can also happen when the record is updated by the same user more than once.

In memory, an MboSet owns zero or more Mbos. Each of those Mbos can have zero or more MboSets which in turn own zero or more Mbos. Naturally, if your MboSets contain zero Mbos you won’t have problems with records updated by another user. Problems will arise if two different Mbos reference the same database record and both Mbos attempt to update data. In memory, these Mbos will be represented as separate Java objects and will be owned by different MboSets. If only one Mbo is updated, it won’t be a problem. If both Mbos are updated, the first will update the database record and change the ROWSTAMP value. The second will attempt to update the record but will fail because the ROWSTAMP doesn’t match. This will trigger an MXRowUpdateException.

Let’s look at an example. In this diagram, ellipses represent Mbo objects in memory. Rectangular boxes represent database tables and their records. Solid lines represent an in-memory association through the named relationship. Dashed lines point to the database record.

In this example, a WORKORDER Mbo loads work order wo1. Following the woactivity relationship, WOACTIVITY wo1.2 is loaded into an Mbo. From here, the parent relationship is followed to WORKORDER wo1. The two WORKORDER objects reference the same database record, but they are represented in memory by two separate Mbo objects because they were loaded through two different relationships.

If either the first WORKORDER Mbo or the second WORKORDER Mbo is updated, it will save properly. If both Mbos are updated, the save will fail with an MXRowUpdateException. This occurs because the first update is applied to the database and updates the ROWSTAMP column. The second update is then applied, but the ROWSTAMP column has changed so it fails with an MXRowUpdateException.

To find where a single database record is updated by multiple Mbos, I have developed a MboDumper class. Starting from a single Mbo, it will travel down all instantiated relationships and display the relationship name and all Mbos contained there-in. If it detects the same Mbo in more than one place, it will highlight it. It’s then a matter of finding where in the code the Mbos are modified.

Code for the MboDumper is available on GitHub at https://github.com/ThatMaximoGuy/MboDumper.

To use the MboDumper, place a try-catch block around the call to save. Exactly where will depend on whether the save takes place in a DataBean or in a Cron Task or something else. The code for the above example could look like:

MXServer mxs = MXServer.getMXServer();
UserInfo ui = mxs.getSystemUserInfo();
MboSetRemote workorders = mxs.getMboSet("WORKORDER", ui);
workorders.setWhere("wonum = '10190548'");
MboRemote workorder = workorders.getMbo(0);
workorder.setValue("DESCRIPTION", "Example");

MboSetRemote woactivities = workorder.getMboSet("WOACTIVITY");
MboRemote woactivity = woactivities.getMbo(0);

MboSetRemote parents = woactivity.getMboSet("PARENT");
MboRemote parent = parents.getMbo(0);
parent.setValue("DESCRIPTION", "Example 2");
try {
   workorders.save();
} catch (MXRowUpdateException e) {
   MboDumper.dump(FixedLoggers.MAXIMOLOGGER, mbo);
   throw e;
}

Data is output to the given logger at the INFO level.

Given the example above, MboDumper would generate output like this:

*** MBO Data Dump Start
Mbo: WORKORDER [ToBeSaved ToBeUpdated] <=== DUPLICATE MBO
Key SITEID: EAGLENA
Key WONUM: 10190548
Modified DESCRIPTION: Example
Modified CHANGEBY: MAXADMIN
Modified CHANGEDATE: 2/27/18 3:03 PM
Relationship: WOACTIVITY parent= '10190548'  and siteid= 'AMTRKENG' 
Mbo: WOACTIVITY [ToBeSaved ToBeUpdated] 
Key SITEID: EAGLENA
Key WONUM: 10190549
Relationship: $SECGROUPS userid = 'MAXADMIN' and groupname in ('MAXADMIN','SUPERUSER')
Mbo: GROUPUSER [] 
Key USERID: MAXADMIN
Key GROUPNAME: MAXADMIN
Relationship: __LONGDESCRIPTIONMLFALSEWORKORDER ldownertable = 'WORKORDER' and ldkey =  196399 
Relationship: PARENT wonum= '10190548'  and siteid= 'AMTRKENG' 
Mbo: WORKORDER [ToBeSaved ToBeUpdated ] <=== DUPLICATE MBO
Key SITEID: EAGLENA
Key WONUM: 10190548
Modified DESCRIPTION: Example 2
Modified CHANGEBY: MAXADMIN
Modified CHANGEDATE: 2/27/18 3:03 PM
Relationship: CLASSSTRUCTURE classstructureid =  '' 
Relationship: __LONGDESCRIPTIONMLFALSEWORKORDER ldownertable = 'WORKORDER' and ldkey =  196398 
Relationship: CLASSSTRUCTURE classstructureid =  '' 
Relationship: __LONGDESCRIPTIONMLFALSEWORKORDER ldownertable = 'WORKORDER' and ldkey =  196398 
*** MBO Data Dump Done

The WORKORDER updated in two places is marked with “<== DUPLICATE MBO.“

Given the Mbo and the relationship name, it’s usually enough to find where in the code these multiple updates occur.