Interessting insights!
Dan pointed out, that I should check the locks on the DB files and report
that information back. Unluckily, my 'lsof' just reports PIDs,
executable name
and open file. It is a Busybox multicall binary.
The good thing is: I have 'lslocks' on my system, which gives me exactly
the information I need. It turns out, that one of the two main processes
doesn't apply a lock at all (still under investigation why). The
process, that
uses exactly the same procedure to open the DB helds locks on the DB itself
and the '-shm' file, the other does not:
root:~> lsof |fgrep ecc.db
698 /usr/bin/lcd_manager /data/ecc/ecc.db
698 /usr/bin/lcd_manager /data/ecc/ecc.db-wal
698 /usr/bin/lcd_manager /data/ecc/ecc.db-shm
706 /usr/bin/ecc_core /data/ecc/ecc.db
706 /usr/bin/ecc_core /data/ecc/ecc.db-wal
706 /usr/bin/ecc_core /data/ecc/ecc.db-shm
root:~> lslocks
COMMAND PID TYPE SIZE MODE M START END PATH
lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335
/data/ecc/ecc.db
lcd_manager 698 POSIX 32K READ 0 128 128
/data/ecc/ecc.db-shm
root:~>
At least, this explains why closing the second process (lcd_manager)
will close
the DB completely, removing the '-shm' and '-wal' file.
I will now investigate, why the first process doesn't acquire the locks.
I will report back, when I know more!
Andreas
Am 11.07.2019 um 18:07 schrieb Andreas Kretzer:
> I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3
> filesystem.
>
> Several processes hold the DB open and the "-wal" and "-shm" files exist.
> if I use 'lsof | fgrep <name of DB>' I can see all processes having all
> three
> files open. At least one of the processes uses threads, but every process
> has just one single DB connection active which is shared among all threads.
>
> The compilation of sqlite3 is done with multithreading in mind:
>
> sqlite> pragma compile_options;
> COMPILER=gcc-6.2.0
> ENABLE_DBSTAT_VTAB
> ENABLE_FTS4
> ENABLE_JSON1
> ENABLE_RTREE
> ENABLE_STAT3
> ENABLE_STMTVTAB
> ENABLE_UNKNOWN_SQL_FUNCTION
> ENABLE_UPDATE_DELETE_LIMIT
> HAVE_ISNAN
> THREADSAFE=1
>
> I can check, that the database is threadsafe (mode == 1) and is switched
> to WAL-mode.
>
> So far I never noticed any problems dealing with concurrent updates or so.
> The only thing (tested in depth with V3.15.2 and V3.29.0) is when one
> process stops and closes the database using sqlite3_close(). This may even
> be the sqlite3 CLI. That process closes DB (lsof shows that this process has
> closed its filedescriptors and is not in the listing anymore). Right at the
> next write access to the DB in the still running process (at least I
> think that
> this is exactly the point) the "-wal" and "-shm" files are removed.
> The sqlite3_exec() function still returns SQLITE3_OK on all following
> actions,
> but 'lsof' reports, that this process has opened the "-wal" and "-shm"
> files,
> but marked as "deleted". And they are really deleted and none of the
> upcoming
> DB changes will ever reach the real DB.
>
> What is wrong? I already checked, that my kernel supports POSIX file locking
> (CONFIG_FILE_LOCKING=yes). What else can I check? Two or more sqlite3 CLI
> processes started in parallel don't exhibit this behavior.
>
> Thanks
>
> Andreas
--
Mit freundlichen Grüßen
Andreas Kretzer
ETB Electronic Team
Beratungs- und Vertriebs GmbH
Berliner Straße 8a
15537 Erkner
FON +49 3362 889349-12
FAX +49 3362 889349-23
email: a.kret...@etb-electronic.de
AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee
Geschäftsführer: Marco Runge, Jürgen Gentzsch
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
