I'll continue the conversation in this PR. Gave a thorough review. Let me know if I can assist in something :).

A job is allocated for a fixed time period. And after that period it is reset to the running state. Our queue could run a query > once at start to reset those cases. That would require an additional column with the allocatedAt timestamp.

We're doing this in our implementation with a field named "status_timeout". If a job is in status "ALLOCATING" or "RUNNING" for more than x minutes, then this job is automatically restarted. I didnt port it to this PR because this would mean another field in the table and another check regularly.

Added allocationTimeout.

Still open:

• DB migration (adding fields workerId and allocationTimeout).

@MatthiasKuehneEllerhold I think the migration can simply be stated in the changelog. And that we can bump this library a major version. A migration as part of this code-base is hard because it really depends on what migration strategy you use, and whether this SQL user has enough SQL rights to alter tables.

We should however update somes files in the repo: https://github.com/JouwWeb/SlmQueueDoctrine/blob/master/data/queue_default.sql and https://github.com/JouwWeb/SlmQueueDoctrine/blob/master/tests/Asset/queue_default.sqlite for example. And we should think whether a new index is required, or needs to be set.

Do you have other questions at this point @MatthiasKuehneEllerhold?

Apart from that I think we need to update / fix the test suite at the very least. And maybe look at testing the new functionality :).

Im currently pretty busy, but I hope that I can work on this next week.

@MatthiasKuehneEllerhold No problem off course :). Let me know if I can assist in any way.

@MatthiasKuehneEllerhold Just a kindly reminder. Do you still care to finish this PR? It not we can look if somebody else is willing to step in, as your work seems promising!

Im really sorry to leave you hanging like this, but things have gotten kinda crazy here and I wont be able to contribute at least until january. If anyone wants to jump in and help out, I'd be glad!
Again: really sorry for this!

Haha take care @MatthiasKuehneEllerhold. No worries. This PR will be here waiting if you ever like to finish to ;).

Im terribly sorry for abandoning you here but it was a crazy time. Ive had a little bit of time to properly review our logs und BG-Queue and Im terribly saddened to see that this implementation does not fix the problem Ive outlined.
Although the UPDATE locks less rows than the SELECT FOR UPDATE previously, it still locks too many rows. We're still getting the "deadlock while trying to get lock" exception (about ~ 10 times per day in a queue with ~ 1200 jobs per day and 12 workers). Ive seen clashes between the UPDATE and INSERTs, between the UPDATE and other UPDATEs that just modify 1 job. For a lack of proper fixes we implemented a lot of code to remedy the fact that sometimes jobs go missing, get stuck or ... ?

This problem does not seem to be solvable with MariaDB alone. Ive had proper time now to analyse it and what we can do. Whats problematic is SELECT FOR UPDATE locking the whole table and blanket UPDATEs locking all possible matches.
If we could use a normal SELECT and then UPDATE a single job, that would work. But if 2 jobs simultaneously SELECT the same Job and try to fetch it, we get multiple executions of the same job! Not what we want! But with this way we wont have the locked table / row in the DB.

So what we've come up with is this: we're using redis for an exclusive lock on this code path. It boils down to:

while (true) {
  $lock = acquireLock();
  if ($lock) {
    $job = $db->query('SELECT job');
    if ($job) {
      $db->query('UPDATE job set status = running WHERE id = ' . $job);
    }
  
    releaseLock($lock);
    return $job;
  } else {
    sleep(1);
  }
}

So if multiple workers want to pop a now Job from the queue they can do so... in single file! You can only get to the SELECT+UPDATE pair if you have the lock and redis makes sure, that only one worker can have this lock at the same time.

This way every INSERT or UPDATE on existing Jobs is not impacted. So the if the frontend adds a new job it doesnt have to wait, because there is no table locking. Only workers may have to wait a bit, but thats ok.

For more information on exclusive locks in redis you can read the chapter here: https://redislabs.com/ebook/part-2-core-concepts/chapter-6-application-components-in-redis/6-2-distributed-locking/

Im pretty sure adding redis exclusive locks is severly out of scope for this library because for most it would be easier to just use a different Queue-Provider or database. Im unsure how PostgreSQL solves this problem, so maybe just MariaDB is the culprit here and unsuitable for a message queue (a sentiment I read a lot in the last few days).

@MatthiasKuehneEllerhold Great that you find out before we merged this :). And thanks for you response.

You obviously know more than me, but hear me out. What about the following strategy. I feel like that would work just fine with MySQL and utilizes the fact that an update query is atomic.

1. Each worker gets a unique ID when it starts working.
2. You do an UPDATE queue SET leasedTo = [unique id] WHERE leasedTo IS NULL AND status = 1 .. LIMIT 1
3. The job worker queries all jobs that are leased to him. And only proceeds if exactly one result found.
4. We update that job: UPDATE queue SET executed = NOW(), leasedTo = NULL WHERE id = [..]
5. We execute the job.

There needs to be a mechanism that will unlease the jobs that where leased to a job worker that crashed, after some time.