How to know which process is responsible for a "OperationalError: database is locked"?

Question

I sometimes randomly encounter:

OperationalError: database is locked

in a process that updates a SQLite database, but I find it difficult to reproduce the error:

• no other process is inserting / deleting rows at the same time
• just one process might do some read-only queries (SELECT, etc.) here and there, but no committing

I've already read OperationalError: database is locked

Question: Is there a way, when this error happens, to log which other process ID is responsible for the lock?

More generally, how to debug a OperationalError: database is locked?

Answer 1

Solution: Always close the cursor for (even read-only) queries!

First, here is a way to reproduce the problem:

1. First run this code, once:

   import sqlite3
   conn = sqlite3.connect('anothertest.db')
   conn.execute("CREATE TABLE IF NOT EXISTS mytable (id int, description text)")
   for i in range(100):
       conn.execute("INSERT INTO mytable VALUES(%i, 'hello')" % i)
   conn.commit()
   

   to initialize the test.

2. Then begin a read-only query:

   import sqlite3, time
   conn = sqlite3.connect('anothertest.db')
   c = conn.cursor()
   c.execute('SELECT * FROM mytable')
   item = c.fetchone()
   print(item)
   print('Sleeping 60 seconds but the cursor is not closed...')
   time.sleep(60)
   

   and keep this script running while executing the next step:

3. Then try to delete some content and commit:

   import sqlite3
   conn = sqlite3.connect('anothertest.db')
   conn.execute("DELETE FROM mytable WHERE id > 90")
   conn.commit()
   

   It will trigger this error indeed:

   sqlite3.OperationalError: database is locked

Why? Because it's not possible to delete data that is currently accessed by a read-query: if the cursor it's still open, it means the data could still be fetched with fetchone or fetchall.

Here is how to solve the error: in step #2, just add:

item = c.fetchone()
print(item)
c.close()
time.sleep(60)

Then while this is still running, start script #3, you will see there is no more error.