How do I unlock a SQLite database?

The SQLite wiki DatabaseIsLocked page offers an explanation of this error message. It states, in part, that the source of contention is internal (to the process emitting the error). What this page doesn't explain is how SQLite decides that something in your process holds a lock and what conditions could lead to a false positive.

This error code occurs when you try to do two incompatible things with a database at the same time from the same database connection.

Changes related to file locking introduced in v3 and may be useful for future readers and can be found here: File Locking And Concurrency In SQLite Version 3

Deleting the -journal file sounds like a terrible idea. It's there to allow sqlite to roll back the database to a consistent state after a crash. If you delete it while the database is in an inconsistent state, then you're left with a corrupted database. Citing a page from the sqlite site:

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.

I can't explain why deleting a -journal file would let you lock a database that you couldn't before. Is that reproducible?

By the way, the presence of a -journal file doesn't necessarily mean that there was a crash or that there are changes to be rolled back. Sqlite has a few different journal modes, and in PERSIST or TRUNCATE modes it leaves the -journal file in place always, and changes the contents to indicate whether or not there are partial transactions to roll back.

If a process has a lock on an SQLite DB and crashes, the DB stays locked permanently. That's the problem. It's not that some other process has a lock.

My lock was caused by the system crashing and not by a hanging process. To resolve this, I simply renamed the file then copied it back to its original name and location.

Using a Linux shell that would be:

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

the SQLite db files are just files, so the first step would be to make sure it isn't read-only. The other thing to do is to make sure that you don't have some sort of GUI SQLite DB viewer with the DB open. You could have the DB open in another shell, or your code may have the DB open. Typically you would see this if a different thread, or application such as SQLite Database Browser has the DB open for writing.

I found the documentation of the various states of locking in SQLite to be very helpful. Michael, if you can perform reads but can't perform writes to the database, that means that a process has gotten a RESERVED lock on your database but hasn't executed the write yet. If you're using SQLite3, there's a new lock called PENDING where no more processes are allowed to connect but existing connections can sill perform reads, so if this is the issue you should look at that instead.

I just had something similar happen to me - my web application was able to read from the database, but could not perform any inserts or updates. A reboot of Apache solved the issue at least temporarily.

It'd be nice, however, to be able to track down the root cause.

I ran into this same problem on Mac OS X 10.5.7 running Python scripts from a terminal session. Even though I had stopped the scripts and the terminal window was sitting at the command prompt, it would give this error the next time it ran. The solution was to close the terminal window and then open it up again. Doesn't make sense to me, but it worked.

In my experience, this error is caused by: You opened multiple connections.

e.g.:

1. 1 or more sqlitebrowser (GUI)
2. 1 or more electron thread
3. rails thread

I am nore sure about the details of SQLITE3 how to handle the multiple thread/request, but when I close the sqlitebrowser and electron thread, then rails is running well and won't block any more.

From your previous comments you said a -journal file was present.

This could mean that you have opened and (EXCLUSIVE?) transaction and have not yet committed the data. Did your program or some other process leave the -journal behind??

Restarting the sqlite process will look at the journal file and clean up any uncommitted actions and remove the -journal file.

As Seun Osewa has said, sometimes a zombie process will sit in the terminal with a lock aquired, even if you don't think it possible. Your script runs, crashes, and you go back to the prompt, but there's a zombie process spawned somewhere by a library call, and that process has the lock.

Closing the terminal you were in (on OSX) might work. Rebooting will work. You could look for "python" processes (for example) that are not doing anything, and kill them.

I was receiving sqlite locks as well in a C# .NET 4.6.1 app I built when it was trying to write data, but not when running the app in Visual Studio on my dev machine. Instead it was only happening when the app was installed and running on a remote Windows 10 machine.

Initially I thought it was file system permissions, however it turns out that the System.Data.SQLite package drivers (v1.0.109.2) I installed in the project using Nuget were causing the problem. I removed the NuGet package, and manually referenced an older version of the drivers in the project, and once the app was reinstalled on the remote machine the locking issues magically disappeared. Can only think there was a bug with the latest drivers or the Nuget package.

I had the tool "DB Browser for SQLite" running and was also working in there. Obviously that tool also puts locks on things. After clicking on "Write Changes" or "Revert Changes", the lock was gone and the other process (A React-Native script) did not give that error anymore.

I encountered this error while looking at stored passwords in Google Chrome.

# ~/.config/google-chrome/Default
$ sqlite3 Login\ Data
SQLite version 3.35.5 2021-04-19 18:32:05
sqlite> .tables
Error: database is locked

If you don't particularly care about the parent process or if you don't want to stop the current chrome process which is using the database, simply copy the file somewhere else.

$ cp Login\ Data ~/tmp/ld.sql
$ sqlite3 ~/tmp/ld.sql .tables
field_info              meta   sync_model_metadata   
insecure_credentials    stats                 
logins                  sync_entities_metadata

Doing so will allow you to read the contents of the database without disturbing or stopping the main chrome process.