How do I unlock a SQLite database

Question

sqlite gt  DELETE FROM mails WHERE   id    71   SQL error  database is locked   How do I unlock the database so this will work

User · Answer

I added  Pooling true  to connection string and it worked

User · Answer

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  my python code                      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

User · Answer

The DatabaseIsLocked page listed below is no longer available  The File Locking And Concurrency page describes changes related to file locking introduced in v3 and may be useful for future readers  https   www sqlite org lockingv3 html  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

User · Answer

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

User · Answer

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

User · Answer

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  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

User · Answer

The DatabaseIsLocked page listed below is no longer available  The File Locking And Concurrency page describes changes related to file locking introduced in v3 and may be useful for future readers  https   www sqlite org lockingv3 html  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

User · Answer

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   sqlite reset xxx     or  sqlite finalize xxx

User · Answer

For some reason the database got locked  Here is how I fixed it    I downloaded the sqlite file to my system  FTP  Deleted the online sqlite file Uploaded the file back to the hosting provider   It works fine now

User · Answer

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

User · Answer

If you want to remove a  database is locked  error then follow these steps    Copy your database file to some other location   Replace the database with the copied database  This will dereference all processes which were accessing your database file

User · Answer

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  quot important data quot   This file is perhaps familiar to them  But after the crash  there is also a hot journal named  quot important data-journal quot   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

User · Answer

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

User · Answer

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   Taken from  http   random kakaopor hu how-to-repair-an-sqlite-database

User · Answer

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

User · Answer

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 and macOS 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

User · Answer

An old question  with a lot of answers  here s the steps I ve recently followed reading the answers above  but in my case the problem was due to cifs resource sharing  This case is not reported previously  so hope it helps someone    Check no connections are left open in your java code   Check no other processes are using your SQLite db file with lsof   Check the user owner of your running jvm process has r w permissions over the file  Try to force the lock mode on the connection opening with  final SQLiteConfig config   new SQLiteConfig     config setReadOnly false    config setLockingMode LockingMode NORMAL    connection   DriverManager getConnection url  config toProperties        If your using your SQLite db file over a NFS shared folder  check this point of the SQLite faq  and review your mounting configuration options to make sure your avoiding locks  as described here     myserver  mymount cifs username       password       iocharset utf8 sec ntlm file nolock file mode 0700 dir mode 0700 uid 0500 gid 0500 0 0

User · Answer

I got this error in a scenario a little different from the ones describe here   The SQLite database rested on a NFS filesystem shared by 3 servers  On 2 of the servers I was able do run queries on the database successfully  on the third one thought I was getting the  database is locked  message   The thing with this 3rd machine was that it had no space left on  var  Everytime I tried to run a query in ANY SQLite database located in this filesystem I got the  database is locked  message and also this error over the logs      Aug 8 10 33 38 server01 kernel  lockd  cannot monitor 172 22 84 87   And this one also      Aug  8 10 33 38 server01 rpc statd 7430   Failed to insert  writing  var lib nfs statd sm other server name com  No space left on device   Aug  8 10 33 38 server01 rpc statd 7430   STAT FAIL to server01 for SM MON of 172 22 84 87   After the space situation was handled everything got back to normal

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I was having  database is locked  errors in a multi-threaded application as well  which appears to be the SQLITE BUSY result code  and I solved it with setting sqlite3 busy timeout to something suitably long like 30000    On a side-note  how odd that on a 7 year old question nobody found this out already  SQLite really is a peculiar and amazing project

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In my case  I also got this error   I already checked for other processes that might be the cause of locked database such as  SQLite Manager  other programs that connects to my database   But there s no other program that connects to it  it s just another active SQLConnection in the same application that stays connected      Try checking your previous active SQLConnection that might be still connected  disconnect it first  before you establish a new SQLConnection and new command

User · Answer

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

User · Answer

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

User · Answer

Before going down the reboot option  it is worthwhile to see if you can find the user of the sqlite database   On Linux  one can employ fuser to this end     fuser database db    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

User · Answer

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    Provide to export your tables  You can use  SQLite manager  on Firefox  If the migration alter your database scheme delete the last failed migration Rename your  database sqlite  file Execute  rake db migrate  to make a new working database Provide to give the right permissions to database for table s importing Import your backed up tables Write the new migration Execute it with  rake db migrate

User · Answer

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

User · Answer

If you re trying to unlock the Chrome database to view it with SQLite  then just shut down Chrome   Windows   userprofile  Local Settings Application Data Google Chrome User Data Default Web Data  or   userprofile  Local Settings Application Data Google Chrome User Data Default Chrome Web Data   Mac    Library Application Support Google Chrome Default Web Data

User · Answer

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

User · Answer

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

User · Answer

This is because some other query is running on that database  SQLite is a database where query execute synchronously  So if some one else is using that database then if you perform a query or transaction it will give this error   So stop that process which is using the particular database and then execute your query

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sqlite] How do I unlock a SQLite database?

Examples related to sqlite