Sequences in MongoDB

In a previous post about No-gap sequence in PostgreSQL and YugabyteDB, I mentioned that sequences in SQL databases are not transactional, which can lead to gaps. MongoDB does not require a special sequence object. A collection can be used thanks to incremental update ($inc) and returning the updated value in a single atomic operation with findOneAndUpdate().

Here is an example. I create a "sequence" collection to hold sequence numbers and a "demo" collection to insert values with an auto incremented identifier:

db.createCollection("demo"); db.createCollection("sequence"); 

An insert can simply fetch the next value while incrementing it:

db.demo.insertOne({ _id: db.sequences.findOneAndUpdate( { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'm the first" }); db.demo.find(); [ { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" } ] 

I start two transactions:

sessionA = db.getMongo().startSession(); sessionA.startTransaction(); dbA = sessionA.getDatabase(db.getName()); sessionB = db.getMongo().startSession(); sessionB.startTransaction(); dbB = sessionB.getDatabase(db.getName()); 

Scalable sequence (accepting gaps on rollback)

The two transactions insert a document into "demo" with an "_id" fetched from the "sequences":

dbA.demo.insertOne({ _id: db.sequences.findOneAndUpdate( // non-transactional { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'll abort" }); dbB.demo.insertOne({ _id: db.sequences.findOneAndUpdate( // non-transactional { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'll commit" }); 

It is important to note that I increment ($inc) and fetch the value (returnDocument: "after") with db, out of the dbA or dbB transactions. The sequence operation is atomic, but non-transactional (not part of a multi-document transaction). This simulates the behavior of sequences in SQL databases.

The first transaction aborts (rollback) and the second one commits:

 sessionA.abortTransaction(); sessionA.endSession(); sessionB.commitTransaction(); sessionB.endSession(); 

I check the result:

db.demo.find() [ { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" }, { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" } ] 

I have a gap in the numbers because {_id: 2} has been used by a transaction that aborted. The transaction has been rolled back, but because I incremented the sequence out of the transaction (using db instead of dbA) the increment was not rolled back.

Note that all updates to a single document are atomic, but I used findOneAndUpdate() so that it returns the updated document in the same atomic operation that updated it. It can return the before or after value and I used returnDocument: "after" to get the next value. I used upsert: true to initialize the sequence if no value exists, and $inc sets the field to the specified value when it doesn't exist.

No-gap Sequences (and optimistic locking)

If you want a no-gap sequence, you can fetch the sequence number in the multi-document transaction:

 sessionA = db.getMongo().startSession(); sessionA.startTransaction(); dbA = sessionA.getDatabase(db.getName()); sessionB = db.getMongo().startSession(); sessionB.startTransaction(); dbB = sessionB.getDatabase(db.getName()); dbA.demo.insertOne({ _id: dbA.sequences.findOneAndUpdate( // part of the transaction { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'll abort" }); dbB.demo.insertOne({ _id: dbB.sequences.findOneAndUpdate( // part of the transaction { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'll commit" }); 

When two transactions try to increment the same sequence, an optimistic locking error is raised:

 MongoServerError[WriteConflict]: Caused by :: Write conflict during plan execution and yielding is disabled. :: Please retry your operation or multi-document transaction. 

This is a retriable error and the application should have implemented a retry logic:

sessionA.abortTransaction(); // retry the insert sessionB.abortTransaction(); sessionB.startTransaction(); dbB.demo.insertOne({ _id: dbB.sequences.findOneAndUpdate( // part of the transaction { _id: "demo_id" }, { $inc: { seq: 1 } }, { upsert: true, returnDocument: "after" } ), value: "I'll commit" }); sessionB.commitTransaction(); sessionB.endSession(); sessionA.endSession(); 

I check the result:

db.demo.find() [ { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" }, { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" }, { _id: { _id: 'demo_id', seq: 4 }, value: "I'll commit" } ] 

Here, the first session rollback didn't introduce another gap because the sequence increment was part of the insert transaction. To achieve the same in a SQL database, you must use a table and UPDATE ... SET seq=seq+1 RETURNING seq;. With pessimistic locking, it acquires a lock and waits for the other transaction to complete. To be scalable, SQL databases provide a non-transactional SEQUENCE that does the same without waiting, but with gaps. It still has some scalability issues, and distributed databases may discourage (CockroachDB raises a warning) or even not support sequences (like Google Spanner or Amazon Aurora DSQL).

Incrementing identifiers for the primary key

MongoDB provides developers with greater control, allowing them to apply the same logic to a collection while deciding whether to include it in a transaction. It also supports optimized atomic operations. You can also use Atlas triggers to deploy the logic into the managed database, like demonstrated in MongoDB Auto-Increment

It's important to note that generating an incremented sequence is typically rare, primarily occurring during migrations from MySQL's AUTO_INCREMENT or PostgreSQL's BIGSERIAL. The default "_id" field is a globally unique and scalable ObjectId. You can also use a UUID generated by the application and choose the format (UUIDv4 or UUIDv7) to distribute or collocate the documents inserted at the same time.