0

I have the following collections in my MongoDB

Task:

{
_id: string;
name: string;
}

Employee:

{
_id:string;
name: string;
tasks: string[];// contains the ids of the assigned tasks.
}

I want to delete a task if it's not assigned to any employee, so I tried the following code(typescript)

1-async deleteTask(taskID:string){
2-const emp =await db.employeesCollection.findOne({tasks:taskID});
3-if(emp) throw WrongOperationException("can't delete task while some employees working on it.");
4-// race condition could occur here when someone assign the task to some employees 
5-await db.tasksCollection.deleteOne({_id:taskID});
6-}

the function is working fine but as mentioned in the comment, a race condition may occur before the deletion actually committed which will cause some employees to be assigned to a task that doesn't exist.

what's the best solution to make sure that the task is not assigned to any employee before deletion?

  • I recommend storing the employee assigned to the task in the task object itself. In doing so, you can simply delete by taskId where employee is null. Your race condition occurs because you are implementing MongoDB like a relational database. The other approach would be to use a database transaction which is slow. if you choose to use a database transaction, you must fake a select for update - meaning temporary change the employee object to lock it since MongoDB does not have select for update capabilities. – barrypicker Nov 24 '21 at 16:43
  • @barrypicker I need to store the relation on the employee object because the task could be assigned to hundreds of employees while the employee works on a few tasks. Your idea about using the transaction and faking a select for update looks interesting could you explain it more? – Amjad Roy Nov 24 '21 at 16:57
  • MongoDB supports multi-document transactions starting with version 4.0, and sharded multi-document transactions starting with version 4.2. If you feel there is a potential race condition because related records can be modified during the operation you can lock the expected records first to perform an atomic update on multiple related objects. I think there is some discovery about the relationships based on your data model that need to be recognized to determine best course of action for transactions. – barrypicker Nov 24 '21 at 20:23
  • Based on what you described, you have a many-to-many relationship between employee and tasks, and you describe that relationship via an array of tasks found on the employee. To simplify things you may want to adjust the way you represent the relationships. – barrypicker Nov 24 '21 at 20:26
  • @barrypicker The relation is "many employees-to-few tasks". I want to lock employees collection from having a particular task on their tasks array right after checking if there are any existing relationships. On the system, the employee could be assigned to a task when the employee is created or modified. Is there any way to perform all these steps atomically? – Amjad Roy Nov 24 '21 at 21:53
  • In an ER model the word "few" means "many". Either you have a 1:1, 1:many, or many:many. In either case I believe I understand your use-case. You cannot, and should not ever lock an entire collection because *some* records might be in your process. This is a recipe for disaster. Your data model is designed where you have no other choice. As such, I recommend you re-think how you store the data to achieve your objective. – barrypicker Nov 24 '21 at 22:50

0 Answers0