Python 操作错误：数据库被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26862809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
operational error: database is locked
提问by Ishq Mehta
So I know this problem is not new in flask, and people have already asked it before. However I am still facing a problem while executing my database commands in bash as I am new to python. This is what i did
所以我知道这个问题在flask中并不是新问题，之前已经有人问过了。但是，由于我是 Python 新手，因此在 bash 中执行我的数据库命令时仍然遇到问题。这就是我所做的
import sqlite3
conn = sqlite.connect('/home/pjbardolia/mysite/tweet_count.db')
c = conn.cursor()
c.execute("create table count_twitter (count_id integer primary key autoincrement ,count_present integer not null,last_tweet not null)")
c.execute(insert into count_twitter values('',10,10))
however after executing insert statement I am getting operational error: database is locked. Can someone tellme in simple terms what does this error means? and how to solve it. Thanks in advance
但是在执行插入语句后，我收到操作错误：数据库被锁定。有人可以简单地告诉我这个错误是什么意思吗？以及如何解决。提前致谢
采纳答案by Nima Soroush
This is what this error means:
这是这个错误的意思：
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 the 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.
If you're getting this error, you can solve it by:
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.
Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
Increase the default timeout value by setting the timeout database option.
SQLite 是一个轻量级数据库，因此不能支持高并发。OperationalError: database is locked 错误表明您的应用程序遇到的并发性比默认配置中的 sqlite 可以处理的要多。这个错误意味着一个线程或进程在数据库连接上有一个排他锁，另一个线程超时等待锁被释放。
Python 的 SQLite 包装器有一个默认超时值，用于确定第二个线程在超时并引发 OperationalError: database is locked 错误之前允许在锁定上等待多长时间。
如果您收到此错误，可以通过以下方式解决：
切换到另一个数据库后端。在某个时候，SQLite 对于实际应用程序来说变得过于“精简”，而这些并发错误表明您已经达到了这一点。
重写代码以减少并发并确保数据库事务是短暂的。
通过设置超时数据库选项增加默认超时值。
Probably you have another connection in your code that is not closed or not committed and this cause this error. Basically trying to do second executewhen it is already locked by the another one. If you really want to have your concurrent transactions you need to have a RDBMS.
可能您的代码中有另一个未关闭或未提交的连接，这会导致此错误。基本上execute是在它已经被另一个锁定时尝试做第二个。如果您真的想拥有并发事务，则需要有一个RDBMS。
回答by Mohamed Ibrahim
make sure you commit the other connections by using con.commit()
确保使用 con.commit() 提交其他连接
回答by John Phelps
I had the same issue and found killing all Python processes solved the problem.
我遇到了同样的问题，发现杀死所有 Python 进程解决了这个问题。
回答by Terry Brown
Here's what I used to manage concurrency. I was hitting one DB from 270 processes. Just increasing SQLite's timeout didn't help, but this approach where you just wait without attempting to connect for a while seemed to work. The number of attempts (50) and wait period (10-30 seconds) could be adjusted. I was collecting results from long running analyses, so 10-30 seconds was fine, but maybe 1-3 would have worked.
这是我用来管理并发的方法。我从 270 个进程中击中了一个 DB。只是增加 SQLite 的超时并没有帮助，但是这种您只是等待而不尝试连接一段时间的方法似乎有效。可以调整尝试次数 (50) 和等待时间 (10-30 秒)。我从长时间运行的分析中收集结果，所以 10-30 秒没问题，但也许 1-3 秒会起作用。
import random
import sqlite3
def do_query(path, q, args=None, commit=False):
"""
do_query - Run a SQLite query, waiting for DB in necessary
Args:
path (str): path to DB file
q (str): SQL query
args (list): values for `?` placeholders in q
commit (bool): whether or not to commit after running query
Returns:
list of lists: fetchall() for the query
"""
if args is None:
args = []
for attempt in range(50):
try:
con = sqlite3.connect(path)
cur = con.cursor()
cur.execute(q, args)
ans = cur.fetchall()
if commit:
con.commit()
cur.close()
con.close()
del cur
del con
return ans
except sqlite3.OperationalError:
time.sleep(random.randint(10, 30))
回答by Pavan Varyani
I had the same issue when I was using threads in my flask app, I tried almost everything (Checking if the Syntax of ORM command is right etc.), but all of these tries didn't solve my issue.
当我在我的烧瓶应用程序中使用线程时，我遇到了同样的问题，我尝试了几乎所有的方法（检查 ORM 命令的语法是否正确等），但所有这些尝试都没有解决我的问题。
The last thing I did was to change the database from SQLite3 to PostgreSQL, now everything works fine.
我做的最后一件事是将数据库从 SQLite3 更改为 PostgreSQL，现在一切正常。
