sqlite> DELETE FROM mails WHERE (`id` = 71); SQL error: database is locked
How do I unlock the database so this will work?
sqlite> DELETE FROM mails WHERE (`id` = 71); SQL error: database is locked
How do I unlock the database so this will work?
In windows you can try this program http://www.nirsoft.net/utils/opened_files_view.html to find out the process is handling db file. Try closed that program for unlock database
In Linux you can do something similar, for example, if your locked file is development.db:
$ fuser development.db
This command will show what process is locking the file:
> development.db: 5430
Just kill the process...
kill -9 5430
...And your database will be unlocked.
I caused my sqlite db to become locked by crashing an app during a write. Here is how i fixed it:
echo ".dump" | sqlite old.db | sqlite new.db
The SQLite wiki DatabaseIsLocked page offers a good 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.
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.
I had this problem just now, using an SQLite database on a remote server, stored on an NFS mount. SQLite was unable to obtain a lock after the remote shell session I used had crashed while the database was open.
The recipes for recovery suggested above did not work for me (including the idea to first move and then copy the database back). But after copying it to a non-NFS system, the database became usable and not data appears to have been lost.
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.
If you want to remove a "database is locked" error then follow these steps:
Usually that means something else has a connection open to it.
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.
This error can be thrown if the file is in a remote folder, like a shared folder. I changed the database to a local directory and it worked perfectly.
Some functions, like INDEX'ing, can take a very long time - and it locks the whole database while it runs. In instances like that, it might not even use the journal file!
So the best/only way to check if your database is locked because a process is ACTIVELY writing to it (and thus you should leave it the hell alone until its completed its operation) is to md5 (or md5sum on some systems) the file twice. If you get a different checksum, the database is being written, and you really really REALLY don't want to kill -9 that process because you can easily end up with a corrupt table/database if you do.
I'll reiterate, because it's important - the solution is NOT to find the locking program and kill it - it's to find if the database has a write lock for a good reason, and go from there. Sometimes the correct solution is just a coffee break.
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: http://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.
There might be another process accessing the database file - did you check lsof?
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.
Should be a database's internal problem...
For me it has been manifested after trying to browse database with "sqlite manager"...
So, if you can't find another process connect to database and you just can't fix it, just try this radical solution:
1) Provide to export your tables (You can use "sqlite manager" on Firefox)
2) If the migration alter your database scheme delete the last failed migration
3) Rename your "database.sqlite" file
4) Execute "rake db:migrate" to make a new working database
5) Provide to give the right permissions to database for table's importing
6) Import your backed up tables
7) Write the new migration
8) Execute it with "rake db:migrate"
This might be a solution if you are unable to find a way to close other connections to the database.
lsof command on my Linux environment helped me to figure it out that a process was hanging keeping the file open.
Killed the process and problem was solved.
I have such problem within the app, which access to SQLite from 2 connections - one was read-only and second for writing and reading. It looks like that read-only connection blocked writing from second connection. Finally, it is turns out that it is required to finalize or, at least, reset prepared statements IMMEDIATELY after use. Until prepared statement is opened, it caused to database was blocked for writing.
DON'T FORGET CALL:
This link solve the problem. : When Sqlite gives : Database locked error It solved my problem may be useful to you.
And you can use begin transaction and end transaction to not make database locked in future.
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.
I just had the same error. After 5 minets google-ing I found that I didun't closed one shell witch were using the db. Just close it and try again ;)
I had the same problem. Apparently the rollback function seems to overwrite the db file with the journal which is the same as the db file but without the most recent change. I've implemented this in my code below and it's been working fine since then, whereas before my code would just get stuck in the loop as the database stayed locked.
Hope this helps
############## #### Defs #### ############## def conn_exec( connection , cursor , cmd_str ): done = False try_count = 0.0 while not done: try: cursor.execute( cmd_str ) done = True except sqlite.IntegrityError: # Ignore this error because it means the item already exists in the database done = True except Exception, error: if try_count%60.0 == 0.0: # print error every minute print "\t" , "Error executing command" , cmd_str print "Message:" , error if try_count%120.0 == 0.0: # if waited for 2 miutes, roll back print "Forcing Unlock" connection.rollback() time.sleep(0.05) try_count += 0.05 def conn_comit( connection ): done = False try_count = 0.0 while not done: try: connection.commit() done = True except sqlite.IntegrityError: # Ignore this error because it means the item already exists in the database done = True except Exception, error: if try_count%60.0 == 0.0: # print error every minute print "\t" , "Error executing command" , cmd_str print "Message:" , error if try_count%120.0 == 0.0: # if waited for 2 miutes, roll back print "Forcing Unlock" connection.rollback() time.sleep(0.05) try_count += 0.05 ################## #### Run Code #### ################## connection = sqlite.connect( db_path ) cursor = connection.cursor() # Create tables if database does not exist conn_exec( connection , cursor , '''CREATE TABLE IF NOT EXISTS fix (path TEXT PRIMARY KEY);''') conn_exec( connection , cursor , '''CREATE TABLE IF NOT EXISTS tx (path TEXT PRIMARY KEY);''') conn_exec( connection , cursor , '''CREATE TABLE IF NOT EXISTS completed (fix DATE, tx DATE);''') conn_comit( connection )
One common reason for getting this exception is when you are trying to do a write operation while still holding resources for a read operation. For example, if you SELECT from a table, and then try to UPDATE something you've selected without closing your ResultSet first.
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.
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 it's original name and location.
Using a linux shell that would be...
mv mydata.db temp.db cp temp.db mydata.db
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.
Before going down the reboot option, it is worthwhile to see if you can find the user of the sqlite database.
e.g. on Linux use fuser prompt> fuser database.db or prompt> fuser database.db-journal
In my case I got the following response: philip 3556 4700 0 10:24 pts/3 00:00:01 /usr/bin/python manage.py shell
Which showed that I had another python program with pid 3556 (manage.py) using the database
you can try this:
.timeout 100 to set timeout . I don't know what happen in command line but in C# .Net when I do this:
"UPDATE table-name SET column-name = value;" I get Database is locked but this
"UPDATE table-name SET column-name = value" it goes fine.
It looks like when you add ;, sqlite'll look for further command.
I got this error when using Delphi with the LiteDAC components. Turned out it only happened while running my app from the Delphi IDE if the Connected property was set True for the SQLite connection component (in this case TLiteConnection).
I also got this error in window, to resolve this error just go to task manager and kill task of ruby interpreter.