How do I unlock an SQLite database?

Question

When I enter sqlite> DELETE FROM mails WHERE ('id' = 71); SQLite returns:

SQL error: database is locked

How do I unlock the database so this query will work?

Answer 1

In my case, I am using db browser for sqlite app. When I closed that app and ran the process, it is working fine, otherwise it is throwing DatabaseLocked Error Continuously, even when no other process was using it.

Answer 2

Functions like INDEXing can take a long time and lock the database while it runs. It might not even use the journal file. To check if a process is actively writing to it (and should be left alone) md5 (or md5sum) the file twice. If you get a different checksum don't kill the process because you can end up with a corrupt database. The solution is to find if the database has a write lock for a good reason. Sometimes the correct solution is a coffee break.

The only way to create this "locked but not being written to" situation is when your program runs BEGIN EXCLUSIVE (to do table alterations or something) then never sends END afterwards and the process never terminates. All three conditions being met is unlikely in properly-written code. 99 times out of 100 the locking is for a good reason. Programmers don't typically add BEGIN EXCLUSIVE unless really needed (it prevents concurrency and increases user complaints). SQLite itself only adds it when it really needs to (like when indexing).

Locked status does not exist inside the file, it resides in the operating system's kernel. The process which ran BEGIN EXCLUSIVE requested the OS to lock the file. Even if your exclusive process crashed, your OS will figure out if it should maintain the file lock or not. It is not possible to end up with a database which is locked when no process is actively locking it.

To see which process is locking the file use lsof rather than fuser (this is why). If you have DTrace (OS X) you can use iosnoop.

Answer 3

In Windows OpenedFilesView can find the process handling the database file. Close that program to unlock the database.

In Linux and macOS you can do something similar:

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

Answer 4

In my case the problem was due to CIFS resource sharing.

• Check no connections are left open in your Java code.

• Check no other processes are using your SQLite database file with lsof.

• Check the owner of your running JVM process has r/w permissions over the file.

• Try to force the lock mode on connection opening:

  final SQLiteConfig config = new SQLiteConfig();
  config.setReadOnly(false);
  config.setLockingMode(LockingMode.NORMAL);
  connection = DriverManager.getConnection(url, config.toProperties());
  

If you're using your SQLite database file over a NFS shared folder check the SQLite FAQ and review your mounting configuration to make sure you're avoiding locks:

//myserver /mymount cifs username=*****,password=*****,iocharset=utf8,sec=ntlm,file,nolock,file_mode=0700,dir_mode=0700,uid=0500,gid=0500 0 0

Answer 5

If you are coming here like me but your problem is totally different, here is my solution:

• In my case, db is coming from my NAS through LAN but mounted to local system from samba in the remote system.
• I had to add nobrl at the end of my mount snippet so that i can mount without locking.

sudo mount -t \
  cifs //IP_LAN/MOUNT_POINT_IN_SAMBA \
  /mnt/MY_MOUNT_DIR -o \
username=username,password=pass,iocharset=utf8,file_mode=0777,dir_mode=0777,nobrl

hope it helps someone.

Wrote it in more details in my blog: https://hakanu.net/python/2023/04/15/sqlite3-over-network-shared-drive-and-database-getting-locked-error/

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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.

Answer 10

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.

Answer 11

For some reason the database got locked. Here is how I fixed it.

1. I downloaded the sqlite file to my system (FTP)
2. Deleted the online sqlite file
3. Uploaded the file back to the hosting provider

It works fine now.

Answer 12

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.

Answer 13

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

Answer 14

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.

Answer 15

I added "Pooling=true" to connection string and it worked.

Answer 16

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"

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

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

Answer 21

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

Answer 22

If you want to remove a "database is locked" error then follow these steps:

1. Copy your database file to some other location.
2. Replace the database with the copied database. This will dereference all processes which were accessing your database file.

Answer 23

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.

Answer 24

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.

Answer 25

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.

Answer 26

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

Answer 27

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 )

Answer 28

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

Answer 29

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

Answer 30

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.