https://sqlite.org/walformat.html
The -shm file contains information about in process transactions that are blocking the checkpoint, but not the processes that are holding them. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto >Sent: Sunday, 24 March, 2019 06:45 >To: SQLite mailing list >Subject: Re: [sqlite] Diagnosing stale readers > >Hi Larry, > >thanks for you reply. >Indeed, readers are long-lived processes on a Linux system. >They're threads of a Pyhon Flask application to export data, which >uses uses peewee as a wrapper for SQLite. >Theoretically, once a request is served, the transaction should be >terminated. However, I suspect there is some issue there (it's a >pretty complex pipeline to build files to be served on the fly), and >for some reason cleanup is not performed correctly. For one thing, I >did not program it explicitly. >I can of course investigate further, but I was wondering if there was >some way of ascertaining whether my suspicion is correct (and later, >whether I've fixed the bug or not). In other words, if there's a way >of getting a list of the current transactions or locks. >I believe this information is stored somewhere in the WAL-index file >(.db-shm), because that's the kind of information SQLite needs to >decide it can't run a checkpoint (which is believe is what >happening). > >If would also like to know if killing (possibly with -9) a process >with an open handle might accidentally leave the DB locked. > >Thank you, >Gerlando > >On Sun, Mar 24, 2019 at 12:56 PM Larry Brasfield ><brasfield.la...@gmail.com> wrote: >> >> Gerlando Falauto writes: >> >> I'm using SQLite in WAL mode in the following scenario: >> >> - One writer which continuously writes new rows and deletes older >ones >> >> - Multiple readers performing queries >> >> >> >> Writer and readers are running each in a separate process. >> >> The (Inactive) Journal Size limit is set to 100MB by pragmas and in >> >> normal usage this request seems to be honoured (i.e. the file does >not >> >> grow bigger than that). >> >> I've however run into a situation where the .db-wal grows up to >> >> several Gigabytes, and the main .db file stays small. >> >> I believe this can be explained by some stale transaction in the >> >> system preventing the WAL pages from getting discarded (after being >> >> copied to the main database file). >> >> >> >> Any suggestion on how I can diagnose this situation, i.e. identify >the >> >> offending process? >> >> >> >> If the various readers are short-lived processes (relative to the >writer), >> you could treat this similarly to a memory leak. The diagnostic >method >> used for those [a] can be adapted where you would wrap the >transaction >> begin and end operations with registration/deregistration and see >whether >> registrations exceed deregistrations upon program exit. >> >> >> >> [a. Generally, it is to record, in some kind of set or associative >> container, each allocated memory pointer upon the allocation, and >upon >> corresponding frees, to remove the pointer from the set. Once that >is >> setup, leaks are diagnosed by examining the set as main() exits (or >> whatever corresponds to main() in non-C derived languages.) If the >set is >> not empty then, there is a leak. ] >> >> >> >> If the reader processes are long-lived and running on the Windows >OS, >> another approach is viable. There is a handy set of system >utilities which >> allow developers to look at (Windows) OS system objects ( >> https://docs.microsoft.com/en-us/sysinternals/downloads/winobj ) >and see >> what handles are held by a process to such objects ( >> https://docs.microsoft.com/en-us/sysinternals/downloads/handle ). >Since >> SQLite uses various system objects (the DB file and associated >handles, and >> mutexes and associated handles) to coordinate DB access among >processes, >> these utilities would be helpful for figuring out which process is >failing >> to relinquish its claim to DB access. You could still use a >registration >> system to narrow the problem further by attaching a debugger to the >> identified laggard process and examining its pending transaction >registry. >> (Often, resource registration associates the allocation code file >and line >> number with the handle or pointer representing resource.) >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
