Write conflict in findAndModify when sort is used

About the Community Welcome
, ,
1

This is the query.

db.coll1.explain('executionStats').findAndModify({query: {status: 0, key1: 'VALUE1', rand: {$lte: 0.34234324234234234 }}, update: {$inc: {status: 1}}, fields: {key2: 1}, sort: {rand: 1}, new: true})

This is the index.

{
    v: 2,
    key: { key1: 1, rand: 1, key2: 1 },
    name: 'key1_1_rand_1_key2_1',
    partialFilterExpression: { status: 0 }
  },

Following is the error I am getting in mongo log. How to resolve this conflict. I am using sort and added rand field so that write conflict is minimised when multiple thread is trying to pick one document.

{
  "t": {
    "$date": "2023-04-04T13:48:31.818+05:30"
  },
  "s": "W",
  "c": "COMMAND",
  "id": 23802,
  "ctx": "conn239",
  "msg": "Plan executor error during findAndModify",
  "attr": {
    "error": {
      "code": 112,
      "codeName": "WriteConflict",
      "errmsg": "WriteConflict error: this operation conflicted with another operation. Please retry your operation or multi-document transaction."
    },
    "stats": {
      "stage": "PROJECTION_DEFAULT",
      "nReturned": 0,
      "executionTimeMillisEstimate": 0,
      "works": 2,
      "advanced": 0,
      "needTime": 0,
      "needYield": 1,
      "saveState": 1,
      "restoreState": 1,
      "failed": true,
      "isEOF": 1,
      "transformBy": {},
      "inputStage": {
        "stage": "UPDATE",
        "nReturned": 0,
        "executionTimeMillisEstimate": 0,
        "works": 2,
        "advanced": 0,
        "needTime": 0,
        "needYield": 1,
        "saveState": 1,
        "restoreState": 1,
        "failed": true,
        "isEOF": 1,
        "nMatched": 0,
        "nWouldModify": 0,
        "nWouldUpsert": 0,
        "inputStage": {
          "stage": "CACHED_PLAN",
          "nReturned": 1,
          "executionTimeMillisEstimate": 0,
          "works": 1,
          "advanced": 1,
          "needTime": 0,
          "needYield": 0,
          "saveState": 2,
          "restoreState": 1,
          "isEOF": 1,
          "inputStage": {
            "stage": "LIMIT",
            "nReturned": 1,
            "executionTimeMillisEstimate": 0,
            "works": 1,
            "advanced": 1,
            "needTime": 0,
            "needYield": 0,
            "saveState": 2,
            "restoreState": 1,
            "isEOF": 1,
            "limitAmount": 1,
            "inputStage": {
              "stage": "FETCH",
              "filter": {
                "status": {
                  "$eq": 0
                }
              },
              "nReturned": 1,
              "executionTimeMillisEstimate": 0,
              "works": 1,
              "advanced": 1,
              "needTime": 0,
              "needYield": 0,
              "saveState": 2,
              "restoreState": 1,
              "isEOF": 0,
              "docsExamined": 1,
              "alreadyHasObj": 0,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 1,
                "executionTimeMillisEstimate": 0,
                "works": 1,
                "advanced": 1,
                "needTime": 0,
                "needYield": 0,
                "saveState": 2,
                "restoreState": 1,
                "isEOF": 0,
                "keyPattern": {
                  "key1": 1,
                  "rand": 1,
                  "key2": 1
                },
                "indexName": "key1_1_rand_1_key2_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "key1": [],
                  "rand": [],
                  "key2": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": true,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "key1": [
                    "[\"VALUE1\", \"VALUE1\"]"
                  ],
                  "rand": [
                    "[-inf.0, 0.1602877584416872]"
                  ],
                  "key2": [
                    "[MinKey, MaxKey]"
                  ]
                },
                "keysExamined": 1,
                "seeks": 1,
                "dupsTested": 0,
                "dupsDropped": 0
              }
            }
          }
        }
      }
    },
    "cmd": {
      "findAndModify": "coll1",
      "query": {
        "status": 0,
        "key1": "VALUE1",
        "rand": {
          "$lte": 0.16028775844168722
        }
      },
      "fields": {
        "key2": 1
      },
      "sort": {
        "rand": 1
      },
      "remove": false,
      "update": {
        "$inc": {
          "status": 1
        }
      },
      "upsert": false,
      "new": true
    }
  }
}
2

Rand isn’t working how you want it to.

Try this:

session = db.getMongo().startSession();
session.startTransaction();
try {
    const coll1 = session.getDatabase("dbname").coll1;
    const doc = coll1.findOneAndUpdate(
        { status: 0, key1: 'VALUE1', rand: { $lte: 0.34234324234234234 } },
        { $inc: { status: 1 } },
        { returnNewDocument: true }
    );
    // Do some work with the updated document
    session.commitTransaction();
} catch (error) {
    session.abortTransaction();
    throw error;
} finally {
    session.endSession();
}

I put in transactions to isolate everything, and then I put in findOne and new document parameters when wrapped in transactions will fight the conflict you’re having, and hopefully prevent it.

I hope this helps.