0

I have a collection with 2 compound unique index, uuid and id. I want to update a document if the collection have a document with unique value of uuid and id (composite unique) and I found in the documentation that updateOne with upsert=true can do this. So, I use:

db.collection("messages").updateOne({uuid:this.uuid, id:new_message.id}, {$set: {uuid: this.uuid, ...new_message}}, {upsert:true})

and this always throw an error saying that there's a document with duplicate value of uuid=xxx and id=yyy. I looked up and found a post stating there's data race happening on update and insert on mongodb upsert operation so this will always happen. Is there another way to do this? How do I properly and efficiently upsert a collection with 1 million documents?

EDIT:

I gave the wrong code for this question. The code should be:

db.collection("messages").updateOne({uuid:this.uuid, key:{id:new_message.key.id}}, {$set: {uuid: this.uuid, ...new_message}}, {upsert:true})
swdmnd
  • 113
  • 12
  • `"I looked up and found a post stating there's data race happening on update and insert on mongodb upsert operation so this will always happen."` Where did you read this? – Minsky Feb 03 '22 at 12:53
  • @Minsky here and somewhere in SO, but I remember they cite the same source, though it's a question from 6 years ago https://stackoverflow.com/questions/29305405/mongodb-impossible-e11000-duplicate-key-error-dup-key-when-upserting – swdmnd Feb 03 '22 at 13:03
  • @Minsky https://stackoverflow.com/questions/43946523/why-would-this-upsert-fail-with-a-duplicate-id-exception – swdmnd Feb 03 '22 at 13:05
  • Thanks. I believe if the query is done with an unique index no problem should occur. Are those uuid, id not indexed? (may be good do to it). – Minsky Feb 03 '22 at 13:07
  • @Minsky They're uniquely indexed using `db.messages.createIndex({uuid:1,id:1},{unique:true})` from mongodb compass – swdmnd Feb 03 '22 at 13:10
  • Oh, I understand the problem now. Apologizes. I don't know very well what a 'thread' is. But I imagine that MongoDB splits the collection array and runs the the same query operation in each slice (asynchronously). If you have several giving no update, then they may insert same document (may you got this earlier). I'll inspect for solutions. I can't believe this happens though. – Minsky Feb 03 '22 at 13:18
  • You could try findAndModify, at least it was added later on, maybe the prolem was fixed. – Minsky Feb 03 '22 at 13:27
  • @Minsky yes, threads are used to do tasks asynchronously. Thanks, I'll try findAndModify and do some update to this later – swdmnd Feb 03 '22 at 13:55

2 Answers2

1

Since you have multi-threading, this is a common problem. All the supported operations in mongo will run into this issue as it is based on your architecture.

You can catch the exception and retry the operation. In this case, one of the threads would be succeeded. Other one will pass through exception handling. This is a feasible workaround.

When do you except both threads updating the same document at the same time? This is a serious design problem. This will alter the desired document state.

Gibbs
  • 21,904
  • 13
  • 74
  • 138
  • I'm surprised mongodb didn't handle the conflict internally. Upsert should be either update or insert, and of course one would expect only one of the two operations would succeed. Do you have any other suggestions besides catching the exception? Is catching the exception enough to ensure the other operation successfully executed? – swdmnd Feb 03 '22 at 14:36
  • Yes, that's enough. I don't think it's a problem at mongo side. – Gibbs Feb 03 '22 at 14:37
0

So, after trying out things I found out that I should use dot notation in the query, I changed it to:

db.collection("messages").updateOne({uuid:this.uuid, "key.id":new_message.key.id}}, {$set: {uuid: this.uuid, ...new_message}}, {upsert:true})

and now it works.

swdmnd
  • 113
  • 12