Sqlite ошибка database is locked

From django doc:

SQLite is meant to be a lightweight
database, and thus can’t support a
high level of concurrency.
OperationalError: database is locked
errors indicate that your application
is experiencing more concurrency than
sqlite can handle in default
configuration. This error means that
one thread or process has an exclusive
lock on the database connection and
another thread timed out waiting for
the lock the be released.

Python’s SQLite wrapper has a default
timeout value that determines how long
the second thread is allowed to wait
on the lock before it times out and
raises the OperationalError: database
is locked error.

If you’re getting this error, you can
solve it by:

Switching to another database backend. At a certain point SQLite becomes too «lite» for real-world applications, and these sorts of concurrency errors indicate you’ve reached that point.

Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.

Increase the default timeout value by setting the timeout database option

http://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errorsoption

user1857492

6521 gold badge7 silver badges20 bronze badges

answered Jul 3, 2010 at 21:33

patrickpatrick

6,2827 gold badges43 silver badges65 bronze badges

In my case, It was because I open the database from SQLite Browser. When I close it from the browser, the problem is gone.

answered Sep 12, 2016 at 11:21

Aminah NurainiAminah Nuraini

17.4k8 gold badges87 silver badges104 bronze badges

I slightly disagree with the accepted answer which, by quoting this doc, implicitly links OP’s problem (Database is locked) to this:

Switching to another database backend. At a certain point SQLite becomes too «lite» for real-world applications, and these sorts of concurrency errors indicate you’ve reached that point.

This is a bit «too easy» to incriminate SQlite for this problem (which is very powerful when correctly used; it’s not only a toy for small databases, fun fact: An SQLite database is limited in size to 140 terabytes ).

Unless you have a very busy server with thousands of connections at the same second, the reason for this Database is locked error is probably more a bad use of the API, than a problem inherent to SQlite which would be «too light». Here are more informations about Implementation Limits for SQLite.

Now the solution:

I had the same problem when I was using two scripts using the same database at the same time:

one was accessing the DB with write operations
the other was accessing the DB in read-only

Solution: always do cursor.close() as soon as possible after having done a (even read-only) query.

Here are more details.

answered Nov 25, 2018 at 17:43

BasjBasj

39.5k87 gold badges352 silver badges622 bronze badges

The practical reason for this is often that the python or django shells have opened a request to the DB and it wasn’t closed properly; killing your terminal access often frees it up. I had this error on running command line tests today.

Edit: I get periodic upvotes on this. If you’d like to kill access without rebooting the terminal, then from commandline you can do:

from django import db
db.connections.close_all()

answered Oct 22, 2013 at 11:52

WithnailWithnail

3,0802 gold badges31 silver badges47 bronze badges

As others have told, there is another process that is using the SQLite file and has not closed the connection. In case you are using Linux, you can see which processes are using the file (for example db.sqlite3) using the fuser command as follows:

$ sudo fuser -v db.sqlite3
                     USER        PID ACCESS COMMAND
/path/to/db.sqlite3:
                     user        955 F....  apache2

If you want to stop the processes to release the lock, use fuser -k which sends the KILL signal to all processes accessing the file:

sudo fuser -k db.sqlite3

Note that this is dangerous as it might stop the web server process in a production server.

Thanks to @cz-game for pointing out fuser!

vvvvv

20.9k17 gold badges46 silver badges66 bronze badges

answered May 6, 2019 at 7:34

mrtsmrts

15.3k6 gold badges87 silver badges69 bronze badges

I got this error when using a database file saved under WSL (\wsl$ …) and running a windows python interpreter.

You can either not save the database in your WSL-tree or use a linux based interpreter in your distro.

answered May 15, 2021 at 14:44

JoeJoe

732 silver badges4 bronze badges

I encountered this error message in a situation that is not (clearly) addressed by the help info linked in patrick’s answer.

When I used transaction.atomic() to wrap a call to FooModel.objects.get_or_create() and called that code simultaneously from two different threads, only one thread would succeed, while the other would get the «database is locked» error. Changing the timeout database option had no effect on the behavior.

I think this is due to the fact that sqlite cannot handle multiple simultaneous writers, so the application must serialize writes on their own.

I solved the problem by using a threading.RLock object instead of transaction.atomic() when my Django app is running with a sqlite backend. That’s not entirely equivalent, so you may need to do something else in your application.

Here’s my code that runs FooModel.objects.get_or_create simultaneously from two different threads, in case it is helpful:

from concurrent.futures import ThreadPoolExecutor

import configurations
configurations.setup()

from django.db import transaction
from submissions.models import ExerciseCollectionSubmission

def makeSubmission(user_id):
    try:
        with transaction.atomic():
            e, _ = ExerciseCollectionSubmission.objects.get_or_create(
                student_id=user_id, exercise_collection_id=172)
    except Exception as e:
        return f'failed: {e}'

    e.delete()

    return 'success'


futures = []

with ThreadPoolExecutor(max_workers=2) as executor:
    futures.append(executor.submit(makeSubmission, 296))
    futures.append(executor.submit(makeSubmission, 297))

for future in futures:
    print(future.result())

answered Sep 6, 2019 at 18:14

EvanEvan

2,1822 gold badges18 silver badges20 bronze badges

I was facing this issue in my flask app because I opened the database in SQLite Browser and forgot to write the changes.

If you have also made any changes in SQLite Browser, then click on write changes and everything will be fine.

enter image description here

answered Oct 19, 2021 at 17:19

This also could happen if you are connected to your sqlite db via dbbrowser plugin through pycharm. Disconnection will solve the problem

answered Jan 7, 2019 at 15:21

For me it gets resolved once I closed the django shell which was opened using python manage.py shell

answered Feb 6, 2019 at 2:59

Avinash RajAvinash Raj

170k27 gold badges223 silver badges265 bronze badges

I’ve got the same error! One of the reasons was the DB connection was not closed.
Therefore, check for unclosed DB connections. Also, check if you have committed the DB before closing the connection.

answered Sep 2, 2019 at 13:11

I had a similar error, right after the first instantiation of Django (v3.0.3). All recommendations here did not work apart from:

deleted the db.sqlite3 file and lose the data there, if any,
python manage.py makemigrations
python manage.py migrate

Btw, if you want to just test PostgreSQL:

docker run --rm --name django-postgres 
  -e POSTGRES_PASSWORD=mypassword 
  -e PGPORT=5432 
  -e POSTGRES_DB=myproject 
  -p 5432:5432 
  postgres:9.6.17-alpine

Change the settings.py to add this DATABASES:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'myproject',
        'USER': 'postgres',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

…and add database adapter:

pip install psycopg2-binary

Then the usual:

python manage.py makemigrations
python manage.py migrate

answered Feb 14, 2020 at 22:50

KyrKyr

5,3032 gold badges27 silver badges21 bronze badges

Check if your database is opened on another DB Browser.

If it is opened on an other application, then close the application and run the program again.

Vega

27.1k27 gold badges91 silver badges98 bronze badges

answered Jun 5, 2021 at 9:03

Just close (stop) and open (start) the database. This solved my problem.

answered May 6, 2020 at 21:35

doğukandoğukan

20.3k12 gold badges52 silver badges63 bronze badges

I found this worked for my needs. (thread locking) YMMV
conn = sqlite3.connect(database, timeout=10)

https://docs.python.org/3/library/sqlite3.html

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

answered Aug 18, 2020 at 4:04

In my case, I added a new record manually saved and again through shell tried to add new record this time it works perfectly check it out.

In [7]: from main.models import Flight

In [8]: f = Flight(origin="Florida", destination="Alaska", duration=10)

In [9]: f.save()

In [10]: Flight.objects.all() 
Out[10]: <QuerySet [<Flight: Flight object (1)>, <Flight: Flight object (2)>, <Flight: Flight object (3)>, <Flight: Flight object (4)>]>

Tonechas

13.1k15 gold badges43 silver badges78 bronze badges

answered Nov 21, 2020 at 8:54

In my case, I had not saved a database operation I performed within the SQLite Browser. Saving it solved the issue.

answered Sep 13, 2018 at 21:41

A very unusual scenario, which happened to me.

There was infinite recursion, which kept creating the objects.

More specifically, using DRF, I was overriding create method in a view, and I did

def create(self, request, *args, **kwargs):
    ....
    ....

    return self.create(request, *args, **kwargs)

answered Apr 19, 2020 at 20:33

LonerLoner

1483 silver badges8 bronze badges

Already lot of Answers are available here, even I want to share my case , this may help someone..

I have opened the connection in Python API to update values, I’ll close connection only after receiving server response. Here what I did was I have opened connection to do some other operation in server as well before closing the connection in Python API.

answered Jun 27, 2020 at 9:02

indevindev

1052 silver badges8 bronze badges

If you get this error while using manage.py shell, one possible reason is that you have a development server running (manage.py runserver) which is locking the database. Stoping the server while using the shell has always fixed the problem for me.

answered Aug 4, 2020 at 13:09

actually I have faced same problem , when I use «transaction.atomic() with select_for_update() » i got error message «the OperationalError: database is locked» ,

and after many tries / searching / read django docs ,
i found the problem from SQLite itself it is not support select_for_update method as django DOCs says , kindly have a look at the following url and read it deeply:

https://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errors

, and when i moved to MySQL everything goes fine .

as django DOCs also says «database is locked» may happen when database timeout occur ,
they recommend you to change database timeout by setting up the following option :

'OPTIONS': {
    # ...
    'timeout': 20,
    # ...
}

finally, I recommend you to use MySQL/PostgreSQL even if you working on development environment .

I hope this helpful for you .

answered Mar 23, 2021 at 4:27

K.AK.A

1,0289 silver badges21 bronze badges

I got this error when attempting to create a new table in SQLite but the session object contained uncommitted (though flushed) changes.

Make sure to either:

Commit the session(s) before creating a new table
Close all sessions and perform the table creation in a new connection
…

answered May 11, 2021 at 8:39

mibmmibm

1,2882 gold badges12 silver badges23 bronze badges

@Shilp Thapak’s answer is correct: the reason for the error is that you did not write your manual changes to the data in your DB Browser for SQLite before running your application.

If you didn’t write the changes in whatever SQL client you are using, you can still create the engine but

will throw the operational error about the database being locked.

You can check whether your engine can connect by checking the existence of a rollback journal. The default mode of a rollback journal is to be created and deleted at the start and end of a transaction.

It is exists in the same directory where your database is, it has the same name as the database file and the suffix «-journal» appended.

If the mode is not changed, at Journal mode in Edit pragmas panel in DB Browser for SQLite.

You can check the existence of the temp file like so:

if os.path.isfile('your-database.sqlite-journal'):
    print("The database is locked. Please write your changes in your SQL client before proceeding.n")

Various Reasons Responsible for SQLite Error Code 5

This error occurs whenever an SQLite user performs two inappropriate transactions or operations in a database on the same data connection.

The error message indicates that a similar operation can not be performed as there is an encounter with the transaction using the same database connection or a different database connection using a shared cache.

Different scenarios:

Different scenarios in which one can encounter this error are given below:

When you use a table to write and on which the SELECT operations are previously active.
The database gets locked in a situation when you try to DROP or CREATE an index or table while the SELECT statement is still in a pending state. The main reason behind it is the misconception of the users that if the Sqlite3_() returns the SQLITE_DONE value, the SELECT statement is finished. However, there is a different scenario because the SELECT statement is not considered to be completed until Sqlite3_reset() or Sqlite3_finalize() are called.
When you try to SELECT operations on the same table at the same time in an application having multiple threads and SQLite is not allowed for the same.

Manual Methods to Fix SQLite Database is Locked Error

It becomes really annoying whenever this SQLite_locked error occurs but using some of the mentioned methods, you can easily unlock the SQLite database.

1. By Creating Backup Having No Locks.

To remove the error code 5 from the SQLite database, the best way is to create a backup of the database having no locks. After creating backup, just replace the original one with the backup copy. After that execute the below script to do so:

Note- Here, db.SQLite is meant to be an SQLite database file.

$Sqlite3 .db.Sqlite

Sqlite> .backup main backup.Sqlite

Sqlite> .exit

In the same directory, you will get the newly created backup file. Now swap the old file with this new file and execute the further script.

$mv .db.SQLite old.Sqlite

$mv backup.Sqlite .db.Sqlite

After the execution of the above script just check that the database is open allowing for both Read and Write. Then delete the old database file and you are good to go.

2. Rewrite The Code:

SQLite is a “lite” database for real-world implementation. Whenever we try to make the database handle concurrency more than the default configuration, you can get errors. Therefore, rewriting the code for reducing concurrency can help to unlock the SQLite Database. By doing so you can also ensure that the transactions are not long-lived.

3. Increase Default Timeout:

If a transaction takes too much time then it can block the other threads from accessing the database. In the worst-case scenario, several threads can deadlock while waiting for each other’s completion. For this issue, SQLite has a lock timeout. If it detects that a thread is waiting for a lock for more than default time(5 seconds), it stops the process and we face the SQLite DB is locked error.

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

Alternate Solution to resolve the SQLite error

If you are finding difficulty in manual methods, then you can use the alternate method of using an SQLite Database Recovery software. It recovers all the SQLite database and fixes the corruptions in the database created by both SQLite2 and SQLite3 easily. Also, it is an efficient tool with an advanced algorithm that can effectively restore the SQLite Database.

Conclusion

In this article, I have discussed the SQLite error Database is locked. In addition to this, the reasons behind its occurrence and the possible manual and automated methods to resolve this particular error are also discussed. Go through these solutions as per your choice. If you have any other query regarding the process, then do let me know, I’ll do my best to help you out. I hope this article is able to resolve your issue.

Источник

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.

Источник

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,58413 silver badges21 bronze badges

answered Aug 13, 2010 at 22:37

nonenononeno

3,2441 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,3312 gold badges28 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.2k28 gold badges142 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

8981 gold badge6 silver badges24 bronze badges

answered Aug 4, 2009 at 11:02

Ben LBen L

6,4508 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

67.5k11 gold badges97 silver badges131 bronze badges

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

shA.t

16.4k5 gold badges52 silver badges111 bronze badges

answered Mar 8, 2016 at 9:49

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,3391 gold badge27 silver badges35 bronze badges

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,3392 gold badges30 silver badges42 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

76.8k42 gold badges148 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.4k6 gold badges60 silver badges86 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.4k5 gold badges52 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

18.5k5 gold badges71 silver badges93 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.4k1 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,2496 gold badges37 silver badges52 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,5419 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.5k11 gold badges44 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.4k5 gold badges52 silver badges111 bronze badges

answered Apr 20, 2016 at 10:31

kothvandirkothvandir

2,0612 gold badges19 silver badges33 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.4k5 gold badges52 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

15.4k10 gold badges82 silver badges98 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,9371 gold badge30 silver badges29 bronze badges

Источник

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,58413 silver badges21 bronze badges

answered Aug 13, 2010 at 22:37

nonenononeno

3,2441 gold badge15 silver badges5 bronze badges

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,3312 gold badges28 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.2k28 gold badges142 silver badges147 bronze badges

answered Mar 7, 2013 at 12:28

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.

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

answered Jun 22, 2009 at 14:25

AaronAaron

6085 silver badges8 bronze badges

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

8981 gold badge6 silver badges24 bronze badges

answered Aug 4, 2009 at 11:02

Ben LBen L

6,4508 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

answered Jun 16, 2011 at 3:19

jogojapanjogojapan

67.5k11 gold badges97 silver badges131 bronze badges

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

shA.t

16.4k5 gold badges52 silver badges111 bronze badges

answered Mar 8, 2016 at 9:49

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!

answered Feb 19, 2015 at 16:05

J.JJ.J

3,3391 gold badge27 silver badges35 bronze badges

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,3392 gold badges30 silver badges42 bronze badges

answered Nov 14, 2008 at 18:44

Kyle CroninKyle Cronin

76.8k42 gold badges148 silver badges163 bronze badges

DON’T FORGET CALL:

sqlite_reset(xxx);

sqlite_finalize(xxx);

answered Feb 17, 2012 at 18:39

Mike KeskinovMike Keskinov

11.4k6 gold badges60 silver badges86 bronze badges

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

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.4k5 gold badges52 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

18.5k5 gold badges71 silver badges93 bronze badges

answered Jun 12, 2009 at 20:20

Shawn SwanerShawn Swaner

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

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,2496 gold badges37 silver badges52 bronze badges

answered Oct 24, 2012 at 17:33

MartinMartin

7,5419 gold badges48 silver badges82 bronze badges

(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.5k11 gold badges44 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

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.4k5 gold badges52 silver badges111 bronze badges

answered Apr 20, 2016 at 10:31

kothvandirkothvandir

2,0612 gold badges19 silver badges33 bronze badges

I got this error in a scenario a little different from the ones describe here.

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.4k5 gold badges52 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

15.4k10 gold badges82 silver badges98 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

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,9371 gold badge30 silver badges29 bronze badges

Источник

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.

Источник

Last updated on July 22nd, 2022 at 11:47 am

Summary:- Whenever we try to develop an application with SQLite, we often get an error SQLite Database is Locked. So in this “How to” article we will get to know how to fix Operational Error Code 5. Here, we will discuss three manual methods to fix the operational error database is locked. Furthermore, We will learn about a utility named SQLite Database Repair Tool.

SQLite Database Repair Tool

SQLite is a relational database management system which is contained in a C library. Rather being a client-server database engine it is embedded into the end-program which makes it fast and contrast to many other database systems.

SQLite Database is Locked | Operational Error Code 5

This is an Operational error which indicates that the application is handling more concurrency than the default configuration. it means that one thread has a lock on the database and other thread or process is waiting for the release of that lock.

Reasons for the Error Database is locked

SQLite_locked error has multiple reasons to occur:

Being a lightweight database, SQLite can’t handle a higher level of concurrency.
When trying to Insert into a table while a Select thread is active on the same table.
When trying to perform two Select on a table at the same time in a multithread application while SQLite is not configured for that.
An NFS locking issue is generating the SQLite_locked error.
whenever trying to Create or Drop a table while a Select thread is going on that table.

Fact_O_Verse: SQLite_locked vs SQLite_busy
There is a huge confusion between SQLite_locked and SQLite_busy. SQLite_locked represents a situation when there is a dispute between two transactions running on the same database connection. On the other hand, SQLite_busy points to a state when two transactions are running on different database connections and they have conflicted on different processes.

It becomes really annoying whenever this SQLite_locked error occurs but using some of the mentioned methods, you can easily unlock SQLite database.

1. By Creating Backup Having No Locks

To remove the error code 5 from SQLite database, the best way is to create a backup of the database having no locks. When the backup is created, just replace the original one with the backup copy. Just execute the below script to do so:

Note:- Here, db.SQLite is meant to be a SQLite database file.

$Sqlite3 .db.Sqlite

Sqlite> .backup main backup.Sqlite

Sqlite> .exit

In the same directory, you will get the newly created backup file. Now just swap the old file with this new file and execute the further script.

$mv .db.Sqlite old.Sqlite

$mv backup.Sqlite .db.Sqlite

After the execution of the above script just check that the database is open allowing for both Read and Write. Then delete the old database file and you are good to go. This way you can easily fix SQLite Database is locked error.

2. Rewrite The Code

SQLite is a “lite” database for real-world implementation. Whenever we try to make the database handle concurrency more than the default configuration we face errors. So rewriting the code for reducing concurrency can help to unlock SQLite Database. By doing so you can also ensure that the transactions are not long-lived.

3. Increase Default Timeout

If a transaction takes too much time then it can make other threads block from accessing the database. In the worst-case scenario, several threads can deadlock while waiting for each other’s completion. To remove this issue, SQLite has a lock timeout. If it detects that a thread is waiting for a lock for more than default time(5 seconds), it stops the process and we face SQLite database is locked error.

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

An Automated Solution to Unlock SQLite Database

The real trouble with the database is this error. As I’ve mentioned there are multiple ways to unlock SQLite Database but each of the steps got its own pros and cons. It becomes more dreadful if the database gets altered or deleted during these processes. So it’s better to go for an automated solution that can do the job without any data loss. My recommendation is to go for the SQLite Database Repair Utility which can easily fix most of the database errors with zero data alterations.

You can watch this informational video to resolve SQLite Database is locked error and unlock the SQLite Database.

Conclusion

As I’ve given you all the possible reasons which can create blunders in your application and database. So try to keep all those reasons in your mind while working with the SQLite database and if you are up to fix your database then please don’t forget to take a backup first because these processes can ruin your data as well. If you have any other queries regarding the process to fix SQLite Database is locked error then do let me know. I’ll do my best to help you out. Hope your issue gets resolved.

Источник

Sqlite ошибка database is locked

Various Reasons Responsible for SQLite Error Code 5

Manual Methods to Fix SQLite Database is Locked Error

1. By Creating Backup Having No Locks.

2. Rewrite The Code:

3. Increase Default Timeout:

Alternate Solution to resolve the SQLite error

Conclusion

“error code 5: Database is locked”

Reasons Responsible for Error Database is Locked

Different Scenarios of SQLite Database is Locked Error Code 5

Resolve SQLite Locked Error

Automated Method to Fix SQLite Locked Error

my python code

my python code

Introduction

Cause of the error

Different scenarios of SQLite Database Locked error

How to remove SQLITE_LOCKED error?

Professional solution to the SQLite database locked error

Conclusion

SQLite Database is Locked | Operational Error Code 5

Reasons for the Error Database is locked

1. By Creating Backup Having No Locks

2. Rewrite The Code

3. Increase Default Timeout

An Automated Solution to Unlock SQLite Database

Conclusion

А вот еще интересные материалы: