Coordinate access to resource between workers

Question

I have a database accessed by an several servers, let's call them workers. These workers take one item from the database, do some operations on it and then update it back into the database. The workers are also volatile, there can be any amount of them and they can die/start at any time.

My problem is coordination of resources across workers. I want to ensure that there aren't 2 workers working on the same item. I also want to ensure that if a worker dies while he was working on an item, this item will be released to another server (either immediately or via timeout).

How can I ensure this coordination? I know that I can implement some custom logic myself using database transactions and some kind of server discovery/monitoring.

This seems like a common problem but I am having a hard time googling for a solution, I don't know which terms to search for.

Answer 1

Why not use built-in database synchronization? All enterprise-class SQL databases are ACID-compliant, as are most NoSQL databases.

1. Worker acquires a task. You may need to use a DB-specific syntax for this:

   UPDATE task SET status = 'in process', worker = (this worker's ID) WHERE status = 'new' LIMIT 1

   SELECT * FROM task WHERE status = 'in process' AND worker = (this worker's ID)

2. Worker works on the task: it can periodically update a timestamp on the task to indicate it is not timing out.

3. Worker finishes: it updates the task to set its status to complete and save the results wherever they need to go.

I would have a job running in the background to look for timed out tasks and reset them. This would likely be simpler than adding extra logic to the SELECT query.

There are likely existing solutions out there to do this. Using them may be easier than rolling your own depending on the complexity of managing the tasks and your requirements. Specifically, something like Hadoop would likely both get the job done and be overkill for what you describe here.