SQLite Forum

Journal mode for "" (temp) db cannot be set to WAL?
Login

Journal mode for "" (temp) db cannot be set to WAL?

(1) By Stephan Beal (stephan) on 2021-12-03 03:38:38 [source]

$ sqlite3
SQLite version 3.35.3 2021-03-20 01:00:26
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach "" as foo;
sqlite> pragma foo.journal_mode;
delete
sqlite> pragma foo.journal_mode=WAL;
delete

Question 1: is it not possible to change such a db to WAL mode? Obviously, the WAL journal would be lost when the db is closed, because it's a temp file, but my goal here is not cross-session durability but gaining only the locking benefits of WAL.

Question 2: assuming the answer is "no", is there another way to get similar locking benefits?

Background:

My library has to open several databases and the order they're opened is not set in stone - it depends very much on how the library is used. To avoid certain vagaries of connection-juggling, it initializes itself with a temp db (opened with the name "") to act as its MAIN db and then ATTACHes the others on demand.

It was recently discovered that that leads to certain locking problems, however. e.g. trying to do a DROP TABLE X, where X is a TEMP table, fails in certain cases because cached read-only statements are opened on that connection. The hypothesis is that if the main DB's journal mode can be changed to WAL, that problem will disappear. However, it seems impossible to change the journal mode to WAL.

The docs recommend against changing it to MEMORY or OFF because of the potential for corruption, but my suspicion is that for a temp db like this one, OFF would be completely fine, so long as the journal mode for the attached DBs is WAL/DELETE. The potential lack of atomicity across the ATTACHed DBs in the case of a crash is not critical in this case, as only one operation updates more than one db at a time and that update is easily recovered from if it gets out of sync.

In this library the main DB is used solely as an ATTACH point to manage 1 to 3 other DBs, and does not actually store any tables, so corruption of the main db is a moot point.

Question 3: given the above, is it "safe" to set the journal mode to OFF for the main DB, where "safe" means something along the lines of "will not, by itself, lead to corruption of the ATTACHed DBs in case of a crash"?

(2) By Dan Kennedy (dan) on 2021-12-03 14:57:11 in reply to 1 [link] [source]

Question 1: is it not possible to change such a db to WAL mode? Obviously, the WAL journal would be lost when the db is closed, because it's a temp file, but my goal here is not cross-session durability but gaining only the locking benefits of WAL.

Not possible. There are no locking benefits to gain in this case though, as temp databases are only ever accessed by a single client. wal mode wouldn't change anything in that case.

It was recently discovered that that leads to certain locking problems, however. e.g. trying to do a DROP TABLE X, where X is a TEMP table, fails in certain cases because cached read-only statements are opened on that connection. The hypothesis is that if the main DB's journal mode can be changed to WAL, that problem will disappear. However, it seems impossible to change the journal mode to WAL.

Yeah - you can't do a DROP TABLE if there are active readers. All reader statements need to be sqlite3_reset() or sqlite3_finalize()d first.

Question 3: given the above, is it "safe" to set the journal mode to OFF for the main DB, where "safe" means something along the lines of "will not, by itself, lead to corruption of the ATTACHed DBs in case of a crash"?

It's safe in that respect. Because your main database is a temp db, you won't get atomic commit across multiple attached databases, even in rollback mode. But each individual db will either be completely updated or not updated at all. Changing the journal mode of the main db doesn't change this.

Dan.

(3) By anacrolix on 2023-09-06 01:27:20 in reply to 1 [link] [source]

I do see performance improvements using WAL, even for "temporary" databases.

If I use a specific path and set locking mode EXCLUSIVE for a database, I see a measurable performance improvement over a temporary database that sqlite3 creates for me with an empty path.