SQLite Forum

Locking_Mode=EXCLUSIVE: What is the Journal file?
Login

Locking_Mode=EXCLUSIVE: What is the Journal file?

(1) By anonymous on 2020-12-17 18:03:57 [link] [source]

With an .open database, and on applying PRAGMA locking_mode=Exclusive followed by a write operation, a file with the same name as the .open database but with extension .Journal appears; it is permanent i.e. does not disappear when the session is terminated.

  1. What is the purpose of the *.Journal file?
  2. Should I keep the *.Journal file?
  3. If yes, when moving the database file to another location, should the *.Journal file be moved also?

(2) By Keith Medcalf (kmedcalf) on 2020-12-17 18:22:41 in reply to 1 [link] [source]

With an .open database, and on applying PRAGMA locking_mode=Exclusive followed by a write operation, a file with the same name as the .open database but with extension .Journal appears; it is permanent i.e. does not disappear when the session is terminated.

Do you mean -journal appended to the database filename perchance?

What is the purpose of the *.Journal file?

The journal file is used to journal transactions in order to have this thing called ACID, which is the whole point of using a "database" rather than just using a standard file.

Should I keep the *.Journal file?

Yes.

If yes, when moving the database file to another location, should the *.Journal file be moved also?

Yes.

However, having a -journal left behind is an indicator that either (a) something terminated before committing a transaction; (b) it is a deliberate configuration; or, (c) it is a bug.

What version of SQLite3 are you using, on what Operating System, and what is the journal_mode, and did you configure to leave the journal file behind?

(3) By anonymous on 2020-12-17 19:04:10 in reply to 2 [link] [source]

Thanks for the feedback.

What version of SQLite3 are you using, on what Operating System, and what is the journal_mode, and did you configure to leave the journal file behind?

  1. I am using version 3.34 on Windows 10.
  2. I did not deliberately configure to leave the Journal file behind; that this requires configuration suggests that there is an option to remove the Journal file. What are the configuration options?
  3. The journal file appeared when I executed PRAGMA locking_mode = EXCLUSIVE and executed an SQL statement that made a change to the database.

The Journal file size is larger than the size of the database file; is this always the case? Aside of using over double the size of the database, does this impact on query execution times, given that 2 files need to be kept synchronised?

PS: I can understand another file appearing but would have expected it to disappear as soon as the database on which locking _mode is set is closed.

(4.1) By Keith Medcalf (kmedcalf) on 2020-12-18 04:26:00 edited from 4.0 in reply to 3 [link] [source]

I cannot reproduce this.

The journal contains before images of modified pages so that transactions can be rolled back. It appears that as a side-effect when locking_mode=exclusive the journal_mode=delete internally becomes journal_mode=persist in that the journal is not deleted at commit time but rather has the header overwritten with 0's (probably as an optimization).

However, the journal is deleted on close. Or at least it is for me on Windows.

(5) By anonymous on 2020-12-18 07:37:51 in reply to 4.1 [link] [source]

However, the journal is deleted on close. Or at least it is for me on Windows.

Yes. I've re-traced my steps & the Journal file is deleted on close.

Previously, I think my session might have ended without closing.

(6) By Simon Slavin (slavin) on 2020-12-18 18:36:50 in reply to 5 [link] [source]

If you see a journal file, then some part of SQLite didn't realise you were finished with that database. You left a database connect open in your code, or one of the programs crashed while a connection was open, or you had a hardware failure. If you tell SQLite to access that database again, and it sees a journal file it will figure out what was happening, and either finish or cancel whatever was going on.

If you see a journal file and you need to move the database before letting SQLite fix the problem and delete the journal file, move the journal file with the database.

It's worth noting that the journal file is not the only kind of temporary file SQLite might create. Any file you find with a name similar to the database file should be treated the same. And they should all disappear if you close your connections correctly.

(7) By Keith Medcalf (kmedcalf) on 2020-12-18 18:48:15 in reply to 5 [link] [source]

If you do not like that the journal is maintained at its maximum used size before the connection is closed you can change the journal_mode to truncate (which works with locking_mode=exclusive) to release the disk space after each transaction commit. Theoretically the journal_size_limit will also be effective in limiting the retained journal size although I have not tested this.

(8) By anonymous on 2020-12-18 19:00:05 in reply to 7 [link] [source]

In the CLI there is no .close method. Is another .open the only way to close an existing .open (which might have locking_mode=Exclusive)?

(9.2) By Keith Medcalf (kmedcalf) on 2020-12-18 19:35:45 edited from 9.1 in reply to 8 [source]

No, exiting the CLI will close the file.

On Windows use Control-Z as a "short form" for typing the .exit command. (This is basically sending End-of-File on the input stream -- that would be control-D on Linux).

If you exit with break/Control-C or other signal then you are ABENDing the program and the the process is simply "de-rugged" and will not cleanup itself.

Of course, the CLI only works with one connection (open database) at a time, so issuing a .open command will close the previously open connection and and open a new one to the database specified.

Just like you do not need to specify a database filename on the CLI command-line, you do not need to specify a filename on the .open command. A bare ".open" will close the current database connection and then open a new temporary database connection.

It should be noted that the .sqliterc and -init parameters only apply to the initially opened connection (the connection opened from the command line) and NOT to databases opened with the .open command.

(10) By MichaelW on 2020-12-18 19:38:00 in reply to 9.1 [link] [source]

Hi I only have one database and quit the program within the program

file_menu.add_command(label = "Quit", font="helvetica 12", command = root.quit)

I still get the same error ie:

ValueError: parameters are of unsupported type

We seem to have a problem. Cheers Michael