Solution: Always close the cursor
for (even read-only) queries!
First, here is a way to reproduce the problem:
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.
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:
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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…