Managing shared resources with threads in Huey

Question

I have to update many rows (increment one value in each rows) in peewee database (SqliteDatabase). Some objects can be uncreated so I have to create them with default values before working with them. I would use ways which are in peewee docs (Atomic updates) but I couldn't figure out how to mix model.get_or_create() and in [my_array].

So I decided to make queries in a transaction to commit it once at the end (I hope it does).

Why I'm writting in stack overflow is because I don't know how to work with db.atomic() with threading (I tested with 4 workers) in Huey because .atomic() locks the connection (peewee.OperationalError: database is locked). I've tried to use @huey.lock_task but it's not a solution of my problem as I've found.

Code of my class:

class Article(Model):
    name = CharField()
    mention_number = IntegerField(default=0)

    class Meta:
        database = db

Code of my task:

@huey.task(priority=30)
def update(names):  # "names" is a list of strings
    with db.atomic():
        for name in names:
            article, success = Article.get_or_create(name=name)
            article.mention_number += 1
            article.save()

Answer 1

Well, if you're using a recent version of Sqlite (3.24 or newer) you can use Postgres-style upsert queries. This is well supported by Peewee: http://docs.peewee-orm.com/en/latest/peewee/api.html#Insert.on_conflict

To answer the other question about shared resources, it's not clear from your example what you would like to happen... Sqlite only allows one write transaction at a time. So if you are running several threads, only one of them may be writing at any given time.

Peewee stores database connections in a thread local, so Peewee databases can be safely used in multithreaded applications.

You didn't mention why huey lock_task wouldn't work.

Another suggestion is to try using WAL-mode with Sqlite, as WAL-mode allows multiple reader transactions to co-exist with a single writer.