Бот для приёма заказов, aiogram, база SQLite в одном файле. Заказчику нужна была ещё ежедневная выгрузка в Excel, и её я сделал отдельным скриптом по крону, который читает тот же файл. Месяц всё жило спокойно. Потом заказчик прислал скриншот: бот на «Оформить заказ» молчит секунд пять, а потом пишет «Что-то пошло не так». В логах:
sqlite3.OperationalError: database is locked
Ошибка плавающая, раз в два-три дня, а нагрузка там десяток заказов в час. В этом проекте я потом нашёл сразу две причины из трёх, про которые ниже. Третью встречал в чужих ботах, которые приносили на доработку.
Как SQLite вообще блокирует
В режиме по умолчанию (rollback journal) запись в базу эксклюзивная. Пока одно соединение держит открытую пишущую транзакцию, другое не может начать свою. Второе соединение получает SQLITE_BUSY, и модуль sqlite3 превращает его в database is locked.
Перед тем как сдаться, sqlite3 какое-то время ждёт. За это отвечает параметр timeout у connect(), по умолчанию 5 секунд. Те самые пять секунд тишины в боте.
Воспроизводится в несколько строк:
import sqlite3, timedb = 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) # Truet = 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, timedef 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 asynciofrom contextlib import asynccontextmanagerimport aiosqliteclass 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. Ради десяти заказов в час я смысла не видел. Если несколько процессов активно пишут в одну базу или серверов больше одного, тогда да, пора.
ссылка на оригинал статьи https://habr.com/ru/articles/1099860/