Category Archives: Java Development

MBO Transactions: Beware the setWhere

Caution: setWhereOftentimes, creating, updating, or deleting an MBO is accompanied by other related MBO actions that should either succeed together or fail together within a single database transaction. 

For example, we recently added a feature to our mapping software that allows a user to select an asset on the map and enter meter readings for all of the active meters associated with that asset. We wanted to treat that collection of meter readings as a single bundle so that if, by chance, one of the input readings were to cause the database to fail to save, we didn’t want any of the other meter readings to save.

In the database world, this property is just one part of being ACID-compliant and is usually how people prefer their database to operate so that the database isn’t left in some partially incorrect or incomplete state. However, for performance reasons or for seeing steady progress in a long operation, it may be desirable at times to avoid transactions. Yet, this case should be rare. Always strive for transactions first and then choose to back away if needed.

Both IBM and Bruno provide some useful discussion on how child MBOs obtained via relationships from parent MBOs are contained in the same transaction as their parents.

So, for example:

// meters is a JSON array of JSON objects that holds meter data
for (int meterIndex = 0; meterIndex < meters.size(); ++meterIndex) {
    final JSONObject meterMap = (JSONObject) metersJson.get(i);
    final String meterName = (String) meterMap.get("meterName");
    final String meterReading = (String) meterMap.get("meterReading")

    // get asset meter
    final MboSetRemote assetMeters = mxSession.getMboSet("ASSETMETER");
    assetMeters.setWhere("assetnum = '" + assetNum + "' AND siteid = '" + siteId + "' AND metername = '" + meterName + "'");

    // update asset meter
    final MboRemote assetMeter = assetMeters.moveFirst();
    assetMeter.setValue("lastreading", meterReading);

    // update related measurements
    final MboSetRemote measurementSet = assetMeter.getMboSet("NEWMEASUREMENT");
    final MboRemote newMeasurement = measurementSet.add();
    newMeasurement.setValue("measuredate", today);

    assetMeters.save()
}

It should be noted that the above snippit is only a partial solution of how to correctly record meter data and has been gutted and rearranged for illustratation purposes. Notice that the call to assetMeters.save() is sufficent to persist both the assetMeter update as well as new additions to measurementSet. It is not necessary to call measurementSet.save() because the parent MBO tracks its children in the same transaction.

However, there is a major flaw in the code. Each meter reading is saved independently of the others; it is not transactional. It is tempting to push assetMeters.save() to the very end of the code outside the loop:

final MboSetRemote assetMeters = mxSession.getMboSet("ASSETMETER");

// meters is a JSON array of JSON objects that holds meter data
for (int meterIndex = 0; meterIndex < meters.size(); ++meterIndex) {
    ...

    // get asset meter
    assetMeters.setWhere("assetnum = '" + assetNum + "' AND siteid = '" + siteId + "' AND metername = '" + meterName + "'");

    ...
}
assetMeters.save()

Unfortunately, this fails to save meter data except for the very last meter. This leads to a crucial observation: even though assetMeters is declared above the loop, the setWhere() call resets the transaction. We must call setWhere() only once on the MBOSetRemote on which we will call save().

The corrected code looks like this:

final MboSetRemote assetMeters = mxSession.getMboSet("ASSETMETER");
final String meterList = "'meter1', 'meter2', 'meter3'";
assetMeters.setWhere("assetnum = '" + assetNum + "' AND siteid = '" + siteId + "' AND metername in (" + meterList +")";

for (MboRemote assetMeter = assetMeters.moveFirst(); assetMeter != null; assetMeter = assetMeters.moveNext()) {
    ...
}
assetMeters.save()

meterList is a String that is built up by iterating through the meters array. We also iterate through the actual MBOs instead of the meters (and reference a new map not shown to get the JSON data passed in).

The main point is that setWhere() is only called once to prevent new transactions from being saved. However, building up meterList is not ideal.

Another approach to do this, as noted by Chon, is to create assetMeters using a child relationship of some other MBO (e.g., an Asset) from another MBO set (e.g., an Asset set).  This would obviate the need to build up the meterList.  Instead of calling assetMeters.save(), we could call save() on the parent MBO set (AssetSet).

This approach would like:

final MboSetRemote assetSet = mxSession.getMboSet("ASSET");         
assetSet.setWhere("assetnum = '" + assetNum + "' AND siteid = '" + siteId + "'");
final MboRemote asset = assetSet.moveFirst();            
            
if (asset != null) {
    final MboSetRemote assetMeters = asset.getMboSet("ACTIVEASSETMETER");            
    for (MboRemote assetMeter = assetMeters.moveFirst(); assetMeter != null; assetMeter = assetMeters.moveNext()) {
        ...
    }
}
assetSet.save()