如何知道哪个进程负责“ OperationalError:数据库已锁定”? [英] How to know which process is responsible for a "OperationalError: database is locked"?
问题描述
我有时会随机遇到:
OperationalError:数据库已锁定
OperationalError: database is locked
在更新SQLite数据库的过程中,但是我发现很难重现该错误:
in a process that updates a SQLite database, but I find it difficult to reproduce the error:
- 否另一个过程是同时插入/删除行
- 仅一个过程可能会执行一些只读查询(
SELECT
等)。 ),但没有提交
- 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
我已经读过操作错误:数据库已锁定
问题:有没有办法解决此错误,以记录哪个其他进程ID负责锁定?
更一般地说,如何调试 OperationalError:数据库已锁定
?
More generally, how to debug a OperationalError: database is locked
?
推荐答案
解决方案:始终关闭光标
for(即使是只读的y)查询!
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()
初始化测试。
然后开始只读查询:
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)
并保持此脚本运行执行下一步时:
然后尝试删除一些合作伙伴注意并提交:
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()
确实会触发此错误:
sqlite3.OperationalError:数据库已锁定
sqlite3.OperationalError: database is locked
为什么?因为无法删除当前由读取查询访问的数据:如果游标仍处于打开状态,则意味着仍可以使用<$ c来获取数据$ c> fetchone 或 fetchall
。
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
.
以下是解决错误的方法:在步骤2中,只需添加:
Here is how to solve the error: in step #2, just add:
item = c.fetchone()
print(item)
c.close()
time.sleep(60)
然后这仍然是运行,启动脚本3,您将看到没有更多错误。
Then while this is still running, start script #3, you will see there is no more error.
这篇关于如何知道哪个进程负责“ OperationalError:数据库已锁定”?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!