Behavior of unique constraint while index is being built

Working with Data
,
1

I have a User collection with a uid field that needs to be unique, for which a unique constraint has been added.
For creating a new record, I call insertOne() with manually entered data, and as expected, the query fails if another user with same uid exists.
But I am wondering what happens in a scenario where User1 creates a document with “some_unique_uid” and within moments (before indexing is rebuilt), User2 also creates a document with “some_unique_uid”. Will the collection end up with 2 documents having “some_unique_uid”?

2

No it won’t. One of the 2 will fail.

3

The long standing advice for adding indexes with constraints to populated collections is to stop writes to that collection until the index build is complete.

1 Like
4

Documents that violate the index constraints can exist during the index build. If any documents violate the index constraints at the end of the build, the mongod terminates the build and throws an error.

Thanks. I read this example, but my lack of experience with databases is leaving me confused.
From a client perspective, does EACH insert/update operation wait/block until the index is rebuilt (successfully or not)? I thought index rebuilding is a time taking process happening in the background, and the client driver is returned an optimistic response, not to leave them “hanging” until the index is rebuilt. Sorry if I sound too naive!

5

@steevej Is there a way to test this? There seems to be a difference between your and @chris views.

6

Not really. It is complementary because we both assume different things. I assumed that the index was existing, so that you had no duplicate. Chris assumed that the index was not existing and was being created, and as such the collection could currently have duplicates. It is 2 different situations.

  1. The index exits. Any write that would create a duplicate will fail rapidly and the index will not be updated.
  2. The index does not exists. The creation (build) of the index will fail if duplicate existed before or come into existence during the build.

That is why

7

@steevej Got it. The example I had cited in my original query was explicit about writes when index does not exist or being built.

I don’t know how to implement @chris advice.

  1. How does a client know index is being built?
  2. Once the client knows index is being built, it cannot wait for the build to be complete. Users need to know if their insert operation went through or not.
  3. I believe MongoDB itself does not follow this approach for creating unique ObjectId in each document. If it waited for index to be rebuilt before each document creation, the system would not have scaled the way we know it does.
8

Not explicit enough for me. I took

For me has been added meant that the index has been rebuilt with the constraint. Specially since yo wrote

If it failed because the index with the constraint existed. And when I read before index is rebuilt, I read it wrong as the index being updated to add User1 some_unique_uid in it.

Must be my French mother tongue causing me to misinterpret. 8-( Sorry for the mistake and the added confusion it caused.

I am confused again by

The index on _id is never rebuilt between 2 documents, it is updated.

9

It doesn’t. You need to shut down the client(s) or create/update access control to readonly or otherwise prevent the writes.

10

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.