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,