Laravel队列运行时SQLite数据库锁定问题求助

问题场景 #

执行以下命令时：

php artisan queue:retry all
php artisan queue:work

数据偶尔能正常写入jobs和failed_jobs表，但会随机出现数据库锁错误，错误日志如下：

SQLSTATE[HY000]: General error: 5 database is locked (SQL: update "jobs" set "reserved_at" = 1690175898, "attempts" = 1 where "id" = 1000) {"exception":"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 5 database is locked (SQL: update "jobs" set "reserved_at" = 1690175898, "attempts" = 1 where "id" = 1000) at vendor\laravel\framework\src\Illuminate\Database\Connection.php:716) [stacktrace] #0 vendor\laravel\framework\src\Illuminate\Database\Connection.php(676): Illuminate\Database\Connection->runQueryCallback() #1 vendor\laravel\framework\src\Illuminate\Database\Connection.php(533): Illuminate\Database\Connection->run() #2 vendor\laravel\framework\src\Illuminate\Database\Connection.php(466): Illuminate\Database\Connection->affectingStatement() #3 vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php(3205): Illuminate\Database\Connection->update() #4 vendor\laravel\framework\src\Illuminate\Queue\DatabaseQueue.php(326): Illuminate\Database\Query\Builder->update() #5 vendor\laravel\framework\src\Illuminate\Queue\DatabaseQueue.php(309): Illuminate\Queue\DatabaseQueue->markJobAsReserved() #6 vendor\laravel\framework\src\Illuminate\Queue\DatabaseQueue.php(221): Illuminate\Queue\DatabaseQueue->marshalJob() #7 vendor\laravel\framework\src\Illuminate\Database\Concerns\ManagesTransactions.php(29): Illuminate\Queue\DatabaseQueue->Illuminate\Queue\{closure}() #8 vendor\laravel\framework\src\Illuminate\Queue\DatabaseQueue.php(223): Illuminate\Database\Connection->transaction() #9 vendor\laravel\framework\src\Illuminate\Queue\Worker.php(345): Illuminate\Queue\DatabaseQueue->pop() #10 vendor\laravel\framework\src\Illuminate\Queue\Worker.php(354): Illuminate\Queue\Worker->Illuminate\Queue\{closure}() #11 vendor\laravel\framework\src\Illuminate\Queue\Worker.php(159): Illuminate\Queue\Worker->getNextJob() #12 vendor\laravel\framework\src\Illuminate\Queue\Console\WorkCommand.php(126): Illuminate\Queue\Worker->daemon() #13 vendor\laravel\framework\src\Illuminate\Queue\Console\WorkCommand.php(110): Illuminate\Queue\Console\WorkCommand->runWorker() #14 vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(36): Illuminate\Queue\Console\WorkCommand->handle() #15 vendor\laravel\framework\src\Illuminate\Container\Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}() #16 vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure() #17 vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(37): Illuminate\Container\BoundMethod::callBoundMethod() #18 vendor\laravel\framework\src\Illuminate\Container\Container.php(653): Illuminate\Container\BoundMethod::call() #19 vendor\laravel\framework\src\Illuminate\Console\Command.php(136): Illuminate\Container\Container->call() #20 vendor\symfony\console\Command\Command.php(291): Illuminate\Console\Command->execute() #21 vendor\laravel\framework\src\Illuminate\Console\Command.php(121): Symfony\Component\Console\Command\Command->run() #22 vendor\symfony\console\Application.php(989): Illuminate\Console\Command->run() #23 vendor\symfony\console\Application.php(299): Symfony\Component\Console\Application->doRunCommand() #24 vendor\symfony\console\Application.php(171): Symfony\Component\Console\Application->doRun() #25 vendor\laravel\framework\src\Illuminate\Console\Application.php(102): Symfony\Component\Console\Application->run() #26 vendor\laravel\framework\src\Illuminate\Foundation\Console\Kernel.php(129): Illuminate\Console\Application->run() #27 artisan(37): Illuminate\Foundation\Console\Kernel->handle() #28 {main}

环境信息 #

• Laravel版本：9.2
• 数据库：SQLite

解决方案建议 #

• 限制队列工作进程数：SQLite是单文件数据库，不支持多进程并发写入，执行queue:work时指定单进程，避免锁竞争：

  php artisan queue:work --max-processes=1
  

• 调整SQLite连接超时设置：在config/database.php的SQLite配置中添加锁等待超时，延长等待时间：

  'sqlite' => [
      'driver' => 'sqlite',
      'database' => database_path('database.sqlite'),
      'prefix' => '',
      'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
      'options' => [
          PDO::ATTR_TIMEOUT => 10, // 锁等待超时（单位：秒）
      ],
  ],
  

• 避免同时执行重试与工作命令：不要在queue:work运行时执行queue:retry all，两者操作同一张表会引发锁冲突，需先停止工作进程再执行重试。
• 更换队列驱动：如果业务需要高并发队列处理，建议切换到Redis、Beanstalkd等支持并发的驱动，SQLite仅适合开发或低并发场景。

内容的提问来源于stack exchange，提问作者Sevymol Sebastian