Updating a collection with a query that specifies sort and limit

Question

I have an email notification collection that will be accessed by multiple threads. I need to lock some records by the current thread so I can work with them exclusively.

My collection looks like this:

public class EmailNotification
{
    [BsonId]
    public string Id { get; set; }

    public DateTime CreatedAt { get; set; }

    public string LockedBy { get; set; }

    public DateTime? LockedUntil { get; set; }

    public int Priority { get; set; }

    ...
}

My idea was to "soft-lock" the records by updating the "LockedBy" and "LockedUntil" fields first (and therefore take advantage of mongoDB update locking) and on a second step retrieve the records I already soft-locked and work with them.

I am having trouble with the first step. I need to lock the "N most pressing emails" with an update command. This means I need to find all records that have the "LockedUntil" field either set to null or to a value in the past, sort them by "Priority" (desc) and then by "CreatedAt" (asc), and update only the top N records that result from this in one single command in order to achieve the soft lock without running into concurrency issues.

I am trying to do this from a C# method, so using the CSharp driver is a must. Is there a way to accomplish what I need with the latest version of the driver (2.6)?

Answer 1

You cannot update multiple documents in a single, atomic update command:

In MongoDB, write operations, e.g. db.collection.update(), db.collection.findAndModify(), db.collection.remove(), are atomic on the level of a single document. For fields that must be updated together, embedding the fields within the same document ensures that the fields can be updated atomically.

http://docs.mongodb.org/manual/tutorial/model-data-for-atomic-operations/

If you attempt to do implement this lock pattern you run the risk of the update command yielding to another process/thread before all of them are updated.

you could instead use a findandmodify call to lock and retrieve documents one at a time, in a loop.

FindAndModify Method

Use FindAndModify when you want to find a matching document and update it in one atomic operation. FindAndModify always updates a single document, and you can combine a query that matches multiple documents with a sort criteria that will determine exactly which matching document is updated. In addition, FindAndModify will return the matching document (either as it was before the update or after) and if you wish you can specify which fields of the matching document to return.

findAndModify is available via the C# driver and it's use is covered here:

http://docs.mongodb.org/ecosystem/tutorial/use-csharp-driver/