Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
373 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...