当我输入这个查询: 删除邮件中id = 71的所有邮件

SQLite返回以下错误:

SQL error: database is locked

我如何解锁数据库,以便这个查询将工作?


当前回答

在选择重新启动选项之前,有必要看看能否找到sqlite数据库的用户。

在Linux上,可以使用fuser:

$ fuser database.db

$ fuser database.db-journal

在我的案例中,我得到了如下的回应:

philip    3556  4700  0 10:24 pts/3    00:00:01 /usr/bin/python manage.py shell

这表明我有另一个使用数据库的pid 3556 (manage.py)的Python程序。

其他回答

我添加了“池=true”连接字符串,它工作。

有些函数,比如INDEX'ing,可能会花费很长时间——而且在运行时它会锁定整个数据库。在这种情况下,它甚至可能不使用日志文件!

因此,最好/唯一的方法来检查您的数据库是否被锁定,因为一个进程正在积极地写入它(因此你应该让它独自呆着,直到它完成它的操作)是md5(或md5sum在某些系统)文件两次。 如果你得到一个不同的校验和,数据库正在被写入,你真的真的真的不想杀死这个进程,因为如果你这样做,你很容易得到一个损坏的表/数据库。

我要重申一下,因为这很重要——解决方案不是找到锁程序并杀死它——而是找出数据库是否有一个很好的写锁,然后从那里开始。有时候正确的解决方法就是喝杯咖啡休息一下。

The only way to create this locked-but-not-being-written-to situation is if your program runs BEGIN EXCLUSIVE, because it wanted to do some table alterations or something, then for whatever reason never sends an END afterwards, and the process never terminates. All three conditions being met is highly unlikely in any properly-written code, and as such 99 times out of 100 when someone wants to kill -9 their locking process, the locking process is actually locking your database for a good reason. Programmers don't typically add the BEGIN EXCLUSIVE condition unless they really need to, because it prevents concurrency and increases user complaints. SQLite itself only adds it when it really needs to (like when indexing).

Finally, the 'locked' status does not exist INSIDE the file as several answers have stated - it resides in the Operating System's kernel. The process which ran BEGIN EXCLUSIVE has requested from the OS a lock be placed on the file. Even if your exclusive process has crashed, your OS will be able to figure out if it should maintain the file lock or not!! It is not possible to end up with a database which is locked but no process is actively locking it!! When it comes to seeing which process is locking the file, it's typically better to use lsof rather than fuser (this is a good demonstration of why: https://unix.stackexchange.com/questions/94316/fuser-vs-lsof-to-check-files-in-use). Alternatively if you have DTrace (OSX) you can use iosnoop on the file.

如果文件位于远程文件夹(如共享文件夹)中,则可能引发此错误。我将数据库更改为本地目录,它工作得很好。

我的锁是由系统崩溃引起的,而不是由挂起进程引起的。为了解决这个问题,我简单地重命名了文件,然后将其复制回原来的名称和位置。

使用Linux shell将是:

mv mydata.db temp.db
cp temp.db mydata.db

在windows中,您可以尝试这个程序http://www.nirsoft.net/utils/opened_files_view.html,以找出进程正在处理db文件。尝试关闭该程序解锁数据库

在Linux和macOS中,你可以做类似的事情,例如,如果你锁定的文件是development.db:

$ fuser development.db

这个命令将显示哪个进程正在锁定该文件:

> development.db: 5430

只需终止进程……

杀死-9 5430

...您的数据库将被解锁。