The Doctor's On-Call Shift with MongoDB Snapshot Isolation

#architecture #database #mongodb #softwareengineering

In this series, we explored several ways to solve the "Doctor's On-Call Shift" problem, which demonstrates write skew anomalies and the need for serializable transactions in SQL. Beyond using a serializable isolation level, we also addressed it with normalization, explicit parent locking, and SQL assertions. I applied document modeling in Postgres with SELECT FOR UPDATE as an alternative to a parent-child relationship, so it is natural to consider MongoDB. Since MongoDB lacks explicit locking and a serializable isolation level, we can instead use a simple update that atomically reads and writes in an optimistic concurrency control style.

Here is a collection with one document per shift and a list of doctors with their on-call status for this shift:

db.shifts.insertOne({  
  _id: 1,  
  doctors: [  
    { name: "Alice", on_call: true, updated: new Date() },  
    { name: "Bob",   on_call: true, updated: new Date() }  
  ]  
});

Conditional updateOne() to avoid write skew

The following function encapsulates the business logic in a single update: for a shift with at least one other doctor on call, one doctor can be taken off on-call duty:

function goOffCall(shiftId, doctorName) {  
  const res = db.shifts.updateOne(  
    {   
      _id: shiftId,  
      $expr: {   
        $gte: [  
          {  
            $size: {  
              $filter: {  
                input: "$doctors",  
                as: "d",  
                cond: {  
                  $and: [  
                    { $ne: [ "$$d.name", doctorName ] },  
                    { $eq: [ "$$d.on_call", true ] }  
                  ]  
                }  
              }  
            }  
          },  
          1  
        ]  
      },  
      "doctors.name": doctorName  
    },  
    {   
      $set: { "doctors.$.on_call": false, updated: new Date() }  
    }  
  );  
  return res.modifiedCount > 0 ? "OFF_OK" : "OFF_FAIL";  
}

MongoDB is a document database with many array operators. Here, the condition checks that there is another doctor ($ne: ["$$d.name", doctorName]) who is on call ($eq: ["$$d.on_call", true]). It counts these doctors with $size and keeps only shifts where the count is at least 1. Since there is only one document per shift, if none is returned either the shift doesn’t exist, or the doctor is not in this shift, or there aren’t enough on-call doctors to let one go off call. The following calls show the return code:

test> goOffCall(1,"Alice");

OFF_OK

test> goOffCall(1,"Bob");

OFF_FAIL

Alice was allowed to go off‑call, but Bob couldn’t, because he was the only doctor remaining on‑call.

Testing race conditions

I added a simpler function to set a doctor on call for a shift:

function goOnCall(shiftId, doctorName) {  
  const res = db.shifts.updateOne(  
    {   
      _id: shiftId,  
      "doctors.name": doctorName,  
      "doctors.on_call": false  
    },  
    {   
      $set: { "doctors.$.on_call": true, updated: new Date() }  
    }  
  );  
  return res.modifiedCount > 0 ? "ON_OK" : "ON_FAIL";  
}

Here is Alice back to on-call again:

test> goOnCall(1,"Alice");

ON_OK

I define an assertion function to verify the business rule for a shift by counting the doctors on call:

function checkOnCalls(shiftId) {  
  const pipeline = [  
    { $match: { _id: shiftId } },  
    { $project: {  
        onCallCount: {  
          $size: {  
            $filter: {  
              input: "$doctors",  
              as: "d",  
              cond: "$$d.on_call"  
            }  
          }  
        }  
      }  
    }  
  ];  

  const result = db.shifts.aggregate(pipeline).toArray();  
  if (result.length && result[0].onCallCount < 1) {  
    print(`❌ ERROR! No doctors on call for shift ${shiftId}`);  
    return false;
  }  
  return true;  
}

Now, I run a loop that randomly sets Alice or Bob on call and

const shiftId = 1;  
const doctors = ["Alice", "Bob"];  
const actions = [goOnCall, goOffCall];  

let iteration = 0;  

while (true) {  
  iteration++;  
  const doctor = doctors[Math.floor(Math.random() * doctors.length)];  
  const action = actions[Math.floor(Math.random() * actions.length)];  

  const result = action(shiftId, doctor);  
  print(`Shift ${shiftId}, Iteration ${iteration}: ${doctor} -> ${result}`);  

  if (!checkOnCalls(shiftId)) {  
    print(`🚨 Stopping: assertion broken at iteration ${iteration}`);  
    break;  // exit loop immediately  
  }   
}

I've run this loop in multiple sessions and confirmed that the "Doctor's On-Call" assertion is never violated. This runs indefinitely because MongoDB guarantees data integrity—update operations are ACID:

If you want to stop it and check that the assertion works, you can simply bypass the conditional update and set all doctors to off call:

db.shifts.updateOne(  
  { _id: 1 },  
  { $set: { "doctors.$[doc].on_call": false, updated: new Date() } },  
  { arrayFilters: [ { "doc.on_call": true } ] }  
);

The loops stop when they detect the violation:

MongoDB Schema validation

You can, and should, define schema validations on the part of your schema the application relies on, to be sure that no update bypasses the application model and logic. It is possible to add an 'at least one on‑call' rule:

db.runCommand({  
  collMod: "shifts",  
  validator: {  
    $expr: {  
      $gte: [  
        {  
          $size: {  
            $filter: {  
              input: "$doctors",  
              as: "d",  
              cond: { $eq: ["$$d.on_call", true] }  
            }  
          }  
        },  
        1  
      ]  
    }  
  },  
  validationLevel: "strict"  
});

My manual update immediately fails:

Schema validation is a helpful safeguard, but it does not fully protect against write skew under race conditions. It runs on inserts and updates and, with validationLevel: "strict", raises an error on invalid documents—but only after MongoDB has already matched and targeted the document for update.

Key differences between conditional updates and schema validation:

Approach	When Check Occurs	Failure Mode
Conditional `updateOne`	Before write, atomically with document match	Returns `modifiedCount: 0` (no document updated)
Schema validation	After document match but before write	Returns `DocumentValidationFailure` error

To prevent write skew, you need the correct condition in the update itself. Use schema validation as an extra safeguard for other changes, such as inserts.

Indexing documents

In PostgreSQL, using one row per shift with a JSON array of doctors makes updates atomic and eliminates race conditions but reduces indexing flexibility (for example, range scans on array fields), so the serializable isolation level or normalization to parent-child is preferable. In MongoDB, storing a one-to-many relationship in a single document is native, and full indexing remains available—for example, you can index the updated field for each doctor's on-call status:


db.shifts.createIndex({ "doctors.updated": 1 });

This index supports equality, sorting, and range queries, such as finding shifts where the on-call status changed in the last hour:

const oneHourAgo = new Date(Date.now() - 60 * 60 * 1000);  

db.shifts.find({ "doctors.updated": { $gte: oneHourAgo } });

db.shifts.find(
 { "doctors.updated": { $gte: oneHourAgo } }
).explain("executionStats")

Here is the execution plan:

 executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 0,
    totalKeysExamined: 1,
    totalDocsExamined: 1,
    executionStages: {
      stage: 'FETCH',
      nReturned: 1,
      works: 2,
      advanced: 1,
      isEOF: 1,
      docsExamined: 1,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 1,
        works: 2,
        advanced: 1,
        isEOF: 1,
        keyPattern: {
          'doctors.updated': 1,
          _id: 1
        },
        indexName: 'doctors.updated_1__id_1',
        isMultiKey: true,
        multiKeyPaths: {
          'doctors.updated': [
            'doctors'
          ],
          _id: []
        },
        direction: 'forward',
        indexBounds: {
          'doctors.updated': [
            '[new Date(1770384644918), new Date(9223372036854775807)]'
          ],
          _id: [
            '[MinKey, MaxKey]'
          ]
        },
        keysExamined: 1,
        seeks: 1,
      }
    }
  },

Conclusion

MongoDB's document model and optimistic concurrency control solve the "Doctor's On-Call Shift" problem without explicit locks, serializable isolation, or SQL assertion. By embedding business logic in conditional updateOne operations using $expr and array operators, you can prevent write-skew anomalies at the database level.

Atomic, document-level operations combined with a "First Updater Wins" rule ensure that concurrent updates to the same shift document yield exactly one success and one failure. This approach leverages MongoDB's strengths:

Atomic conditional updates that read and write in one step
Optimistic concurrency control to handle conflicts
Schema validation as an extra integrity check
Flexible indexing on fields inside embedded arrays

Schema validation alone cannot prevent race conditions, but together with conditional updates it protects against concurrency anomalies and direct data corruption.

This pattern shows how MongoDB's document model can simplify concurrency problems that would otherwise require advanced transaction isolation or explicit locking in relational databases. By co-locating related data and using atomic operations, you can maintain integrity with simpler, faster code.