SQLite Forum

Empty journal files left on disk after connection was gracefully closed
Login

Empty journal files left on disk after connection was gracefully closed

(1) By PazO (JohnSmith) on 2020-11-10 04:51:36 [link] [source]

My database is opened in sqlite MULTITHREADING(2) mode, with one connection per-thread.
Journal mode is WAL.
During lifetime of the application I am using sqlite3_backup API to save an active database connection to an external file.

What I found is that after I finished successfully to back the database, and after a successful call to sqlite3_backup_finish(), for the new file created I have two journal files left on the disk. Files extensions are '-wal' and '-shm' (e.g. - 'newFile.db-wal', 'newFile.db-shm').
The '-wal' file is empty as expected for gracefull connection shutdown.

I have two questions:

  1. Can I configure sqlite to delete empty journal files?

  2. Can I configure sqlite not to use shared-memory files?

Reason why I think I do not need shared-memory files: My application is using multiple connections - one connection per-thread - BUT I do not support multiple applications accessing the same database.

Thanks for any tip, Pazo

(2) By Larry Brasfield (LarryBrasfield) on 2020-11-10 05:39:55 in reply to 1 [link] [source]

I will leave the posed questions to others better able to answer them. I write to clear up a misconception you have made apparent.

With respect to how SQLite arranges for DB integrity in the face of possible overlapping accesses, there is no practical difference between connections used one per thread among separate processes and connections used one per thread within a single process. So your stated reason for eschewing certain SQLite strategies for protection against concurrent access is fallacious. As a syllogism, it rests upon a false premise.

(3) By PazO (JohnSmith) on 2020-11-10 06:30:26 in reply to 2 [link] [source]

Maybe I did not fully understand this answer.
If this is referring to my note regarding the need for shared-memory then I will emphasize: generally speaking shared-memory is only needed for sharing data between different processes. If a single process wants to share data between its various threads it can do so using heap memory or any other process-internal memory. Since my application is using the database exclusively I thought that sqlite implementation will not need shared-memory per se (unless if it is hard-wired to do so).

(4) By Gunter Hick (gunter_hick) on 2020-11-10 07:37:44 in reply to 1 [link] [source]

The WAL file contains the write ahead log itself. The shm file contains pointers to specific locations inside the WAL file and allows them to be found quickly, without having to read through the whole WAL file.

Therfore, the shm file is crucial to WAL mode, even if there is only a single thread in a single process accessing any given database. It is also what allows multiple threads (irrespective of their location in one or more processes) to cooperate in WAL mode.

(5) By Clemens Ladisch (cladisch) on 2020-11-10 07:46:27 in reply to 1 [link] [source]

Can I configure sqlite to delete empty journal files?

In theory, the WAL files should be automatically deleted when the backup DB connection is closed: https://www.sqlite.org/wal.html#the_wal_file

You can also switch that DB out of WAL mode: PRAGMA journal_mode = delete;

Can I configure sqlite not to use shared-memory files?

Only when you have a single connection: https://www.sqlite.org/wal.html#use_of_wal_without_shared_memory

(SQLite does not differentiate between connections from multiple processes, and multiple connections from threads in a single process.)

(6) By PazO (JohnSmith) on 2020-11-10 09:44:08 in reply to 5 [link] [source]

Regarding READ-ONLY connections:

Is it safe to have the following configuration:

  • PRAGMA journal_mode=OFF
  • PRAGMA synchronous=0

?

(7) By Keith Medcalf (kmedcalf) on 2020-11-10 09:51:48 in reply to 3 [link] [source]

No.

The Write-Ahead-Logging (WAL) journal mode uses a write-ahead-log (wal) file and and index for this file (shm). This is a fact associated with the journaling mode and has nothing whatsoever to do with how many connections/threads/processes access the database.

Multi-access and concurrency are entirely unrelated matters.

(8.1) By Keith Medcalf (kmedcalf) on 2020-11-10 11:46:56 edited from 8.0 in reply to 6 [source]

If you define what you mean by "READ-ONLY connection" your question answers itself.

There is no such thing as a "read-only" connection. A database may be "read-only" but that does not have anything to do with the "connection" other than the connection is the "pipe" through which your "application" accesses "database files".

And if your application only ever does database queries over a connection (I assume this is what you think makes a connection read-only) then what difference would it make since those things only affect writes?

Is the database file really immutable or are you just meaning by logical happenstance in that your application just happens to not write at the moment (which sort of begs the question of how did whatever is there to be read get there in the first place)?

(9) By PazO (JohnSmith) on 2020-11-10 12:56:42 in reply to 8.1 [link] [source]

By READ-ONLY connection I am referring to an sqlite connection that was opened by a call to ::sqlite3_open_v2(..) with the flag SQLITE_OPEN_READONLY.

My assumption is that if I opened this connection with this flag then this connection can be used only for reading, hence 'READ-ONLY connection'.

Important clarification: For this particular database-file that I open in READ-ONLY mode there is only one connection attached - this connection.

I read in sqlite documentation that the WAL attribute is persistent, meaning that if I will open a file that was previously used with WAL journal, and if I will not specify journal-mode then I will receive WAL. For this reason I asked whether for such a READ-ONLY connection the above settings [journal-mode:OFF; syncronous=0] are valid, or at all needed?

(10) By Keith Medcalf (kmedcalf) on 2020-11-10 19:00:22 in reply to 9 [link] [source]

How can you change the journal_mode from WAL to something else if the database is Read-Only since changing the journal_mode to or from wal requires writing to the database?

In fact, you will find that you cannot change the journal_mode at all.

(11) By Keith Medcalf (kmedcalf) on 2020-11-10 19:07:44 in reply to 9 [link] [source]

My assumption is that if I opened this connection with this flag then this connection can be used only for reading, hence 'READ-ONLY connection'.

Yes, the "main" database is opened r/o. Databases are attached r/o. Except of course "temp" which is r/w.

Note that read-only does not mean immutable. Other connections can still write to the database. This means that all the "normal facilities" must be present to support this eventuality -- the prohibition against writing is merely logical.