Back
sinnbeck

René Sinnbeck

@sinnbeck

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

1 • •

121

•
sinnbeck

René Sinnbeck

@sinnbeck

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..

1 • •

28

•
ozdemiru

Ulaş Özdemir

@ozdemiru

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.

1 • •

21

•
sinnbeck

René Sinnbeck

@sinnbeck

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

2 • •

12

•
ozdemiru

Ulaş Özdemir

@ozdemiru

In response to @sinnbeck

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

SHOW ENGINE INNODB STATUS;

1 • •

14

•
  • No matching results...
  • Searching...

/ 1000

sinnbeck

René Sinnbeck

@sinnbeck

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'

1 • •

10

•
ozdemiru

Ulaş Özdemir

@ozdemiru

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; 
    }
}

2 • •

7

•
sinnbeck

René Sinnbeck

@sinnbeck

It never hits the exception. But I think there might be a weird bug in the job itself. If I just tell the job to return instantly instead of actually running the code, it works..!

1 • •

3

•
ozdemiru

Ulaş Özdemir

@ozdemiru

Yup, looks like there is nothing wrong with what we have here so far. From the db point there is only one more thing I can think of and it's testing with a different isolation level.

Have you tried simulating the job?

<?php
function runJob() {
    error_log("Job started");

    sleep(1); 

    error_log("Job completed");
}

runJob();
?>



btw indexing would significantly change the transaction performance.

• •

7

•
sinnbeck

René Sinnbeck

@sinnbeck

I managed to fix it. The job changed some stuff in the dependency injection container, which was never rolled back.. So I added a call to reset the DIC on every run :D

Thanks for all your help!

1 • •

94

•
ozdemiru

Ulaş Özdemir

@ozdemiru

Great! Good work mate!

• •

96

•