How to handle 2 users place order same products at same time in MongoDB?

Question

I'm a beginner and don't have much experience with this case. I hope everyone can help me or provide me with a solution. Suppose, two users order the same product at the same time and there is only one product available. To handle the order, I need to do the following:

1. Use the FindOneAndUpdate method to decrease the in-stock quantity by one.
2. Create an order for the user.

It's a simple process that I want to know first. If step-2 fails, I need to roll back the operation in step 1 by increasing the in-stock quantity by one. I've created a transaction for these two operations, so if either step fails, I will abort the transaction.

Everything works fine if only one user makes an order at a specific time. However, if two or more users make an order at the same time, only the first request will be handled, and the rest will be rejected and receive an error response. In this case, I want every request to be executed sequentially.

const placeOrder = async (req: NextApiRequest, res: NextApiResponse<Data>) => {
  const session = await startSession();
  session.startTransaction();
  try {
    if (req.headers.isauth === '0') {
      return handleError(req, res, { code: 401, message: 'unAuthorized' });
    }
    const { address } = req.body as Omit<Address, 'defaultAdd'> & {
      defaultAdd?: boolean;
    };

    const user = await User.findOne({ _id: req.headers._id }, {}, { session });
    if (user) {
      // decrease product quantity
      const bulkOps = user.cart.map((item: Cart) => ({
        updateOne: {
          filter: {
            _id: item.id,
            inStock: { $gte: item.quantity },
          },
          update: { $inc: { inStock: -item.quantity, sold: +item.quantity } },
        },
      }));
      const [productResponse] = await Promise.all([
        Product.bulkWrite(bulkOps, { session }),
        user.update({ cart: [] }, { session }),
        Order.create(
          [
            {
              userId: user._id.toString(),
              shippingFee: 15000, // fixed price
              address,
              products: user.cart,
            },
          ],
          { session },
        ),
      ]);
      if (productResponse.result.nModified < bulkOps.length)
        throw new Error('Out of stock');

      await session.commitTransaction();
      return res.status(200).send({ message: 'ok' });
    }
    return handleError(req, res, { code: 404, message: 'user not found' });
  } catch (err) {
    await session.abortTransaction();
    return handleError(req, res, {});
  } finally {
    session.endSession();
  }
};

Answer 1

Mongo updates are atomic, means it has own queue for updates (locks) and guarantees no simultaneous updates happen to the same document.

The filter in step 1 should include a condition "in-stock quantity > 0", so even if you have multiple concurrent sales of the same (last) item, only the first update will return number of affected documents = 1, and all other updates will return 0.

Check this value on application level and proceed to step 2 only when the update was successful. All other requests will need to way for re-stocking. The best you can do for them is to send your regrets to the clientside and invite to the waiting list.

UPDATE

Since the OP uses transaction, it should solve the problem. The documents updated within the transaction remain exclusively locked on WiredTiger level until the transaction is committed or rejected.

All concurrent transaction will wait to lock any of these documents up to maxTransactionLockRequestTimeoutMillis configuration option, 5 millis by default.

If second transaction fails to acquire the lock, the driver throws a WriteConflict exception. Details are documented at https://www.mongodb.com/docs/manual/core/transactions-production-consideration/#lock-request-timeout

I found some people who have the same problem as me, but they haven't received any good solutions yet. (https://www.mongodb.com/community/forums/t/recommended-way-to-handle-write-conflicts-inside-transactions/104491/5)

The solution is in the last post:

If the transaction is required you should leave it in. Then if their are write-conflicts you will have to retry.

given by Mongodb employee. To elaborate - you are expected to use try-catch on application level to implement custom retries if default behaviour doesn't meet your requirements.