I frequently get a Database Locked error when multiple workers access a database of tasks. As the tasks themselves are considerably longer than updating their status, I wrap each update call with a beginTransaction() and a commit().

Note: I’m using PHP 8.3 with Sqlite via PDO.

Besides inserting, the only writes are updating a single record via a Primary Key.

while (data) {
   status = doSomething(data); // slow
   beginTransaction();
   updateStatus(data.id, status); // update data set status=:status where id=:id
   commit();
}

There’s another process that loads the tasks, in bulk, but that’s pretty fast. I set the pragma to WAL to make it even faster.

There are multiple workers acting on the tasks, but no transaction should much time, yet I’m getting the database locked error a lot.

What am I doing wrong? Should I skip the transaction for updating a single record with the status and data?