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

SQLite返回以下错误:

SQL error: database is locked

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


当前回答

根据我的经验,此错误是由以下原因引起的:您打开了多个连接。

例如:

1个或多个sqlitebrowser (GUI) 一个或多个电子线 rails线程

我不确定SQLITE3如何处理多线程/请求的细节,但当我关闭sqlitebrowser和电子线程时,rails运行良好,不会再阻塞了。

其他回答

我发现SQLite中锁的各种状态的文档非常有用。Michael,如果您可以执行读操作,但不能对数据库执行写操作,这意味着某个进程已经获得了数据库上的保留锁,但还没有执行写操作。如果你正在使用SQLite3,有一个叫做PENDING的新锁,不允许更多的进程连接,但现有的连接仍然可以执行读取,所以如果这是问题所在,你应该看看这个锁。

删除-journal文件听起来是个糟糕的主意。它允许sqlite在崩溃后将数据库回滚到一致的状态。如果在数据库处于不一致状态时删除它,则会留下一个损坏的数据库。引用sqlite站点的一个页面:

If a crash or power loss does occur and a hot journal is left on the disk, it is essential that the original database file and the hot journal remain on disk with their original names until the database file is opened by another SQLite process and rolled back. [...] We suspect that a common failure mode for SQLite recovery happens like this: A power failure occurs. After power is restored, a well-meaning user or system administrator begins looking around on the disk for damage. They see their database file named "important.data". This file is perhaps familiar to them. But after the crash, there is also a hot journal named "important.data-journal". The user then deletes the hot journal, thinking that they are helping to cleanup the system. We know of no way to prevent this other than user education.

The rollback is supposed to happen automatically the next time the database is opened, but it will fail if the process can't lock the database. As others have said, one possible reason for this is that another process currently has it open. Another possibility is a stale NFS lock, if the database is on an NFS volume. In that case, a workaround is to replace the database file with a fresh copy that isn't locked on the NFS server (mv database.db original.db; cp original.db database.db). Note that the sqlite FAQ recommends caution regarding concurrent access to databases on NFS volumes, because of buggy implementations of NFS file locking.

我无法解释为什么删除一个-journal文件会让你锁定一个数据库,而你以前不能。这是可复制的吗?

顺便说一下,-journal文件的存在并不一定意味着发生了崩溃或有要回滚的更改。Sqlite有几种不同的日志模式,在PERSIST或TRUNCATE模式下,它始终保留-journal文件,并更改内容以指示是否有要回滚的部分事务。

应该是数据库的内部问题… 对我来说,这是在尝试用“SQLite管理器”浏览数据库后表现出来的… 所以,如果你找不到另一个连接到数据库的进程,你也无法修复它, 试试这个激进的解决方案:

提供导出您的表(您可以在Firefox上使用“SQLite管理器”) 如果迁移改变了数据库方案,请删除上次失败的迁移 重命名“数据库”。sqlite”文件 执行“rake db:migrate”创建一个新的工作数据库 提供给正确的权限数据库表的导入 导入备份的表 编写新的迁移 执行"rake db:migrate"

有些函数,比如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.

我有一个工具“DB Browser for SQLite”正在运行,也在里面工作。显然,这个工具也会锁上东西。 在点击“写入更改”或“恢复更改”后,锁消失了,另一个进程(一个React-Native脚本)不再给出这个错误。