Fixed; check the second answer for a definitive solution.
So I'm currently working with aiosqlite database within a discord.py bot - a part of the bot's job is to handle data inputs (such invites, bans, warns, etc...) and insrt them into an aiosqlite database; however, I've been experiencing a persistent issue with a specific error being raised; namely: OperationalError: database is locked.
It was completely random at first, sometimes locking up the database in the middle of an operation for seemingly no reason; but after some debugging and rummaging through a 4k line code, I think I might've found the root cause of the error: my bot's warning system.
My bot's warning system is made of multiple commands woven together, all working in unison to properly handle warnings and such; except that when I invoke my warning system, it completely locks up my database for some sort of unintuitive reason (to me, at least)...
I've stripped down my warning system to just the barebones of it, to make it easier to read and debug; so here's my code:
@commands.command()
async def warn(self, ctx, member: discord.Member, *,
reason: str = 'No reason provided.'):
await self.addwarn(ctx, member, reason=reason) # Add a warning
@commands.command()
async def addwarn(self, ctx, member, *, reason):
self.bot.db = await aiosqlite.connect("database.db") # Connect to db
async with self.bot.db.cursor() as cursor:
await cursor.execute("INSERT OR IGNORE INTO Warns "
"(User, Reason, Time, Server) VALUES "
"(?,?,?,?)",
(member.id, reason,
int(datetime.now().timestamp()),
member.guild.id)) # Register the warning's reason, member id, time, and guild id
async with self.bot.db.cursor() as cursor:
await cursor.execute('SELECT Reason, Time FROM Warns WHERE '
'User = ? AND Server = ?',
(member.id, member.guild.id)) # Fetch past warning records from db
data = await cursor.fetchall() # Store past records in a variable
if data:
warnnum = 0 # Warning count
for record in data:
warnnum = 1 # Increment variable for past records in db
if warnnum >= 3: # If user has 3 warnings, ban them
await self.ban_member(ctx, member=member,
reason='User has exceeded their '
'warnings limit, and has been '
'banned as a result.')
if member.joined_at >= datetime.now(timezone.utc) - timedelta(hours=3): # If user has joined within the
# past 3 hours prior to recieving
# a warning, ban them
await self.ban_member(ctx, member=member,
reason='User has gained an '
'infraction within a short duration '
'of joining.')
await self.bot.db.commit() # Commit the changes
@commands.command()
async def ban_member(self, ctx, member: discord.Member, *, reason=None):
await member.ban(reason=reason) # Ban the member
await self.clearwarn(ctx, member) # Clear a member's entire warning record when they're banned
async def clearwarn(self, ctx, member: discord.Member):
self.bot.db = await aiosqlite.connect("database.db")
async with self.bot.db.cursor() as cursor:
await cursor.execute('DELETE FROM Warns WHERE User = ? '
'AND Server = ?',
(member.id, ctx.guild.id)) # Clear a member's entire warning record
await self.bot.db.commit()
I've organized the code for reading and debugging as best I could, so hopefully it's coherent enough.
If anyone could find out exactly what's making this code lock up my aiosqlite database, I'd truly appreciate it if they could help me by supplying any sort of information of how I could solve this.
Thanks in advance :)
CodePudding user response：
SQLite is meant to be a lightweight database, and thus can't support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than SQLite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock to be released.
Python's SQLite wrapper has a default timeout value that determines how long the second thread is allowed to wait on the lock before it times out and raises the OperationalError: database is locked error.
Ways to solve this error
- Switching to another database backend. At a certain point, SQLite becomes too "lite" for real-world applications, and these sorts of concurrency errors indicate you've reached that point.
- Increase the default timeout value by setting the timeout database option
- Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
CodePudding user response：
Okay, I managed to miraculously fix this issue without switching to a new database; here's how:
First thing was that the main issue here was all those self.bot.db = await aiosqlite.connect("database.db") connections lying around within each function; they were the main reason the database was being locked - since each one of them created a brand new connection instance (my call stack sometimes registered straight up 20 threads, excluding the main thread and the asyncio_0 thread; it was hurrendous); so, I fixed it by doing this:
Within main.py:
# At the top of the code:
bot = commands.Bot(...)
bot.db_con = None # A global variable, which assigns
# a connection to the entire bot,
# and is reusable within Cogs
@bot.event
async def on_ready():
if bot.db_con is None:
bot.db_con = bot.db_con = await aiosqlite.connect("database.db")
...
# Rest of code
Within Cogs:
class ClassName(commands.Cog):
def __init__(self, bot) -> None:
super().__init__()
self.bot = bot
self.bot.db_con = bot.db_con
...
# Rest of code
Now, my class stack at most registers 5 threads at once (including the main thread and the asyncio_0 thread); this is because the ENTIRE bot now only uses a single connection.
Hope this helps anyone who stumbles upon this question, and I hope you have a great day :)