MongoDB Update with upsert and unique index atomicity when document is not in the working set

Question

In summary, we have ran into this weird behavior in doing concurrent updates on an existing document when the document is not part of the working set (not in resident memory).

More details:

Given a collection with a unique index and when running concurrent updates (3 threads) with upsert as true on a given existing document, 1 to 2 threads raise the following exception:

Processing failed (Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$key_1  dup key: { : 1008 }'):

According to the documentation, I would expect all of the three updates to succeed because the document I am trying to update already exists. Instead, it looks like it is trying to do an insert on few or all of the update requests and few fails due to the unique index.

Repeating the same concurrent update on the document does not raise any exceptions. Also, using find() on a document to bring it to the working set, then running the concurrent updates on that document also runs as expected. Also, using findAndModify with the same query and settings does not have the same problem.

Is this working as expected or am I missing something?

Setup:

-mongodb java driver 3.0.1

-3 node replica set running MongoDB version "2.6.3"

Query:

BasicDBObject query = new BasicDBObject();  
query.put("docId", 123L);
collection.update (query, object, true, false);

Index:

name: docId_1
unique: true
key: {"docId":1}
background: true

Updated on May 28 to include sample code to reproduce the issue. Run MongoDB locally as follow (Note that the test will write about ~4 GB of data): ./mongodb-osx-x86_64-2.6.10/bin/mongod --dbpath /tmp/mongo Run the following code, restart the database, comment out "fillUpCollection(testMongoDB.col1, value, 0, 300);", then run the code again. Depending on the machine, you may need to tweak some of the numbers to be able to see the exceptions.

package test;

import com.mongodb.BasicDBObject;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.Mongo;
import com.mongodb.MongoClient;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class TestMongoDB {
    public static final String DOC_ID = "docId";
    public static final String VALUE = "value";
    public static final String DB_NAME = "db1";
    public static final String UNIQUE = "unique";
    public static final String BACKGROUND = "background";
    private DBCollection col1;
    private DBCollection col2;

    private static DBCollection getCollection(Mongo mongo, String collectionName) {
        DBCollection col =  mongo.getDB(DB_NAME).getCollection(collectionName);
        BasicDBObject index = new BasicDBObject();
        index.append(DOC_ID, 1);
        DBObject indexOptions = new BasicDBObject();
        indexOptions.put(UNIQUE, true);
        indexOptions.put(BACKGROUND, true);
        col.createIndex(index, indexOptions);
        return col;
    }

    private static void storeDoc(String docId, DBObject doc, DBCollection dbCollection) throws IOException {
        BasicDBObject query = new BasicDBObject();
        query.put(DOC_ID, docId);
        dbCollection.update(query, doc, true, false);
        //dbCollection.findAndModify(query, null, null, false, doc, false, true);
    }

    public static void main(String[] args) throws Exception{
        final String value = new String(new char[1000000]).replace('\0', 'a');
        Mongo mongo = new MongoClient("localhost:27017");
        final TestMongoDB testMongoDB = new TestMongoDB();
        testMongoDB.col1 = getCollection(mongo, "col1");
        testMongoDB.col2 = getCollection(mongo, "col2");

        fillUpCollection(testMongoDB.col1, value, 0, 300);
        //restart Database, comment out previous line, and run again
        fillUpCollection(testMongoDB.col2, value, 0, 2000);
        updateExistingDocuments(testMongoDB, value);
    }

    private static void updateExistingDocuments(TestMongoDB testMongoDB, String value) {
        List<String> docIds = new ArrayList<String>();
        for(int i = 0; i < 10; i++) {
            docIds.add(new Random().nextInt(300) + "");
        }
        multiThreadUpdate(testMongoDB.col1, value, docIds);
    }


    private static void multiThreadUpdate(final DBCollection col, final String value, final List<String> docIds) {
        Runnable worker = new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("Started Thread");
                    for(String id : docIds) {
                        storeDoc(id, getDbObject(value, id), col);
                    }
                } catch (Exception e) {
                    System.out.println(e);
                } finally {
                    System.out.println("Completed");
                }
            }
        };

        for(int i = 0; i < 8; i++) {
            new Thread(worker).start();
        }
    }

    private static DBObject getDbObject(String value, String docId) {
        final DBObject object2 = new BasicDBObject();
        object2.put(DOC_ID, docId);
        object2.put(VALUE, value);
        return object2;
    }

    private static void fillUpCollection(DBCollection col, String value, int from, int to) throws IOException {
        for(int i = from ; i <= to; i++) {
            storeDoc(i + "", getDbObject(value, i + ""), col);
        }
    }
}

Sample Output on the second run:

Started Thread
Started Thread
Started Thread
Started Thread
Started Thread
Started Thread
Started Thread
Started Thread
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "290" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "170" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "241" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "127" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "120" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "91" }'
Completed
com.mongodb.DuplicateKeyException: Write failed with error code 11000 and error message 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db1.col1.$docId_1  dup key: { : "136" }'
Completed
Completed

Solution 1

This looks like a known issue with MongoDB, at least up to version 2.6. Their recommended fix is to have your code retry the upsert on error. https://jira.mongodb.org/browse/SERVER-14322

Solution 2

Your query is too specific, not finding the document even if it's created, e.g. not only searching for the unique field. Then the upsert tries to create it a second time (another thread) but fails as it actually exists, but wasn't found. Please see http://docs.mongodb.org/manual/reference/method/db.collection.update/#upsert-behavior for more details.

Boil down from doc: To avoid inserting the same document more than once, only use upsert: true if the query field is uniquely indexed. Use modify operators like $set, to include your query document into the upsert doc

If you feel that this isn't the case for you. Please provide us with the query and some information about your index.

Update:

If you try to run your code from cli, you'll see the following:

> db.upsert.ensureIndex({docid:1},{unique:true})
{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.upsert.update({"docid":123},{one:1,two:2},true,false)
WriteResult({
    "nMatched" : 0,
    "nUpserted" : 1,
    "nModified" : 0,
    "_id" : ObjectId("55637413ad907a45eec3a53a")
})
> db.upsert.find()
{ "_id" : ObjectId("55637413ad907a45eec3a53a"), "one" : 1, "two" : 2 }
> db.upsert.update({"docid":123},{one:1,two:2},true,false)
WriteResult({
    "nMatched" : 0,
    "nUpserted" : 0,
    "nModified" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: test.upsert.$docid_1  dup key: { : null }"
    }
})

You have the following issue:

• You want to update the document but don't find it. And your update contains no modify operators, thus your docid field won't be included in the newly created document (or better it's set to null, and null can be set only once in a unique index, too).
• Next time you try to update your document, you still don't find it, because of the last step. So MongoDB tries to insert it following the same procedure as before, and fails again. No second null allowed.

Simply change your update query to this, to modify the document/ on upsert case include your query into it: db.upsert.update({"docid":123},{$set:{one:1,two:2}},true,false)

db.upsert.update({"docid":123},{$set:{one:1,two:2}},true,false)
WriteResult({
    "nMatched" : 0,
    "nUpserted" : 1,
    "nModified" : 0,
    "_id" : ObjectId("5562164f0f63858bf27345f3")
})
> db.upsert.find()
{ "_id" : ObjectId("5562164f0f63858bf27345f3"), "docid" : 123, "one" : 1, "two" : 2 }
> db.upsert.update({"docid":123},{$set:{one:1,two:2}},true,false)
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

Comments

[Ameen]

I don't feel that this is my case since the query is very simple. I added how the query and index look like. Also, added that findAndModify operation on the same query and index does not result in the same exceptions.

[philnate]

Please see my updated answer, if you run this without having the document upfront, the newly created one, won't contain docid: db.upsert.update({"docid":123},{one:1,two:2},true,false) only the one with $set will have it.

[Ameen]

Thanks for the update. If the document inserted in the first update has the docId field, the second update should find the document, which is what we are doing.

[philnate]

Exactly, but this will only be the case, when you use the later query (the one extended with $set) call. Please try it out yourself and you'll see.

[Ameen]

When you do a $set, it is updating the existing document. Without the $set operator, it does a replace. We use replace and the updates work as expected like the following example: db.col1.update({"docid":123},{docid:123,one:1,two:2},true,fa‌​lse). Our issue is with the concurrency of these updates when the index or the document are not in the working set. When I have some time, I will work on doing a simulation that attempts to reproduce this issue.

[philnate]

Ok, from what you say this shouldn't happen, indeed. But we might miss something here. So it would be very helpful if you could boil down your issue. Atomicity of single doc updates should avoid this.

[Ameen]

Thanks. Just added code to reproduce the issue if you are interested.