On this sequence, we explored a number of methods to resolve the “Physician’s On-Name Shift” drawback, which demonstrates write skew anomalies and the necessity for serializable transactions in SQL. Past utilizing a serializable isolation degree, we additionally addressed it with normalization, specific mother or father locking, and SQL assertions. I utilized doc modeling in Postgres with SELECT FOR UPDATE as a substitute for a parent-child relationship, so it’s pure to contemplate MongoDB. Since MongoDB lacks specific locking and a serializable isolation degree, we are able to as an alternative use a easy replace that atomically reads and writes in an optimistic concurrency management fashion.
Here’s a assortment with one doc per shift and an inventory of medical doctors with their on-call standing for this shift:
db.shifts.insertOne({
_id: 1,
medical doctors: [
{ name: "Alice", on_call: true, updated: new Date() },
{ name: "Bob", on_call: true, updated: new Date() }
]
});
Conditional updateOne() to keep away from write skew
The next operate encapsulates the enterprise logic in a single replace: for a shift with at the very least one different physician on name, one physician may be taken off on-call obligation:
operate 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
]
},
"medical doctors.identify": doctorName
},
{
$set: { "medical doctors.$.on_call": false, up to date: new Date() }
}
);
return res.modifiedCount > 0 ? "OFF_OK" : "OFF_FAIL";
}
MongoDB is a doc database with many array operators. Right here, the situation checks that there’s one other physician ($ne: ["$$d.name", doctorName]) who’s on name ($eq: ["$$d.on_call", true]). It counts these medical doctors with $dimension and retains solely shifts the place the rely is at the very least 1. Since there is just one doc per shift, if none is returned both the shift doesn’t exist, or the physician shouldn’t be on this shift, or there aren’t sufficient on-call medical doctors to let one go off name. The next calls present the return code:
check> goOffCall(1,"Alice");
OFF_OK
check> goOffCall(1,"Bob");
OFF_FAIL
Alice was allowed to go off‑name, however Bob couldn’t, as a result of he was the one physician remaining on‑name.
Testing race circumstances
I added an easier operate to set a health care provider on name for a shift:
operate goOnCall(shiftId, doctorName) {
const res = db.shifts.updateOne(
{
_id: shiftId,
"medical doctors.identify": doctorName,
"medical doctors.on_call": false
},
{
$set: { "medical doctors.$.on_call": true, up to date: new Date() }
}
);
return res.modifiedCount > 0 ? "ON_OK" : "ON_FAIL";
}
Right here is Alice again to on-call once more:
check> goOnCall(1,"Alice");
ON_OK
I outline an assertion operate to confirm the enterprise rule for a shift by counting the medical doctors on name:
operate checkOnCalls(shiftId) {
const pipeline = [
{ $match: { _id: shiftId } },
{ $project: {
onCallCount: {
$size: {
$filter: {
input: "$doctors",
as: "d",
cond: "$$d.on_call"
}
}
}
}
}
];
const outcome = db.shifts.mixture(pipeline).toArray();
if (outcome.size && outcome[0].onCallCount < 1) {
print(`❌ ERROR! No medical doctors on name for shift ${shiftId}`);
return false;
}
return true;
}
Now, I run a loop that randomly units Alice or Bob on name and
const shiftId = 1;
const medical doctors = ["Alice", "Bob"];
const actions = [goOnCall, goOffCall];
let iteration = 0;
whereas (true) {
iteration++;
const physician = medical doctors[Math.floor(Math.random() * doctors.length)];
const motion = actions[Math.floor(Math.random() * actions.length)];
const outcome = motion(shiftId, physician);
print(`Shift ${shiftId}, Iteration ${iteration}: ${physician} -> ${outcome}`);
if (!checkOnCalls(shiftId)) {
print(`🚨 Stopping: assertion damaged at iteration ${iteration}`);
break; // exit loop instantly
}
}
I’ve run this loop in a number of classes and confirmed that the “Physician’s On-Name” assertion is rarely violated. This runs indefinitely as a result of MongoDB ensures information integrity—replace operations are ACID:
If you wish to cease it and verify that the assertion works, you may merely bypass the conditional replace and set all medical doctors to off name:
db.shifts.updateOne(
{ _id: 1 },
{ $set: { "medical doctors.$[doc].on_call": false, up to date: new Date() } },
{ arrayFilters: [ { "doc.on_call": true } ] }
);
The loops cease after they detect the violation:
MongoDB Schema validation
You’ll be able to, and may, outline schema validations on the a part of your schema the appliance depends on, to ensure that no replace bypasses the appliance mannequin and logic. It’s potential so as to add an ‘at the very least one on‑name’ rule:
db.runCommand({
collMod: "shifts",
validator: {
$expr: {
$gte: [
{
$size: {
$filter: {
input: "$doctors",
as: "d",
cond: { $eq: ["$$d.on_call", true] }
}
}
},
1
]
}
},
validationLevel: "strict"
});
My handbook replace instantly fails:
Schema validation is a useful safeguard, nevertheless it doesn’t totally defend towards write skew below race circumstances. It runs on inserts and updates and, with validationLevel: "strict", raises an error on invalid paperwork—however solely after MongoDB has already matched and focused the doc for replace.
Key variations between conditional updates and schema validation:
| Strategy | When Examine Happens | Failure Mode |
|---|---|---|
Conditional updateOne
|
Earlier than write, atomically with doc match | Returns modifiedCount: 0 (no doc up to date) |
| Schema validation | After doc match however earlier than write | Returns DocumentValidationFailure error |
To forestall write skew, you want the right situation within the replace itself. Use schema validation as an additional safeguard for different adjustments, resembling inserts.
Indexing paperwork
In PostgreSQL, utilizing one row per shift with a JSON array of medical doctors makes updates atomic and eliminates race circumstances however reduces indexing flexibility (for instance, vary scans on array fields), so the serializable isolation degree or normalization to parent-child is preferable. In MongoDB, storing a one-to-many relationship in a single doc is native, and full indexing stays out there—for instance, you may index the up to date discipline for every physician’s on-call standing:
db.shifts.createIndex({ "medical doctors.up to date": 1 });
This index helps equality, sorting, and vary queries, resembling discovering shifts the place the on-call standing modified within the final hour:
const oneHourAgo = new Date(Date.now() - 60 * 60 * 1000);
db.shifts.discover({ "medical doctors.up to date": { $gte: oneHourAgo } });
db.shifts.discover(
{ "medical doctors.up to date": { $gte: oneHourAgo } }
).clarify("executionStats")
Right here is the execution plan:
executionStats: {
executionSuccess: true,
nReturned: 1,
executionTimeMillis: 0,
totalKeysExamined: 1,
totalDocsExamined: 1,
executionStages: {
stage: 'FETCH',
nReturned: 1,
works: 2,
superior: 1,
isEOF: 1,
docsExamined: 1,
inputStage: {
stage: 'IXSCAN',
nReturned: 1,
works: 2,
superior: 1,
isEOF: 1,
keyPattern: {
'medical doctors.up to date': 1,
_id: 1
},
indexName: 'medical doctors.updated_1__id_1',
isMultiKey: true,
multiKeyPaths: {
'medical doctors.up to date': [
'doctors'
],
_id: []
},
path: 'ahead',
indexBounds: {
'medical doctors.up to date': [
'[new Date(1770384644918), new Date(9223372036854775807)]'
],
_id: [
'[MinKey, MaxKey]'
]
},
keysExamined: 1,
seeks: 1,
}
}
},
Conclusion
MongoDB’s doc mannequin and optimistic concurrency management resolve the “Physician’s On-Name Shift” drawback with out specific locks, serializable isolation, or SQL assertion. By embedding enterprise logic in conditional updateOne operations utilizing $expr and array operators, you may stop write-skew anomalies on the database degree.
Atomic, document-level operations mixed with a “First Updater Wins” rule make sure that concurrent updates to the identical shift doc yield precisely one success and one failure. This method leverages MongoDB’s strengths:
- Atomic conditional updates that learn and write in a single step
- Optimistic concurrency management to deal with conflicts
- Schema validation as an additional integrity verify
- Versatile indexing on fields inside embedded arrays
Schema validation alone can not stop race circumstances, however along with conditional updates it protects towards concurrency anomalies and direct information corruption.
This sample exhibits how MongoDB’s doc mannequin can simplify concurrency issues that may in any other case require superior transaction isolation or specific locking in relational databases. By co-locating associated information and utilizing atomic operations, you may keep integrity with easier, sooner code.
