Level 74
That is normal behavior, it wouldn't matter which database you would use. You need to make sure not to make sure that they don't pick the same rows.
1 like
Oct 6, 2024
5
Level 1
Database locked errors with multiple queue workers and SQLite
I have two queue workers listening on the same queue thats using SQLite. When they both get a lot of short tasks I start seeing database locked errors in logs. This causes the job to have a failed state and be retried even if it was successful. This is a bit problematic since the jobs deal with files and executing them again fails with a file not found.
Here is a relevant log line:
[2024-10-06 09:38:14] local.ERROR: SQLSTATE[HY000]: General error: 5 database is locked (Connection: jobs_db, SQL: update "jobs" set "reserved_at" = 1728207494, "attempts" = 1 where "id" = 1952) {"exception":"[object] (Illuminate\\Database\\QueryException(code: HY000): SQLSTATE[HY000]: General error: 5 database is locked (Connection: jobs_db, SQL: update \"jobs\" set \"reserved_at\" = 1728207494, \"attempts\" = 1 where \"id\" = 1952) at /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[stacktrace]
#0 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\\Database\\Connection->runQueryCallback()
#1 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Connection.php(584): Illuminate\\Database\\Connection->run()
#2 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Connection.php(536): Illuminate\\Database\\Connection->affectingStatement()
#3 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3859): Illuminate\\Database\\Connection->update()
#4 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(336): Illuminate\\Database\\Query\\Builder->update()
#5 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(321): Illuminate\\Queue\\DatabaseQueue->markJobAsReserved()
#6 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(225): Illuminate\\Queue\\DatabaseQueue->marshalJob()
#7 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php(30): Illuminate\\Queue\\DatabaseQueue->Illuminate\\Queue\\{closure}()
#8 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(223): Illuminate\\Database\\Connection->transaction()
#9 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(350): Illuminate\\Queue\\DatabaseQueue->pop()
#10 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(364): Illuminate\\Queue\\Worker->Illuminate\\Queue\\{closure}()
#11 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(163): Illuminate\\Queue\\Worker->getNextJob()
#12 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(139): Illuminate\\Queue\\Worker->daemon()
#13 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(122): Illuminate\\Queue\\Console\\WorkCommand->runWorker()
#14 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Illuminate\\Queue\\Console\\WorkCommand->handle()
#15 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Container/Util.php(43): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#16 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(95): Illuminate\\Container\\Util::unwrapIfClosure()
#17 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#18 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Container/Container.php(690): Illuminate\\Container\\BoundMethod::call()
#19 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Console/Command.php(213): Illuminate\\Container\\Container->call()
#20 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/symfony/console/Command/Command.php(279): Illuminate\\Console\\Command->execute()
#21 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Console/Command.php(182): Symfony\\Component\\Console\\Command\\Command->run()
#22 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/symfony/console/Application.php(1047): Illuminate\\Console\\Command->run()
#23 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/symfony/console/Application.php(316): Symfony\\Component\\Console\\Application->doRunCommand()
#24 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/symfony/console/Application.php(167): Symfony\\Component\\Console\\Application->doRun()
#25 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(197): Symfony\\Component\\Console\\Application->run()
#26 /mnt/Btree/Projekti/bjelopic-ingest-station/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(1203): Illuminate\\Foundation\\Console\\Kernel->handle()
#27 /mnt/Btree/Projekti/bjelopic-ingest-station/artisan(13): Illuminate\\Foundation\\Application->handleCommand()
#28 {main}
Level 1
Doesnt seem like it because the error is trying to update a status of a job and the error doesnt happen when using MySQL (or, MariaDB).
I didn't even have the jobs table in the same sqlite database file. As part of an attempted fix, I created a new DB connection in a SQLite database that only contained jobs, but it still failed with a database locked when trying to update it (see logline above, the "jobs_db" database only contains jobs).
The jobs themselves dont update the same rows, I'm sure of that. Either way, the error isnt thrown by the job. It seems to be thrown by Illuminate\\Queue\\DatabaseQueue->markJobAsReserved()
Level 74
@OrakMoya If I remember correctly, the jobs table uses two indexes, and it might be that too frequent updates to the table causes the indexes to lock the records.
This was the case in another thread here a few years back.
Level 1
@Tray2 So is there a way to fix the busy timeouts? I tried changing the busy_timeout parameter in database.php, enabling WAL and moving jobs to redis but some jobs are in a batch and that table also experiences this issue.
Level 74
@OrakMoya Don't know. I would try to add a little sleep between each iteration though.
Please or to participate in this conversation.