6 ms·

> Checkpointers do not block writers, and writers do not block checkpointers. This means that, even following the checkpoint, the wal file cannot be overwritten

> Checkpointers do not block writers, and writers do not block checkpointers. This means that, even following the checkpoint, the wal file cannot be overwritten or deleted, and so all subsequent transactions must also be appended to the wal file. The work of the checkpointer is not wasted - SQLite remembers which parts of the wal file have already been copied into the db file so that the next checkpoint does not have to do so again Probably because of this. > but it does mean that the wal file may grow indefinitely if the checkpointer never gets a chance to finish without a writer appending to the wal file. There are also circumstances in which long-running readers may prevent a checkpointer from checkpointing the entire wal file - also causing the wal file to grow indefinitely in a busy system. > Wal2 mode does not have this problem. In wal2 mode, wal files do not grow indefinitely even if the checkpointer never has a chance to finish uninterrupted. I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem?

stavros 3y ago
I suspect that having two WAL files means you can freely checkpoint the one that isn't currently being written to, but that's just a guess.
CuriousCosmic 3y ago
> I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem? Because with a single wal file you can't checkpoint it during a read since said file may change out from under you. With two wal files, the one you are actively appending to can be treated like in wal1 mode but the one that isn't being appended to is immutable for the time being just like the main database. This means you can treat the actual db file and the immutable wal file together as one immutable database file with some underlying abstraction. That abstraction then allows you to perform the checkpoint operation since the abstraction can keep all that immutable data accessible in some form or another while reworking the data structure of the db file. Then once the checkpoint is complete, the abstraction can clear the now redundant immutable wal file, become transparent, and just present the underling single DB file. And now once the wal file you are actively appending to reaches a sufficient size, you "lock" that one, rendering it immutable, and switch over to appending to the cleared wal file you were previously checkpointing. With this you can now checkpoint again without blocking reads or writes.
- willvarfar 3y ago
  > once the wal file you are actively appending to reaches a sufficient size, you "lock" that one so there might eventually be wal3 and wal4 files and so on?
  - CuriousCosmic 3y ago
    nope. since if the wal you are checkpointing isn't done yet, you just wait to "lock" and switch files until that operation is complete. Checkpointing can be considered "lock free" since the operation will always eventually complete. How long it takes will depend on the wal file being checkpointed into the db but it'll eventually complete in some finite amount of time. Because you know that any given checkpointing operation has to eventually complete, you can simply keep appending to the current "append" wal file and then tackle those changes when you finish the current checkpoint op (at which point the wal file you just finished checkpointing is free to take the appends).
    - athoscouto 3y ago
      Wouldn't that require the reader to finish? Making the reader block the checkpoint from wrapping up?
      - CuriousCosmic 3y ago
        Not if you abstract those reads. It's not like the application is directly opening a file handle. Instead it goes through the sqlite library which accesses not only the db and the wal file but also a non-persistent shm (shared memory) file used for maintaining a consistent view of the write ahead log. When a reader is reading, it puts a shared lock on the specific data it is reading in the shm file. The checkpointer respects that lock and may (potentially) continue working elsewhere in the db file, slowly updating the indices for checkpointed data in the shm file. The checkpointer won't change the underlying data that the reader has a lock on but they may have created a new location for it. When the reader is finally done reading, the checkpointer can quickly grab an exclusive lock and update the header in the shm for that data to point to the new destination (and then release said lock). Since the checkpointer never holds this lock for very long, the reader can either block when trying to get a shared lock or it can retry the lock a few moments later. Now that the header in the shm only points to the new location, the checkpointer can safely do whatever it needs to with the data in the old location. Slowly rinse repeat this until the checkpointer has gotten through the entire write ahead log. At that point there should be no remaining references in the shm to data within the wal file. Now the wal file can be "unlocked" and if the other wal file is large enough, it can be locked, writes switch over to the other wal, and the cycle repeats anew. Edit: Importantly, this requires that all readers be on a snapshot that includes at least one commit from the "new" wal file. So compared with wal1, wal2 allows you to have long running readers as long as they start past the last commit of the "previous" wal file.

stavros 3y ago

I suspect that having two WAL files means you can freely checkpoint the one that isn't currently being written to, but that's just a guess.

CuriousCosmic 3y ago

> I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem? Because with a single wal file you can't checkpoint it during a read since said file may change out from under you. With two wal files, the one you are actively appending to can be treated like in wal1 mode but the one that isn't being appended to is immutable for the time being just like the main database. This means you can treat the actual db file and the immutable wal file together as one immutable database file with some underlying abstraction. That abstraction then allows you to perform the checkpoint operation since the abstraction can keep all that immutable data accessible in some form or another while reworking the data structure of the db file. Then once the checkpoint is complete, the abstraction can clear the now redundant immutable wal file, become transparent, and just present the underling single DB file. And now once the wal file you are actively appending to reaches a sufficient size, you "lock" that one, rendering it immutable, and switch over to appending to the cleared wal file you were previously checkpointing. With this you can now checkpoint again without blocking reads or writes.

willvarfar 3y ago
> once the wal file you are actively appending to reaches a sufficient size, you "lock" that one so there might eventually be wal3 and wal4 files and so on?
- CuriousCosmic 3y ago
  nope. since if the wal you are checkpointing isn't done yet, you just wait to "lock" and switch files until that operation is complete. Checkpointing can be considered "lock free" since the operation will always eventually complete. How long it takes will depend on the wal file being checkpointed into the db but it'll eventually complete in some finite amount of time. Because you know that any given checkpointing operation has to eventually complete, you can simply keep appending to the current "append" wal file and then tackle those changes when you finish the current checkpoint op (at which point the wal file you just finished checkpointing is free to take the appends).
  - athoscouto 3y ago
    Wouldn't that require the reader to finish? Making the reader block the checkpoint from wrapping up?
    - CuriousCosmic 3y ago
      Not if you abstract those reads. It's not like the application is directly opening a file handle. Instead it goes through the sqlite library which accesses not only the db and the wal file but also a non-persistent shm (shared memory) file used for maintaining a consistent view of the write ahead log. When a reader is reading, it puts a shared lock on the specific data it is reading in the shm file. The checkpointer respects that lock and may (potentially) continue working elsewhere in the db file, slowly updating the indices for checkpointed data in the shm file. The checkpointer won't change the underlying data that the reader has a lock on but they may have created a new location for it. When the reader is finally done reading, the checkpointer can quickly grab an exclusive lock and update the header in the shm for that data to point to the new destination (and then release said lock). Since the checkpointer never holds this lock for very long, the reader can either block when trying to get a shared lock or it can retry the lock a few moments later. Now that the header in the shm only points to the new location, the checkpointer can safely do whatever it needs to with the data in the old location. Slowly rinse repeat this until the checkpointer has gotten through the entire write ahead log. At that point there should be no remaining references in the shm to data within the wal file. Now the wal file can be "unlocked" and if the other wal file is large enough, it can be locked, writes switch over to the other wal, and the cycle repeats anew. Edit: Importantly, this requires that all readers be on a snapshot that includes at least one commit from the "new" wal file. So compared with wal1, wal2 allows you to have long running readers as long as they start past the last commit of the "previous" wal file.