All workers run job when using sqlite3 data store

[legout]

Things to check first

• I have checked that my issue does not already have a solution in the FAQ

• I have searched the existing issues and didn't find my bug already reported there

• I have checked that my bug is still present in the latest release

Version

v4.0.0a5

What happened?

When using sqlite3 as the data store and redis as the event broker and having several workers running, jobs are executed by all workers.

How can we reproduce the bug?

job.py

def job(name, *args, **kwargs):
    print(name, args, kwargs)

scheduler.py

from apscheduler import Scheduler
from apscheduler.datastores.sqlalchemy import SQLAlchemyDataStore
from apscheduler.eventbrokers.redis import RedisEventBroker
from job import job

def main():
    data_store = SQLAlchemyDataStore(
        engine_or_url="sqlite+aiosqlite:////tmp/test.db"
    )
    event_broker = RedisEventBroker("redis://localhost:6379")

    with Scheduler(data_store, event_broker) as sched:
        sched.add_job(job, args=("job1", 1, 2, 3), kwargs=dict(a="A"))

if __name__ == "__main__":
    main()

worker.py

from apscheduler import Scheduler
from apscheduler.datastores.sqlalchemy import SQLAlchemyDataStore
from apscheduler.eventbrokers.redis import RedisEventBroker

def main():
    data_store = SQLAlchemyDataStore(
        engine_or_url="sqlite+aiosqlite:////tmp/test.db"
    )
    event_broker = RedisEventBroker("redis://localhost:6379")

    with Scheduler(data_store, event_broker) as sched:
        sched.run_until_stopped()

if __name__ == "__main__":
    main()

Here is a screenshot of running two workers and scheduling the job four times.

[agronholm]

Please try the code from master. There are boatloads of fixes there compared to v4.0.0a5.

[legout]

Thanks for the quick reply. I´ll give it a try!

[legout]

I´ve upgraded apscheduler to current master. The problem still exists.

Code works as expected, when switching from sqlite3 to postgres.

[agronholm]

For the record, sqlite3 is a pretty bad choice when you need concurrency. But this shouldn't be happening regardless, so I'll try to reproduce the problem locally and investigate.

[legout]

For sure. I´ll definitly use postgres in my production environment. But I´ve came across this bug during my testing. :-)

Thanks you very much for developing this fantastic lib.

[legout]

Hi @agronholm

are there already any updates?

Thanks

[agronholm]

Sorry, not yet. But rest assured I will look at this before the next release.

[legout]

For the record, sqlite3 is a pretty bad choice when you need concurrency. But this shouldn't be happening regardless, so I'll try to reproduce the problem locally and investigate.

Maybe this might help regarding sqlite and concurrency. :-)

https://github.com/tursodatabase/limbo

[legout]

@agronholm
Maybe I can have a look into the code and try to fix this issue. Can you point me to the relevant parts in the code? How does the workers communicate with each others? Is there something like a lock in the datastore or eventbroker as soon as one worker acquires a job?

[agronholm]

There are acquired_by and acquired_until fields which are filled in by the data store (after acquiring row-level locks on the jobs). Other schedulers then see these and skip these jobs when looking for new ones.

Probably not relevant to this problem, but there are also JobAcquired events being broadcast by a scheduler when it acquires new jobs. Is this enough information?

[legout]

Yeah, I think this is enough for the start.

[legout]

I hope that it is ok, to document the debugging of this issue further here.

First test with three workers shows:

Sqlite data store:

• aquired_by and aquired_until fields are set by one worker, but the other ones are not aware of it.

Same for the postgres data store looks fine.

Does that mean, that the sqlite db is "to slow" for this? Means, the write transaction (setting acquired_by...) of one worker isn´t finished before another worker reads from the jobs table?

[agronholm]

Sqlite is supposed to lock the database file for a transaction to prevent concurrent use. Is this not happening?

[legout]

You know, how I can check whether this is happening or not?

[agronholm]

You could try to run multiple processes against the same database which increment a value, wait a couple seconds and then decrement it, and then commit the transaction. If the value starts drifting away from 0 and 1, then you know there's a problem.