Working with plain old php today (not laravel) :)

Trying to fix a weird bug. I have written my own queue handler, but if I let supervisor start 2 or more processes, they all pick the same job off the jobs table.. The query is inside a transaction. It runs a select to find the next job, and then sets the reserved column to a timestamp..

I even tried adding FOR UPDATE to the select query, but that seems to never release the lock for some reason.. I cannot close the queue command normally, and if I run "SHOW FULL PROCESSLIST" I can see the update query in the list forever..

Any pdo/sql/php geniuses around that might have any idea on how to fix this!? :D

I am trying to dig into the queue source of Laravel to figure out how it handles it, but no luck so far :( It just runs ->lock(true) on the select query and that apparently takes care of it..

It looks like a mysql issue more than a php issue you are dealing with here if I understand it correctly. If you don’t explicitly commit or roll back, the transaction will stay open, and the lock won’t work. AUTOCOMMIT might be turned off so you may try to manually commit the transaction after the UPDATE query. Also mysql has GET_LOCK() function which might be handy in your case instead of relying on FOR UPDATE.

If I get the problem wrong please let me know. I'm interested.

Thanks for the input!

Yeah it kinda feels that way. It sounds like you are understanding correctly.

I honestly just dont understand why the commit isn't releasing it. I have AUTOCOMMIT on and I also run ->commit() on pdo (dba is just a wrapper around pdo)

    public function transaction(Closure $callback)
    {
        $this->dba->beginTransaction();
        try {
            $result = $callback();

            if ($this->dba->inTransaction()) {
                $this->dba->commit();

            }

            return $result;
        } catch (Exception) {
            $this->dba->rollBack();
            return null;
        }

    }

As far as I can tell Laravel use 'FOR UPDATE` as well, and does not lock up..

I am running MariaDB version 10.3.31

Can you check if you have any deadlocks or lock waits?

SHOW ENGINE INNODB STATUS;

There is yes. It is the second job added to the queue. The first one runs as expected..

---TRANSACTION 4377655, ACTIVE 40 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 26, OS thread handle 139871028414208, query id 2093 172.20.0.5 foo Updating
UPDATE jobs SET reserved_at = '1726473640' WHERE id = '158'

I'd go for logging for further inspection and wrap the rollback with inTransaction() too

public function transaction(Closure $callback)
{
    try {
        $this->dba->beginTransaction();

        $result = $callback();

        if ($this->dba->inTransaction()) {
            $this->dba->commit();
        }

        return $result;
    } catch (Exception $e) {
        if ($this->dba->inTransaction()) {
            $this->dba->rollBack();
        }

        error_log($e->getMessage());
        return null; 
    }
}