DB Lock for Schedules tasks

[brbrbr]

In the comments of the TaskModel I found a remark on the problem of 'unlocking' (crashed) scheduled tasks.

Couldn't this be solved using database locks?

Both mysql ( GET_LOCK) and postgresql (pg_try_advisory_lock) have database locks. Those are automatically released as soon as the database sessions stop. So there is no issue any more when aborted tasks (with locked set) block the whole schedule

Not sure whether I should put it here or create a PR, but I gave it a try in:

https://github.com/brbrbr/joomla-cms/blob/task-dblock/administrator/components/com_scheduler/src/Model/TaskModel.php#L375:L381

https://github.com/brbrbr/joomla-cms/blob/task-dblock/administrator/components/com_scheduler/src/Model/TaskModel.php#L460

This ensures that each site only runs one task. It can easily be changed to lock per task (and not per site)

You can enforce a lock using one of the commands below within a terminal window and a persistent database connection
(phpmyadmin etc won't work as the session is terminated after each query)

psql: SELECT pg_try_advisory_lock(id);
mysql: SELECT GET_LOCK('id',0);

id is unique for each site. I can be found in the response headers, for debugging.

If the database is locked the 'Last Run Date' should not change if you hit the 'test run' button in the Scheduled Tasks dashboard.
However, there is no feedback in the modal, the message is in the ajax response, but that one is not displayed. (Neither in the cirrent situation)

The locked field is not set, so you won't have a running man icon in the dashboard.

There might be an issue for very long-running tasks, closing the database connection. Could they add there own locking?