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
