sqlite3_close() drops wal and shm files despite of other processes holding DB open

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_close() drops wal and shm files despite of other processes holding DB open

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: [hidden email]

 

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee

Geschäftsführer: Marco Runge, Jürgen Gentzsch

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Dan Kennedy-4

On 11/7/62 23:07, Andreas Kretzer wrote:

> 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.


Does lsof show that your app has a read-lock on the database file (not
the *-wal or *-shm files) just before this happens?

Are you executing any PRAGMA statements in the app? "PRAGMA
locking_mode=none" for example?

Or are you opening/closing the database file directly at all (separate
from SQLite), causing SQLite's locks to be dropped by this POSIX quirk?

https://sqlite.org/howtocorrupt.html#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_

Dan.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Andreas Kretzer
In reply to this post by Andreas Kretzer
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: [hidden email]

 

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee

Geschäftsführer: Marco Runge, Jürgen Gentzsch

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Andreas Kretzer-2
In reply to this post by Andreas Kretzer
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: [hidden email]

 

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee
Geschäftsführer: Marco Runge, Jürgen Gentzsch

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Andreas Kretzer-2
... and here is the solution!

I daemonize my programs using

    daemon (1, 1);

If I do that, before I open the DB, then everything is alright,
locks are held - fine!

In the other case I was first opening the DB to fetch some configuration
options from it before I took care of program args and then possibly
daemonize, if the "do not run in background"-option is not set.

And so, if I use this foreground option, my process wild hold the locks
just fine! -> Never use daemon() with open DB.

Thanks for the hints to how to have a look at locks and that it is
worth taking that look!

I think, this problem is solved - I have to change the order of the
startup. Maybe I even open the DB, read the configuration and close
it again, before opening it again after being daemonized.

Andreas

Am 16.07.2019 um 12:21 schrieb Andreas Kretzer:

> 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: [hidden email]

 

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee

Geschäftsführer: Marco Runge, Jürgen Gentzsch

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Hick Gunter
I think your file locks are getting stranded in the parent process during your daemon() call.

When you open the SQLite db, you are opening file descriptors and generating memory state.
Accessing the db to read config creates further memory state plus some file level locks.
Which will persist until you commit the transaction.

The first step in creating a daemon is to fork() from the parent process. This inherits the memory state and open file descriptors BUT NOT the file record locks.

If you continue running the same image in the child process, SQLite will THINK it still has those locks and/or is still within a transaction.

The parent process then needs to terminate, releasing the locks it still had.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Andreas Kretzer
Sent: Dienstag, 16. Juli 2019 12:40
To: [hidden email]
Subject: [EXTERNAL] Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open

... and here is the solution!

I daemonize my programs using

    daemon (1, 1);

If I do that, before I open the DB, then everything is alright, locks are held - fine!

In the other case I was first opening the DB to fetch some configuration options from it before I took care of program args and then possibly daemonize, if the "do not run in background"-option is not set.

And so, if I use this foreground option, my process wild hold the locks just fine! -> Never use daemon() with open DB.

Thanks for the hints to how to have a look at locks and that it is worth taking that look!

I think, this problem is solved - I have to change the order of the startup. Maybe I even open the DB, read the configuration and close it again, before opening it again after being daemonized.

Andreas

Am 16.07.2019 um 12:21 schrieb Andreas Kretzer:

> 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: [hidden email]



AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee

Geschäftsführer: Marco Runge, Jürgen Gentzsch



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open

Andreas Kretzer-2
Sure - I loose my locks when calling daemon(). I already have
changed my program to close and re-open the DB when daemonizing.
I was not aware, that daemon() by itself would do a fork().
There is no explicit fork() in my program...

Anyway, it is now clear what should be avoided: carrying an open
DB over the daemon() call. Maybe it would be worth noting that in
the section about "How to corrupt the DB".

B.T.W.: You are right about the fork(). I just read the manual page
for daemon(3) again and there is stated:

    RETURN VALUE
           (This function forks, and if the fork(2) succeeds, the parent
           calls _exit(2), so that further  errors  are  seen  by  the
           child  only.)   On  success  (...)

So it is clear, what happens here. But it was not obvious to me, that
daemon() uses fork(). I was aware, that a fork() would be a good way
to ruin the DB, but I thought I was not doing that :-D

Anyway, thank you all for your support and the helpful hints. SQLite
is a real cool and handy software - right what I needed.

Andreas

Am 16.07.2019 um 13:19 schrieb Hick Gunter:

> I think your file locks are getting stranded in the parent process during your daemon() call.
>
> When you open the SQLite db, you are opening file descriptors and generating memory state.
> Accessing the db to read config creates further memory state plus some file level locks.
> Which will persist until you commit the transaction.
>
> The first step in creating a daemon is to fork() from the parent process. This inherits the memory state and open file descriptors BUT NOT the file record locks.
>
> If you continue running the same image in the child process, SQLite will THINK it still has those locks and/or is still within a transaction.
>
> The parent process then needs to terminate, releasing the locks it still had.
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Andreas Kretzer
> Sent: Dienstag, 16. Juli 2019 12:40
> To: [hidden email]
> Subject: [EXTERNAL] Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open
>
> ... and here is the solution!
>
> I daemonize my programs using
>
>     daemon (1, 1);
>
> If I do that, before I open the DB, then everything is alright, locks are held - fine!
>
> In the other case I was first opening the DB to fetch some configuration options from it before I took care of program args and then possibly daemonize, if the "do not run in background"-option is not set.
>
> And so, if I use this foreground option, my process wild hold the locks just fine! -> Never use daemon() with open DB.
>
> Thanks for the hints to how to have a look at locks and that it is worth taking that look!
>
> I think, this problem is solved - I have to change the order of the startup. Maybe I even open the DB, read the configuration and close it again, before opening it again after being daemonized.
>
> Andreas
>
> Am 16.07.2019 um 12:21 schrieb Andreas Kretzer:
>> 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: [hidden email]
>
>
>
> AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee
>
> Geschäftsführer: Marco Runge, Jürgen Gentzsch
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--

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: [hidden email]

 

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee

Geschäftsführer: Marco Runge, Jürgen Gentzsch

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users