Best Practice for synchronized jobs in Application clusters

Question

We have got 3 REST-Applications within a cluster. So each application server can receive requests from "outside".

Now we got timed events, which are analysing the database and add/remove rows from the database, send emails, etc.

The problem is, that each application server does start this timed events and it happens that 2 application server are starting this analysing job at the same time.

We got a sql table in the back.

Our idea was to lock a table within the sql database, when starting the job. If the table is locked, we exit the job, because an other application just started to analyse.

What's a good practice to insert some kind of semaphore ?

Any ideas ?

Answer 1

Don't use semaphores, you are over complicating things, just use message queueing, where you queue your tasks and get them executed in row.

Make ONLY one separate node/process/child_process to consume from the queue and get your task done.

Answer 2

We (at a previous employer) used a database-based semaphore. Each of several (for redundancy and load sharing) servers had the same set of cron jobs. The first thing in each was a custom library call that did:

1. Connect to the database and check for (or insert) "I'm working on X".
2. If the flag was already set, then the cron job silently exited.
3. When finished, the flag was cleared.

The table included a timestamp and a host name -- for debugging and recovering from cron jobs that fail to finish gracefully.

I forget how the "test and set" was done. Possibly an optimistic INSERT, then check for "duplicate key".