What is the correct way to do an upsert?

Working with Data
, ,
1

In my code i have a findOneAndUpdate with filter on _id (and two other fields) and upsert: true.
If run concurrently on MongoDB 7 I get this error (Java Driver):

'Plan executor error during findAndModify :: caused by :: E11000 duplicate key error collection: XXXXXX dup key: { _id: "XXXXXX" } found value: {}' on server 127.0.0.1:27017. The full response is {"ok": 0.0, "errmsg": "Plan executor error during findAndModify :: caused by :: E11000 duplicate key error collection: XXXXXX dup key: { _id: \"XXXXXX\" } found value: {}", "code": 11000, "codeName": "DuplicateKey", "keyPattern": {"_id": 1}, "keyValue": {"_id": "XXXXXX"}, "foundValue": {}, "$clusterTime": {"clusterTime": {"$timestamp": {"t": 1724767678, "i": 2}}, "signature": {"hash": {"$binary": {"base64": "XXXXXX", "subType": "00"}}, "keyId": XXXXXX}}, "operationTime": {"$timestamp": {"t": 1724767678, "i": 2}}}

I think this is this very old behavior: https://jira.mongodb.org/browse/SERVER-14322

What is the correct way to handle it?
The thing that comes to my mind is to catch the error on the application side and retry the update a certain number of times but this makes me write a lot of code.

Is it possible that this very common pattern has no elegant solution?

Thanks.

2

What is the form of your _id values? Are you generating those? If your code allows for two threads to simultaneously perform an upsert using the same target _id value, there’s always a chance they both result in a create attempt using that same _id value and only one succeeds.

Your two options would appear to be to either - as you say - handle the error within your application, or to ensure threads cannot use the same value of _id, perhaps by implementing a sequence using findOneAndUpdate similar to the approach described here:

3

You didn’t provide enough information to determine your issue.
Is the collection sharded, if so, what is the shard key.
What is the actual query you are using?

I expect this scenario would fail but have not tried it out.
document 1:
id: 1 (filter)
color: blue (filter)
shape: square (filter)

FindOneAndReplace with upsert:true:
id: 1
color: red
shape: circle

Would fail an “upsert” because the id is the same and would result in a duplicate id.