Sqlite ошибка database is locked - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

When I enter this query:
sqlite> DELETE FROM mails WHERE (id = 71);

SQLite returns this error:

SQL error: database is locked

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

Dave Mackey

4,26621 gold badges78 silver badges135 bronze badges

asked Sep 29, 2008 at 22:35

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.

guruz

1,60414 silver badges21 bronze badges

answered Aug 13, 2010 at 22:37

nonenononeno

3,2741 gold badge15 silver badges5 bronze badges

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

answered Oct 20, 2008 at 14:24

converter42converter42

7,3702 gold badges29 silver badges24 bronze badges

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.

animuson♦

53.6k28 gold badges137 silver badges147 bronze badges

answered Mar 7, 2013 at 12:28

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.

answered Jun 22, 2009 at 14:25

AaronAaron

6085 silver badges8 bronze badges

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.

answered Sep 29, 2008 at 22:39

Heat MiserHeat Miser

19.4k7 gold badges34 silver badges38 bronze badges

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

TarHalda

1,0321 gold badge9 silver badges27 bronze badges

answered Aug 4, 2009 at 11:02

Ben LBen L

6,5788 gold badges39 silver badges34 bronze badges

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.

answered Jan 29, 2009 at 22:58

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.

answered Jun 16, 2011 at 3:19

jogojapanjogojapan

68k11 gold badges99 silver badges131 bronze badges

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

shA.t

16.5k5 gold badges54 silver badges111 bronze badges

answered Mar 8, 2016 at 9:49

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.

answered Sep 12, 2012 at 20:29

ZequezZequez

3,3892 gold badges30 silver badges42 bronze badges

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.

answered Feb 19, 2015 at 16:05

J.JJ.J

3,4091 gold badge29 silver badges35 bronze badges

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.

answered Nov 14, 2008 at 18:44

Kyle CroninKyle Cronin

77.4k43 gold badges147 silver badges163 bronze badges

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

sqlite_finalize(xxx);

answered Feb 17, 2012 at 18:39

Mike KeskinovMike Keskinov

11.5k6 gold badges57 silver badges87 bronze badges

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.

answered Nov 14, 2008 at 18:37

Michael CoxMichael Cox

1,28113 silver badges15 bronze badges

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.

answered Sep 16, 2011 at 20:29

PinkSheepPinkSheep

4152 gold badges4 silver badges12 bronze badges

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.

answered May 16, 2013 at 5:50

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«

shA.t

16.5k5 gold badges54 silver badges111 bronze badges

answered Apr 3, 2011 at 11:49

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

e.g.:

1 or more sqlitebrowser (GUI)
1 or more electron thread
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.

answered Apr 18, 2021 at 9:19

SiweiSiwei

19.4k6 gold badges74 silver badges94 bronze badges

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.

answered Jun 12, 2009 at 20:20

Shawn SwanerShawn Swaner

10.7k1 gold badge21 silver badges14 bronze badges

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

answered Dec 15, 2011 at 9:11

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 )

answered Feb 17, 2012 at 11:56

JayJay

3,3536 gold badges38 silver badges54 bronze badges

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.

answered Oct 24, 2012 at 17:33

MartinMartin

7,6759 gold badges48 silver badges82 bronze badges

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

answered Mar 2, 2016 at 22:38

Stijn SandersStijn Sanders

35.9k11 gold badges45 silver badges67 bronze badges

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.

answered Jun 21, 2010 at 10:45

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

shA.t

16.5k5 gold badges54 silver badges111 bronze badges

answered Apr 20, 2016 at 10:31

kothvandirkothvandir

2,0912 gold badges19 silver badges34 bronze badges

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.

shA.t

16.5k5 gold badges54 silver badges111 bronze badges

answered Aug 9, 2016 at 3:21

ederribeiroederribeiro

3881 gold badge3 silver badges15 bronze badges

If you’re trying to unlock the Chrome database to view it with SQLite, then just shut down Chrome.

Windows

%userprofile%Local SettingsApplication DataGoogleChromeUser DataDefaultWeb Data

or

%userprofile%Local SettingsApplication DataGoogleChromeUser DataDefaultChrome Web Data

Mac

~/Library/Application Support/Google/Chrome/Default/Web Data

answered Feb 25, 2018 at 20:51

Bob SteinBob Stein

16k10 gold badges85 silver badges100 bronze badges

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.

answered Dec 29, 2008 at 1:44

daivd camerondaivd cameron

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.

answered Jan 7, 2010 at 5:51

wistywisty

6,9751 gold badge30 silver badges29 bronze badges

Источник

This blog summarized on how to fix SQLite Error Database is Locked including Error Code 5 with helps of either manual method or automated solution.

It is quite often, if you are using SQLite then you face some issues. Sometimes while doing the transaction, you might come through an error in which the database gets locked and the following message appears:

“error code 5: Database is locked”

So, if you are facing the same then you are at the right place to find the solution of query how to fix SQLite error database is locked. Let’s find out the reasons of this error before going for its solution.

Reasons Responsible for Error Database is Locked

This error code occurs when the user tries to perform two inappropriate operations on a database at the same detail and on the same database connection. This error code shows that an operation can’t be continued due to encounter with a transaction that uses the same database connection or the transaction that uses a distinct database connection by using a shared cache.

Also Read: Best Solution to Fix SQLite Error Malformed Database Schema

Assume a scenario, when you attempt to run a DROP TABLE statement meanwhile a different thread is trying to read from the same table and that also on the same database connection. Then, the table would be deleted and therefore, the other thread will be unable to read from it.

Different Scenarios of SQLite Database is Locked Error Code 5

The different scenarios when you can receive error code 5: database is locked. These are listed below:

The situation in which you want to CREATE or DROP an index or a table whilst the SELECT statement is in a pending state, the database will get locked. The reason is that the users think that the SELECT statement has been finished as Sqlite3_() has returned the value SQLITE_DONE. Although, this is a different case since SELECT statement is not considered as complete until Sqlite3_reset() or Sqlite3_finalize() are called.
When you attempt to write in a table on which the SELECT operation is still active.
When you attempt to do 2 SELECT on the same table and at the same time in a multi-thread application and the SQLite has not been fixed to do the same, the database may get locked.

One thing you should keep in mind that SQLITE_LOCKED should not be confused with a SQLITE_BUSY parameter. This is because of SQLITE_LOCKED points to a situation when there is a conflict between two transactions running on the same database connection. Moreover, the SQLITE_BUSY shows that two transactions are running on different database connection and in different processes they have conflicted.

Also Read: How to Repair SQLite Database and Restore SQLite Database

Resolve SQLite Locked Error

To fix “SQLite database is locked error code 5” the best solution is to create a backup of the database, which will have no locks on it. After that, replace the database with its backup copy. Follow the following script to do the same where .x.Sqlite is the Sqlite database file:
$Sqlite3 .x.Sqlite

Sqlite> .backup main backup.Sqlite

Sqlite> .exit

Further, you have a file named backup.Sqlite in the same directory. Then you have to swap your old database with the backup copy of the database. So, the backup copy will not have any locks, the SQLite database is locked error code 5 will not be conflicted.

$mv .x.Sqlite old.Sqlite

$mv backup.Sqlite .x.Sqlite

After the successful execution of the above script, you can further access the SQLite database. Investigate that the database is allowing both the read and write operations to run successfully, and then you can delete the old SQLite database file.

Also Read: Reasons & Solution for Corrupt SQLite Database

Automated Method to Fix SQLite Locked Error

Download Now

There is an alternate solution that is Aryson SQLite Database Recovery tool. It supports the corrupt SQLite database created by SQLite2 and SQLite3. It can easily recover the SQLite database which is corrupt due to various reasons.

Источник

Summary:-Have you stumbled upon the “SQLite database is locked error code 5”? While performing a query, a user may encounter an Error where the database is locked.

Read the article to know the common causes of why this error occurs and the possible fixes. SQLite is a simple RDBMS(Relational Database Management System) in the C library. It is not a client-server database engine, unlike other Database Management Systems.

Reasons Behind Sqlite Error Database is Locked Code 5

The error occurs mainly when a user executes two inappropriate transactions in any database against the same connection.

This Error message communicates that an identical process can’t complete due to conflict with the transaction utilizing the same database connection or a separate database connection working on a shared cache.

Some Scenarios for SQLite Database is Locked Error Code 5

Being a lightweight database, SQLite can’t handle a high level of concurrency.

Here are a few such instances:

Using a table to write where the SELECT operation is already active.
You are trying to perform two SELECT operations on the same table at the same time in a multi-thread application.
Using DROP or CREATE on an index or a table, while the SELECT statement is pending. The reason being Sqlite3_() returns the value SQLITE_DONE after the SELECT statement operates. Until Sqlite3_reset() or Sqlite3_finalize() are called the SELECT statement is not complete.
An NFS locking issue may generate the error too.

Ways to Fix SQLite error Database is locked code 5

One of the best ways to resolve this error is to create a database backup having no locks on it and replace the original with its backup copy.

Method 1: Creating a new Backup with no locks

Note: Here x.Sqlite is the database file.

$Sqlite3 .x.Sqlite

Sqlite> .backup main backup .Sqlite

Sqlite> .exit

Now in the same directory, save the file backup. Here we are replacing the old locked up database file with the new unlocked copy of it. As the new file isn’t locked up, so the SQLite Database is locked code 5 is no longer there.

Method 2: Rewriting the Code

Whenever we attempt to make the database handle concurrency more than the default configuration, we face an error.

In this case, rewriting a more efficient version of the code statement can help solve the error and unlock the database.

Method 3: Increase Default Timeout

If a transaction takes more time to execute, it can prevent other threads from accessing the database, thus creating a deadlock. (A scenario in which each thread is waiting for a resource while holding a resource needed by the other thread)

To resolve this issue, SQLite has a lock time out. If it finds out that a thread has been waiting for a lock for more than the default time of 5 seconds, the process terminates, and we face the dreaded SQLite DB is locked error.

To fix this error, you can increase the timeout value from the timeout database option.

Best Fix to Automatically Resolve the SQLite Error

The manual methods are a bit tricky to follow through and sometimes may fail to fix the issue entirely. A user may also face data loss/corruption if some difficulty occurs.

So we use the alternative automated solution via SQLite Database Recovery Software. That will help users to Restore SQL Database with a Different Name

Using a Recovery Software has other added advantages too, which help in the long run.

Benefits of using an SQLite Database Recovery Software

Support for the repair of corrupt SQLite2 and SQLite3 databases.
Restore SQLite objects like tables, views, triggers, indices.
It comes with the column Mapping feature to save the recovered data into the MBD Database.
User-Friendly and interactive interface.
It supports all popular Windows versions.

Summing Up

I see you’ve made it to the end of the article. You can go through these fixes one by one to see which one works out.

After you know why the “SQLite database is locked code 5” error occurs with the causes and fix using the manual method as well as using an SQLite Database Recovery Tool. It’s smarter to go for a recovery tool as you can use it as many times as you want in the future too.

Источник

If you are encountering the “SQLite database is locked” error, it means that the database you are trying to access is already in use by some other process. This can be caused by a number of different reasons, but the most common cause is that another connection to the database has not been properly closed.

👋 Check out our easy to use desktop GUI for SQL

Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!

Available for MacOS, Linux, and Windows.

Here are some steps you can take to try and resolve the “SQLite database is locked” error:

Check Open Connections

Check if there are any other connections to the database that are still open. If there are, try to close them and then try accessing the database again.

Restart Your Application

If there are no other open connections, try restarting the application that is using the database. This can sometimes resolve the issue if the problem is with the application itself.

Tweak Using The Shell

If the problem persists, try using the SQLite command line shell to access the database directly. This can be done by using the “sqlite3” command, followed by the path to the database file.

Once you are in the shell, you can try running the “PRAGMA busy_timeout” command, followed by a timeout value in milliseconds. This can help the database handle the lock more gracefully, allowing you to access it even if it is currently in use by another process.

Manually Unlock Using The Shell

If none of the above steps work, you may need to try manually unlocking the database. This can be done by using the “PRAGMA lock_status” command in the SQLite shell, which will show you the current state of the locks on the database.

If the database is indeed locked, you can try using the “PRAGMA busy_timeout” and “PRAGMA lock_timeout” commands to set a timeout for the lock, after which it will be released automatically.

Consider A True Multi Tenant Database

If all else fails, you may need to consider using a different database system, such as MySQL or PostgreSQL, which may be better equipped to handle locking and concurrency issues.

Database locked error summary

Overall, the “SQLite database is locked” error can be frustrating to deal with, but with a little bit of patience and troubleshooting, it can usually be resolved. By following the steps outlined above, you should be able to access your database again and continue working with it as normal.

Источник

Read time 5 minutes

A locked SQLite database stops the user from writing more transactions, and the tables are not updated or altered anymore. If you are facing the same problem, then you will get some simple solutions to remove error 5 and make the SQLite database functional.

Introduction

SQLite database system is developed with a C library. As it follows a weak SQL syntax, it has many issues related to its integrity.

Cause of the error

Normally, the error occurs when two users try to run transactions on the same tables and change the content. SQLite engine finds it abnormal and locks the database. Now, the user cannot run more transactions.

Different scenarios of SQLite Database Locked error

Here are the different scenarios and conditions that can affect the database and lock it:

When a SELECT transaction is already activated on a table, you try a CREATE query.
If the SELECT statement is pending, and you put a new CREATE or DROP statement. Generally, the user thinks that when SQLite gives SQLITE_DONE status, it means that the process is complete. But the overall changes are not completed until sqlite_finalize() or sqlite_reset() commands are run.
When multiple threads access the same table and two or more SELECT queries try to access the same table, the engine may block some access and lock the database.

NOTE- Some users confuse SQLITE_LOCKED with SQLITE_BUSY status, but they are different. The BUSY status shows that two separate transactions are working on two different databases. When the queries are still processing, it shows the busy status. But, when two separate transactions try to run the query on the same database, it creates conflict, and the database engine locks the database. No further queries will work on a locked database.

How to remove SQLITE_LOCKED error?

The simple solution to remove the lock error is to use the backup copy of the same database. If you have taken the backup file of SQLite, then you can restore it at the database engine and remove the locked one. Follow the below process-
Suppose your database name is Administrator.sqlite
$Sqlite Administrator.Sqlite
Sqlite> .backup main BackupAdmin.Sqlite
Sqlite> .exit
In the same directory, you will have the same backup file with the BackupAdmin name. You should replace the backup database with the primary database.
$mv Administrator old.Sqlite
$mv BackupAdmin.Sqlite Administrator.Sqlite
After the successful completion of the queries, the backup file will replace the locked database. As the backup file does not have any lock, it will be ready for the new transactions and queries by the user. Restoring database files from a backup file is the best practice to recover SQLite Database Files that have become corrupt.

Professional solution to the SQLite database locked error

When you have not taken the backup of the database, the above-mentioned manual solution is not feasible for you. You should use professional software to scan the database and recover it. Kernel SQLite Database Recovery can help you in recovering the database without any corruption, deletion, or locking. It is among the best 6 SQLite Database Recovery tool in the market available today.

1. After you install the software, you can start it in the Application Menu. Here, you can see that it has various sections. Click Open.
2. Click the Browse button in the Database Selection wizard.
3. Choose the database from its location, then click Recover.
4. After scanning the database, there will be a clean preview of database items. You can check the table on which the database was locked. Click Save.
5. Click Browse in the Output Folder wizard.
6. Choose a folder where you will save the database file. The folder should be accessible, and there should be enough space in the drive to save large database files. Click OK.
7. Click OK in the wizard after selecting the folder.
8. The database objects are saved quickly.
9. A successful message shows that the saving process is completed. Click OK.

Now, you should go to the database engine and check the functional aspects of the resultant database.

Conclusion

There are different ways to fix SQLite issues. SQLite offers help to its users using its technical forum where the user can ask questions and seek answers. Also, there is a published error list that contains various errors and their solutions. Kernel SQLite database repair software helps when SQLite corruption occurs and deletes various items. If you face a situation where a database is locked and you do not have any backup copy, use our software and recover SQLite data. The software supports all the SQLite database extensions for recovery and saves data in the same format.

Источник