python - DjangoCelery SQLite database locked on concurrent access - Stack Overflow

admin管理员组

文章数量:1279050

I have a local Django 5.1/Celery 5.4 project that is using SQLite. I am the only user.

Certain model saves trigger a Celery task that queries (SELECT) for the updated record (using the Django ORM), then runs an API call to update a remote record based on the local data, and then runs another UPDATE locally. The task wraps all this inside of with transaction.atomic():.

(The Celery worker is configured to run tasks in serial.)

While this task is running, any attempts to write to the database result in a "database is locked" OperationalError.

I have configured Django/SQLite with the latest "production-ready" settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': DB_DIR / 'db.sqlite3',
        'OPTIONS': {
            'init_command': """
                PRAGMA foreign_keys=ON;
                PRAGMA journal_mode=WAL;
                PRAGMA synchronous=NORMAL;
                PRAGMA busy_timeout = 5000;
                PRAGMA temp_store = MEMORY;
                PRAGMA mmap_size=134217728;
                PRAGMA journal_size_limit=67108864;
                PRAGMA cache_size=2000;
            """,
            'transaction_mode': 'IMMEDIATE',
            'timeout': 20,
        },
    },
}

I was under the impression that with these settings, concurrent access was possible. "SQLite in Production" is the latest hotness, and these settings, especially the new-to-Django 5.1 'transaction_mode': 'IMMEDIATE' in OPTIONS, would allow writes to queue. What am I missing?

I have a local Django 5.1/Celery 5.4 project that is using SQLite. I am the only user.

Certain model saves trigger a Celery task that queries (SELECT) for the updated record (using the Django ORM), then runs an API call to update a remote record based on the local data, and then runs another UPDATE locally. The task wraps all this inside of with transaction.atomic():.

(The Celery worker is configured to run tasks in serial.)

While this task is running, any attempts to write to the database result in a "database is locked" OperationalError.

I have configured Django/SQLite with the latest "production-ready" settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': DB_DIR / 'db.sqlite3',
        'OPTIONS': {
            'init_command': """
                PRAGMA foreign_keys=ON;
                PRAGMA journal_mode=WAL;
                PRAGMA synchronous=NORMAL;
                PRAGMA busy_timeout = 5000;
                PRAGMA temp_store = MEMORY;
                PRAGMA mmap_size=134217728;
                PRAGMA journal_size_limit=67108864;
                PRAGMA cache_size=2000;
            """,
            'transaction_mode': 'IMMEDIATE',
            'timeout': 20,
        },
    },
}

I was under the impression that with these settings, concurrent access was possible. "SQLite in Production" is the latest hotness, and these settings, especially the new-to-Django 5.1 'transaction_mode': 'IMMEDIATE' in OPTIONS, would allow writes to queue. What am I missing?

• python
• django
• sqlite

Share Improve this question Follow edited Feb 24 at 17:38 David Eyk asked Feb 24 at 17:32 David EykDavid Eyk 12.6k1212 gold badges6868 silver badges106106 bronze badges 7

• AFAIK, WAL only enables concurrency in the read-while-writing or vice versa scenario, it doesn't enable write-while-writing concurrency. Although see BEGIN CONCURRENT if you haven't already. – Vegard Commented Feb 24 at 20:02
• @Vegard That results in the following error on startup: django.core.exceptions.ImproperlyConfigured: settings.DATABASES['default']['OPTIONS']['transaction_mode'] is improperly configured to 'CONCURRENT'. Use one of 'DEFERRED', 'EXCLUSIVE', 'IMMEDIATE', or None. – David Eyk Commented Feb 24 at 20:34
• Huh, I don't know where you found that link, but searching "BEGIN CONCURRENT" through the regular documentation site turns up nothing: sqlite./search?s=d&q=BEGIN+CONCURRENT – David Eyk Commented Feb 24 at 20:39
• I'm not sure where the hold-up on BEGIN CONCURRENT is, but it might be in Django. You could try customizing the db wrapper to add it, similar to this. – Vegard Commented Feb 25 at 15:34
• 1 Apologies for possibly wasting your time, it appears that BEGIN CONCURRENT is not in the main branch yet. You'd have to compile that branch from source. – Vegard Commented Feb 25 at 19:32

 |  Show 2 more comments

2 Answers 2

Sorted by: Reset to default 1

While SQLite can handle some concurrency with WAL, it’s still limited in multi-process environments (like Django + Celery). I'd suggest to to either use a separate DB for Celery or switch to a full-fledged database like PostgreSQL.

If your Celery tasks don't need to run SQL-heavy operations locally, you can give a try to a separate database for Celery before switching into a better DB config:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': DB_DIR / 'db.sqlite3',
        'OPTIONS': {...},  # Your app DB
    },
    'celery': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': DB_DIR / 'celery.sqlite3',
        'OPTIONS': {...},  # A dedicated instance for Celery
    },
}

And then call:

from django.db import connections
def your_task():
    with connections['celery'].cursor() as cursor:
        cursor.execute("YOUR QUERY")

More about the topic on Django docs about the “Database is locked” error

The solution in this particular case was to shorten my transaction times, i.e. don't hold on to a transaction while making an external API call. This means I have to be more careful about not letting the views and tasks step on each others toes.

I'm still flummoxed that so-called "production ready" settings don't allow concurrent access, with not so much as a queue + timeout!

本文标签： pythonDjangoCelery SQLite database locked on concurrent accessStack Overflow