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
