On Fri, 13 Dec 2019 at 23:50, 고예찬 wrote:
> Hello, I am experiencing `database is locked` error. I wonder if anyone has
> gone through or resolved similar issue.
>
> To illustrate, I have a .db file with below settings:
> ```
> PRAGMA journal_mode=WAL;
> PRAGMA wal_autocheckpoint=128;
> PRAGMA journal_size_limit=0;
> ```
> and I run two programs in python - one writing the data 1 row per second,
> one read / upload / then delete the data every 30 seconds.
>
> Normally, it works fine but occasionally after power failure and reboot(the
> programs run on an IoT device), the reader program throws `database is
> locked` error and never goes away after since, while the writer program
> seems to work fine. This lock doesn't go away even after program/system
> restart.
This is an odd symptom as the fcntl/POSIX file locks (sqlite's default
locking mechanism under linux) are attributed to processes, and the kernel
never writes them to disk. Is there some kind of network file system
involved or does the IoT device have its own SSD?
> One interesting fact is that if I do `cp data.db data1.db && rm data.db &&
> mv data1.db data.db`. The error always goes away completely. Though it
> happens again after several hard reboot.
>
This series of commands gets around the locks because it creates two copies
of the database contents stored in different inodes. The kernel's locking
table is based on inodes rather than file paths, so you end up with
existing processes accessing the deleted inode and newer processes
accessing the new inode, even though they're both referring to the same
path ./data.db (sidenote: this is also a great way to corrupt databases).
> It would be very helpful if anyone can give any advice.
>
There's not really any operating system/file system mechanism which
adequately explains the symptoms you're seeing, so I'd be looking into your
software components. Figure out what is holding the lock and why. Look for
sqlite_stmts which are never disposed - these will retain their locks (the
sqlite3_next_stmt function can be useful for finding these).
Also, look for code which interacts with data.db directly using regular
file syscalls rather than going via sqlite. Because this:
> If I leave the problem as it is(writer keeps writing, and reader
> keeps retrying upon error), the error thrown changes to `database disk
> image is malformed`.
>
Strongly suggests there is a process somewhere which is touching data.db
without obeying sqlite's expected locking protocol (which prevents
corruption). Even something as innocent as an open()/close() on the file
can cause problems, because of the semantics of POSIX locks (section 2.2 of
https://www.sqlite.org/howtocorrupt.html)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
