Lock concurrent crons with postgres database

Question

What I am trying to achieve is to have multiple instances of the same application running at the same time, but only one of those instances running a cron, by locking it in a Postgres database.

My solution so far is :

Running a cron on all the instances.
Inserting a row in a table cron_lock with a unique identifier for the cron.
If I have an error while running the insert query, it is most likely because the row already exists (the cron identifier is the primary key of the table). If that is the case, I do nothing, and I exit.
If I don't have an error while running the insert query, then the application instance will run the cron process.
At the end of my process, I delete the row with the unique identifier.

This solution is working, but I am not sure if another locking mechanism would exist with Postgres, in particular one that would not have me execute queries that are creating errors.

See 13.3.5. Advisory Locks and 9.26.10. Advisory Lock Functions. Section references are v12 and may be different if your version is differeent. If so just click on your version. — Belayer
– Belayer, Commented Apr 21, 2020 at 15:52
What happens if the process fails after inserting, but before deleting? What do you want to happen in that case? What is wrong with creating errors? — jjanes
– jjanes, Commented Apr 21, 2020 at 16:40
@jjanes, in order to avoid a lock staying forever, I would launch a cron running on all instances removing cron_locks that were created more than a certain time ago. Whereas for the creating errors, there is nothing wrong with it, except it is an error, so it would be logged, monitored, etc... So if there is a "cleaner" way to do it, I would prefer. — hellimac
– hellimac, Commented Apr 21, 2020 at 16:57
@Belayer indeed, it seems to be what I could be looking for. I will see if I can make something work with that ! — hellimac
– hellimac, Commented Apr 21, 2020 at 16:58
Hope it works out for you then. But heed @jjanes implied warning. If you get the lock and the process subsequently fails makes sure you release that lock. Advisory locks remain in effect until you release them. — Belayer
– Belayer, Commented Apr 21, 2020 at 17:22

hellimac · Accepted Answer · 2020-05-24 17:06:16Z

Thanks to @Belayer I found a nice way to do it with advisory locks.

Here is my solution :

Each of my crons have an associated and unique ID (integer format). All of the crons start on all the different servers. But before running the main function of the cron, I try to get an advisory lock with the unique ID in the database. If the cron can get the lock, then it will run the main function and free the lock, otherwise, it just stops.

And here is some pseudo code if you want to implement it in a language of your choice :

enum Cron {
  Echo = 1,
  Test = 2
}

function uniqueCron(id, mainFunction) {
  result = POSTGRES ('SELECT pg_try_advisory_lock($id) AS "should_run"')
  if(result == FALSE){ return }

  mainFunction()

  POSTGRES ('SELECT pg_advisory_unlock($id)')
}

cron(* * * * *) do {
  uniqueCron(Cron.Echo, (echo "Unique cron"))
}

cron(*/5 * * * *) do {
  uniqueCron(Cron.Test, (echo "Test"))
}

Running this process many times, or on many different servers, all using the same database, will result in only one mainFunction being executed at once, given that all crons are launched at the same time (same time/timezone on the different servers). A main function too short to execute might cause problems if one server try to get the lock and another already released it. In that case, wait a little before releasing the lock.

Collectives™ on Stack Overflow

Lock concurrent crons with postgres database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related