MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Incorrect total price on "order" document when its order items are being updated concurrently

Hi everyone,

I have 2 collections: order and orderitems where an order consists of order item(s).

The order has field price which should be the sum of the price of the order items only if the status is “new”.

For example, order ABC has 3 order items :

  1. Price: 5, status: “new”
  2. Price: 10, status: “paid”
  3. Price: 15, status: “new”

This order should have the price = 20.

What happens is sometimes the order price does not calculate correctly when the order items’ status is updated concurrently from “new” to “paid” or vice versa.

For example, when order item 1 and 3 are updated to “paid”, the order price is still 20 instead of 0.

My update order item API (Node JS) looks like this:

    const orderitem = await services
      .query("orderitem")
      .findOne({ _id: orderitemId });
    const currStatus = orderitem.status;
    const newStatus = request.status ?? currStatus;
    const currPrice = orderitem.price;
    const newPrice = request.price ?? currPrice;
    const statusNew = "new";
    let inc = 0.0;

    if (newStatus === statusNew && currStatus === statusNew) {
      inc = newPrice - currPrice;
    } else if (newStatus === statusNew && currStatus !== statusNew) {
      inc = newPrice;
    } else if (newStatus !== statusNew && currStatus === statusNew) {
      inc = currPrice * -1;
    }

    const session = services.common.startDbSession();

    try {
      await session.withTransaction(async () => {
        if (inc !== 0.0) {
          await services.order.updateOutstandingBalance(
            orderitem.order._id,
            inc,
            session
          );
        }

        await services
          .query("orderitem")
          .model.updateOne(params, request)
          .session(session);
      });
    } finally {
      await session.endSession();
    }

And here is the updateOutstandingBalance function:

    updateOutstandingBalance: async (_id, inc = 0.0, session = null) => {
      await services
        .query("order")
        .model.updateOne(
          { _id: _id },
          {
            $inc: { price: inc },
            updatedAt: new Date(),
            myLock: { appName: "myApp", pseudoRandom: new ObjectID() },
          }
        )
        .session(session);
    }

The transaction is using write concern " majority" and read concern “local”.

Any help is really appreciated, thank you.

I’m curious - why are order line items in a separate collection? It’s kind of a textbook example of schema that’s likely going to be better with array of line items in order object.

As far as transaction, I’m not sure I follow your explanation of what the business logic should be but I find it highly suspect that the query against orderitems originally happens outside of transaction. It means if there are two attempts to write that conflict the second one will retry the transaction - but not the read.

Hi Asya,

In our case, order items can be dynamically added and updated individually, customers can also pay for only specific order items. It’s a little bit different from the usual e-commerce where order items are created together with the order upon checkout. I hope this answers you.

You can update individual items within an array - and update the total, etc atomically in a single update. It’s part of normal e-commerce. Different items ship at different times. Some item might get returned. It’s still best done as an array in the order document. Saves space and indexes too.

Thanks for the prompt reply.

This discrepancy only intermittently happens when there is batch status update on multiple order items of a particular order.

For example, order ABC has 3 order items:

  1. Status is updated from “new” to “paid”
  2. Status is also updated from “new” to “paid”
  3. Status is also updated from “new” to “paid”

The read outside of the transaction is only reading from the orderitem, not from the order. Then based on its status and price change, it does $inc (if not 0) to the order’s price and update the orderitem itself. So CMIIW, the calculation doesn’t rely on the order price.

Do you think the orderitem read should also happen in the transaction?

Thanks for the suggestion, but it’s currently not possible to change the data structure because it impacts all the APIs and UI that the customers face.

That aside, what could be wrong from my implementation? Seems like the update sometimes just doesn’t increment correctly. Is there anything wrong on the transaction options, write concern “majority” and read concern “local”?

Using write concern “majority” and read concern “snapshot” also did not help.