Db index for findAndModify

This is the schema, query and db index used. Is the db index correct? Sometime it is fast, taking 20ms but sometime it is taking about 200ms. This duration is taken from db slow query log.

What I observed is, this slowness is due to write conflicts. I added one rand field to minimize it. It lies between zero and one. But it is sometime causes write conflicts also. I think in production it will cause more write conflicts when collection has 20 concurrent threads picking one doc and updating it. Collection will have data in billions.

db.coll.explain('executionStats').findAndModify({query: {a: 'a', b: 'b', c: 'active', rand: { $lte: 0.444 }}, update: {$set: { c: 'done' }}, fields: { d: 1 }, new: true })

Enterprise mydb> db.coll.findOne()
  _id: ObjectId("64284cfd94ce0a6a77acbcdd"),
  d: '8afa3c50-d496-4c34-b686-33f4fc7c96bf',
  c: 'active',
  b: 'abc',
  a: 'def',
  rand: 0.7887215406852792

Enterprise mydb> db.coll.getIndexes()
  { v: 2, key: { _id: 1 }, name: '_id_' },
    v: 2,
    key: { a: 1, b: 1, c: 1, rand: 1 },
    name: 'a_1_b_1_c_1_rand_1',
    partialFilterExpression: { c: 'active' }

When I used the sort in findAndModify, it is giving sometime this error message.

{"t":{"$date":"2023-04-02T09:01:46.723+05:30"},"s":"W",  "c":"COMMAND",  "id":23802,   "ctx":"conn536","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":"LIMIT","nReturned":1,"executionTimeMillisEstimate":0,"works":1,"advanced":1,"needTime":0,"needYield":0,"saveState":2,"restoreState":1,"isEOF":1,"limitAmount":1,"inputStage":{"stage":"FETCH","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,

You want to get in a habit of enhancing the “rand” with sort, etc. because you got a lot of concurrent activity going on with your original index. I didn’t read your error admittedly, I’m just going by your index alone.

query: {a: ‘a’, b: ‘b’, c: ‘active’, rand: { $lte: 0.444 }},
sort: { _id: 1 },
update: {$set: { c: ‘done’ }},
fields: { d: 1 },
new: true

Rand helps with preventing writing conflicts, but to expand on why adding sort helps, is you’re essentially forcing MDB to create an order of operation which will help keep things from going into conflict. You can also (I’d highly recommend) shard your cluster so the operations are split between shards, which in combination with my above index for you should cut off a lot of write conflicts you’re experiencing.