A developer encountered the database is locked error in a Telegram bot using SQLite, which caused five-second delays during order processing. The issue stemmed from exclusive write transactions in the default rollback journal mode and improper transaction handling within the aiogram framework.
The root causes included long-running transactions that remained open while the bot performed network operations, and race conditions occurring when multiple connections attempted to read and write simultaneously. Using BEGIN IMMEDIATE instead of the default BEGIN allows the database to queue write requests correctly, preventing immediate failures.
To resolve these issues, the developer migrated the database to Write-Ahead Logging (WAL) mode, which allows concurrent reading and writing. Additionally, replacing the synchronous sqlite3 driver with the asynchronous aiosqlite library ensures the bot remains responsive to other users while waiting for database operations to complete.
The final implementation uses a custom Database class with an asyncio.Lock to manage concurrent write requests within the process. This configuration successfully handled 500 simultaneous requests without errors, proving that SQLite is sufficient for low-traffic applications without needing to migrate to PostgreSQL.
