database is locked. Что на самом деле блокирует SQLite в Telegram-боте и как я это чиню

Бот для приёма заказов, aiogram, база SQLite в одном файле. Заказчику нужна была ещё ежедневная выгрузка в Excel, и её я сделал отдельным скриптом по крону, который читает тот же файл. Месяц всё жило спокойно. Потом заказчик прислал скриншот: бот на «Оформить заказ» молчит секунд пять, а потом пишет «Что-то пошло не так». В логах:

sqlite3.OperationalError: database is locked

Ошибка плавающая, раз в два-три дня, а нагрузка там десяток заказов в час. В этом проекте я потом нашёл сразу две причины из трёх, про которые ниже. Третью встречал в чужих ботах, которые приносили на доработку.

Как SQLite вообще блокирует

В режиме по умолчанию (rollback journal) запись в базу эксклюзивная. Пока одно соединение держит открытую пишущую транзакцию, другое не может начать свою. Второе соединение получает SQLITE_BUSY, и модуль sqlite3 превращает его в database is locked.

Перед тем как сдаться, sqlite3 какое-то время ждёт. За это отвечает параметр timeout у connect(), по умолчанию 5 секунд. Те самые пять секунд тишины в боте.

Воспроизводится в несколько строк:

import sqlite3, time

db = sqlite3.connect("shop.db")
db.execute("CREATE TABLE IF NOT EXISTS orders("
           "id INTEGER PRIMARY KEY, user_id INT, status TEXT)")
db.commit()

a = sqlite3.connect("shop.db")
b = sqlite3.connect("shop.db", timeout=1)

a.execute("INSERT INTO orders(user_id, status) VALUES (1, 'new')")
print(a.in_transaction)  # True

t = time.perf_counter()
try:
    b.execute("INSERT INTO orders(user_id, status) VALUES (2, 'new')")
except sqlite3.OperationalError as e:
    print(e, round(time.perf_counter() - t, 2))
# database is locked 1.15

Обратите внимание на a.in_transaction. Я нигде не писал BEGIN, а транзакция открыта.

Причина 1. Неявная транзакция и забытый commit

Модуль sqlite3 по умолчанию работает в «legacy»-режиме: перед первым INSERT, UPDATE или DELETE он сам отправляет BEGIN, а закрывается транзакция только на commit() или rollback(). Если между ними код ушёл ждать чего-то долгого или вообще забыл закоммитить, файл заблокирован на запись для всех остальных.

В том боте было примерно так:

@router.message(OrderForm.address)
async def got_address(message: Message, state: FSMContext):
    cur.execute("UPDATE users SET address = ? WHERE tg_id = ?",
                (message.text, message.from_user.id))
    data = await state.get_data()
    await message.answer(render_summary(data))  # сетевой запрос к Telegram
    conn.commit()

UPDATE открыл транзакцию, дальше хендлер пошёл в сеть отправлять сообщение. Обычно это доли секунды. Но если Telegram отвечает медленно или бот упёрся во флуд-лимит, транзакция висит, а крон в это время пишет в ту же базу отметку о выгрузке. Хуже, если между UPDATE и commit() вылетело исключение: aiogram его залогирует, хендлер завершится, а транзакция на этом соединении так и останется открытой до следующего commit() где-нибудь в другом месте.

Исправление скучное: коммит сразу после записи, до любых await. Удобнее писать через with conn:, он делает commit при успехе и rollback при исключении.

Причина 2. «Прочитал, потом записал»

Эту я люблю больше всего: timeout тут не помогает, ошибка прилетает мгновенно.

Типичный код: проверить что-то в базе и по результату записать. Например, не больше трёх незакрытых заказов на пользователя.

a = sqlite3.connect("shop.db", timeout=5, isolation_level=None)
b = sqlite3.connect("shop.db", timeout=5, isolation_level=None)
a.execute("PRAGMA journal_mode=WAL")

a.execute("BEGIN")
n = a.execute("SELECT count(*) FROM orders "
              "WHERE user_id = 1 AND status = 'new'").fetchone()[0]

# в это время другое соединение успело записать
b.execute("INSERT INTO orders(user_id, status) VALUES (3, 'new')")

try:
    a.execute("INSERT INTO orders(user_id, status) VALUES (1, 'new')")
except sqlite3.OperationalError as e:
    print(e, e.sqlite_errorname)
# database is locked SQLITE_BUSY_SNAPSHOT

Время до исключения у меня 0.0 секунды, хотя timeout=5. Обычный BEGIN в SQLite означает BEGIN DEFERRED: транзакция стартует как читающая и пытается стать пишущей на первом INSERT. Соединение a уже прочитало снимок базы, а b успело её изменить. Дать a записать поверх старого снимка SQLite не может, и ждать тут бессмысленно, снимок от ожидания свежее не станет. Поэтому busy handler даже не вызывается.

Исправление: если транзакция будет писать, сразу объявить её пишущей.

a.execute("BEGIN IMMEDIATE")
n = a.execute("SELECT count(*) FROM orders "
              "WHERE user_id = 1 AND status = 'new'").fetchone()[0]
if n < 3:
    a.execute("INSERT INTO orders(user_id, status) VALUES (1, 'new')")
a.execute("COMMIT")

BEGIN IMMEDIATE берёт блокировку на запись в начале. Если её держит кто-то другой, соединение честно ждёт timeout, а не падает. Я проверял на двух потоках: второй поток на своём BEGIN IMMEDIATE подождал 0.52 секунды, пока первый закончит, и спокойно записал. Заодно пропала гонка, при которой два параллельных запроса оба видели «два заказа» и оба создавали третий.

Если пользуетесь неявными транзакциями модуля, можно передать isolation_level="IMMEDIATE" в connect(), тогда модуль сам будет отправлять BEGIN IMMEDIATE вместо обычного BEGIN. Но неявный BEGIN ставится только перед DML, поэтому SELECT в начале «проверь и запиши» всё равно окажется вне транзакции. Я предпочитаю isolation_level=None и явный BEGIN IMMEDIATE: так видно в коде, где транзакция начинается и заканчивается.

Причина 3. Синхронный sqlite3 внутри asyncio

Сам по себе синхронный драйвер блокировку не создаёт, зато делает её в разы больнее. Синхронный execute() ждёт свои 5 секунд в потоке цикла событий. Весь бот в это время стоит: не отвечает другим пользователям, не обрабатывает колбэки, не шлёт ничего.

Проверка:

import asyncio, sqlite3, threading, time

def hold_lock():
    c = sqlite3.connect("shop.db")
    c.execute("INSERT INTO orders(user_id, status) VALUES (7, 'new')")
    time.sleep(3)
    c.commit()

async def ticker():
    for _ in range(6):
        print(time.strftime("%X"), "тик")
        await asyncio.sleep(0.5)

async def handler():
    await asyncio.sleep(0.2)
    conn = sqlite3.connect("shop.db", timeout=5)
    conn.execute("INSERT INTO orders(user_id, status) VALUES (8, 'new')")
    conn.commit()
    print(time.strftime("%X"), "записал")

async def main():
    threading.Thread(target=hold_lock).start()
    await asyncio.gather(ticker(), handler())

asyncio.run(main())

Вывод:

09:47:07 тик
09:47:10 записал
09:47:10 тик
09:47:10 тик
09:47:11 тик
...

Между первым и вторым тиком три секунды вместо половины. В реальном боте эти три секунды получают все пользователи разом, а не только тот, кто нажал кнопку.

Поэтому асинхронный драйвер. aiosqlite гоняет запросы в отдельном потоке и отдаёт управление циклу, пока ждёт. Файл он блокировать не перестанет, но бот хотя бы продолжит отвечать остальным.

Что я ставлю теперь в каждый бот на SQLite

Собрал всё в небольшой класс. Одно соединение на процесс, WAL, busy_timeout, явные пишущие транзакции.

import asyncio
from contextlib import asynccontextmanager

import aiosqlite


class Database:
    def __init__(self, path: str):
        self.path = path
        self.conn: aiosqlite.Connection | None = None
        self._write_lock = asyncio.Lock()

    async def connect(self):
        # isolation_level=None: транзакциями управляем сами
        self.conn = await aiosqlite.connect(self.path, isolation_level=None)
        await self.conn.execute("PRAGMA journal_mode=WAL")
        await self.conn.execute("PRAGMA synchronous=NORMAL")
        await self.conn.execute("PRAGMA busy_timeout=5000")
        await self.conn.execute("PRAGMA foreign_keys=ON")

    async def close(self):
        await self.conn.close()

    @asynccontextmanager
    async def write(self):
        async with self._write_lock:
            await self.conn.execute("BEGIN IMMEDIATE")
            try:
                yield self.conn
            except BaseException:
                await self.conn.execute("ROLLBACK")
                raise
            else:
                await self.conn.execute("COMMIT")

    async def fetchone(self, sql, params=()):
        async with self.conn.execute(sql, params) as cur:
            return await cur.fetchone()

Использование в хендлере:

async def create_order(db: Database, user_id: int) -> bool:
    async with db.write() as conn:
        row = await db.fetchone(
            "SELECT count(*) FROM orders WHERE user_id = ? AND status = 'new'",
            (user_id,),
        )
        if row[0] >= 3:
            return False
        await conn.execute(
            "INSERT INTO orders(user_id, status) VALUES (?, 'new')", (user_id,)
        )
        return True

Прогнал 500 одновременных create_order через asyncio.gather на 10 пользователей. В базе ровно 30 заказов, по три на каждого, ни одной ошибки. Исключение внутри write() откатывает транзакцию, это тоже проверил.

Теперь зачем там каждая строчка.

Про WAL. В режиме журнала предзаписи читатели не мешают писателю и наоборот. Крон с выгрузкой в Excel может читать сколько угодно, бот в это время пишет. Писатель по-прежнему один, для бота это обычно не проблема. Режим сохраняется в файле базы, но PRAGMA в connect() я всё равно оставляю. Рядом с базой появятся файлы -wal и -shm, это нормально. Только копировать для бэкапа один .db, пока бот работает, нельзя: свежие изменения могут лежать в -wal. Для бэкапа есть conn.backup() или VACUUM INTO. И WAL не работает на сетевых файловых системах, на обычном Docker volume всё в порядке.

synchronous=NORMAL в WAL это безопасный компромисс: при падении питания можно потерять последние транзакции, но база не побьётся. Для бота с заказами меня это устраивает. Если для вас не устраивает, уберите строку.

busy_timeout это тот же timeout из connect(), только в миллисекундах и через PRAGMA. Он нужен для других процессов: того самого крона или скрипта миграции, который вы запустили руками на проде.

asyncio.Lock нужен, потому что соединение одно, а корутин много. Без блокировки две корутины попытаются открыть BEGIN IMMEDIATE на одном и том же соединении, и вторая получит cannot start a transaction within a transaction. Я на это наступил, когда собирал пример. Lock выстраивает записи внутри процесса в очередь, а busy_timeout разруливает конкуренцию с другими процессами.

И ещё про одно соединение: чтение вне write() идёт через то же соединение и может увидеть незакоммиченные данные, пока другая корутина в середине своей транзакции. Если для вас это критично, заведите второе соединение только для чтения. В моих ботах такого не требовалось.

Чем закончилось у заказчика

В том проекте я перевёл базу в WAL, переписал запись заказа на BEGIN IMMEDIATE, вынес commit() до отправки сообщений и заменил sqlite3 на aiosqlite. С тех пор database is locked в логах не появлялся. Заказчику в каком-то чате сразу посоветовали переезжать на PostgreSQL. Ради десяти заказов в час я смысла не видел. Если несколько процессов активно пишут в одну базу или серверов больше одного, тогда да, пора.