Quote from thejerrybryan on 2025-10-05, 11:40 pm

Here is what ChatGPT says.

What do -shm and -wal mean in SQLite?

When a SQLite database is opened in WAL (Write-Ahead Logging) mode, which is a journal mode intended to improve performance and concurrency, two auxiliary files are typically created:

1. [yourfile]-wal — This is the WAL file.

   • It stores changes that have not yet been committed to the main database.

   • It improves performance by allowing reads and writes to occur concurrently.

2. [yourfile]-shm — This is the shared memory file.

   • It supports coordination between multiple connections to the same database.

   • It’s used internally by SQLite for managing WAL access, such as locking and cache.

Quote from thejerrybryan on 2025-10-05, 11:54 pm

More from ChatGPT:

Let’s walk through the risks and what the switch to WAL mode means for RootsMagic users storing .rmtree files in synced folders like Dropbox, OneDrive, iCloud, Google Drive, etc.

---

⚠️ The Big Picture: SQLite + Cloud Sync = Fragile

SQLite databases are not designed for concurrent access from multiple machines or real-time syncing by file-based cloud services. This has always been true — but WAL mode makes it worse, not better, for this particular use case.

---

🧨 Why syncing a WAL-mode SQLite DB is even more dangerous

When WAL mode is active, you now have three interrelated files:

1. yourfile.rmtree — the main database file

2. yourfile.rmtree-wal — the write-ahead log (ongoing changes not yet merged into main file)

3. yourfile.rmtree-shm — shared memory file (used for lock management and cache coordination)

These three files are tightly coupled while the database is in use.

✴️ What can go wrong?

1. Partial syncs:

   • If only one or two of the three files sync before the others (due to bandwidth, latency, or timing), then the receiving device will have inconsistent state, and the database could become corrupted or unreadable.

2. Locked files:

   • The .rmtree-shm file often uses memory-mapped I/O and OS-level locking, which some sync services don't handle well. You might see syncing errors or zombie lock files.

3. Crash recovery failures:

   • If RootsMagic crashes or is force-closed on one machine, the WAL file may contain unmerged data.

   • If that incomplete WAL file is synced to another machine (where the .rmtree file is in a slightly different state), it may try to replay WAL data inappropriately, corrupting the DB.

4. Conflicts and overwrites:

   • Sync services may create conflicted copies of one of the files (e.g., yourfile (1).rmtree-wal), which SQLite won’t recognize. It’ll just treat the database as broken.

---

🛑 Bottom line: WAL mode increases the risk of corruption when syncing live databases

Even though WAL mode improves performance and concurrency in local environments, it makes cloud sync scenarios more brittle.

---

✅ Safe Practices for RootsMagic + Cloud

If you want to store your .rmtree database in a synced folder, here are safe workarounds:

🧳 1. Don’t open the database from the synced location while the cloud sync is running

• Pause Dropbox/OneDrive sync while working on the DB

• Resume sync after exiting RootsMagic

🗄️ 2. Keep your active database outside the sync folder

• Work on the DB in a local-only folder (e.g., C:\Genealogy_Work)

• Set up manual backups (or periodic automated scripts) to copy the .rmtree file to a cloud folder when closed

🧼 3. Avoid using across multiple computers

• Never open the same RootsMagic database on multiple machines unless you’re sure the cloud service has fully synced all files and RootsMagic is closed on the other device.

☁️ 4. Use cloud services with app-specific sync (like Dropbox Smart Sync or Google Drive File Stream) cautiously

• Some of these use “on-demand” file downloading and may not handle fast-changing auxiliary files well.

Quote from thejerrybryan on 2025-10-06, 12:19 am

Here is still more from ChatGPT. But before I paste it, let me make a couple of comments of my own.

1. I had read the difference between Rollback Journal mode and WAL mode before in the SQLite documentation itself. It says the same thing as ChatGPT, but I find the ChatGPT version easier to read and easier to understand.
2. I understand why syncing and backup services can invalidate a backup of an SQLite database. But despite all the discussion about it,  I sometimes have a hard time seeing how syncing and backup services can invalidate the database itself. Why aren't the reads and write from the SQLite app simply intermixed with the reads from the backup service? Sometimes I think I understand, and sometimes not. But in any case, it's not clear to me why WAL makes it worse, even though ChatGPT says it does. And remember that pretty much all ChatGPT knows about the subject is what it finds on the Internet.

 

🔄 Journal Mode (Traditional Rollback Journal) — What You Described

Write Process:

1. Create journal file

   • The original content of pages that are about to be changed is written to a yourfile.rmtree-journal file.

2. Apply changes directly to the database

   • SQLite modifies the database file on disk.

3. Commit or rollback

   • If commit succeeds, the journal file is deleted.

   • If anything goes wrong, the journal is used to roll back to the state before the transaction.

Key traits:

• All changes go directly into the main database file

• Journaling ensures crash-recovery safety

• Safe for single-writer, few-readers

• Poor for concurrency

---

🚀 WAL Mode — How It’s Different

WAL mode completely flips the process on its head.

Write Process:

1. All writes go into the yourfile.rmtree-wal file, not the main database.

   • The main .rmtree file is never modified directly during normal writes.

   • Only at special moments is it updated (more on this below).

2. Readers read from the main database and overlay recent changes from the WAL.

   • Readers can see a consistent snapshot while writers are active.

   • Multiple readers and one writer can coexist without locking each other out.

3. Checkpointing:

   • At some point (manually or automatically), a checkpoint occurs.

   • This means the changes in the WAL file are merged into the main .rmtree file, and the WAL file is truncated or reset.

4. Crash recovery:

   • On next open, SQLite checks the WAL file.

   • If it contains uncheckpointed commits, they're applied to the database to bring it up to date.

The yourfile.rmtree-shm file:

• Used as a shared memory region to manage locks and cache state.

• Only exists while the DB is open in WAL mode.

• Required for coordination between multiple processes (not stored in the DB itself).

Quote from Richard Otter on 2025-10-06, 1:05 am

Thanks Jerry! Very informative.

I never read that part of the SQLite docs.
Thanks much for saving me lots of time.

I wonder how his change to RM operation will play out;
for instance, will it affect how external apps write to the database while RM also has the database open

Richard

Quote from Tom Holden on 2025-10-06, 10:18 am

will it affect how external apps write to the database while RM also has the database open

I think the "how" has been answered and that you mean by that is what effect it will have on the performance of outboard apps interacting with the database.

RootsMagic 4 was the first version using SQLite3 and its release predated by six months the introduction of WAL-mode in SQLite 3.7.0. WAL-mode is supposed to be an improvement over the older Journal-mode but the latter is supported by all later versions (now 3.50.4) for backward compatibility. SQLite versions earlier than 3.7.0 cannot open a WAL-mode database.

WAL-mode was developed to improve concurrency of multiple readers with single writers and time delay between successive writers. If anything, that should improve outboard/inboard interplay but it might be indiscernible in practice. Most of my usage involves switching between RM and outboard UI to see the effect that doing something in one had on the other; the switching itself typically requires a fresh query to see the results and the other query is finished by then.

What it might benefit is the frequency of RM 'database is locked' errors being encountered within RM because it seems capable of initiating a write before another one it started has finished.

The SQLite.org advice remains the same: avoid syncing an active database and work on a truly local drive (not NAS). The risk of a lock or corruption is higher with WAL than with Journal-mode. That said, about the only interaction I've had with RM11 so far has involved a very large database in OneDrive which I did not pause, created a group using the much improved criteria for Search, and ran an outboard query to make bulk changes which would reduce the group membership to 0 when refreshed. Nothing adverse happened.

Quote from thejerrybryan on 2025-10-06, 10:58 am

There is another little tidbit that I noticed that ChatGPT didn't mention. With the old rollback journal style of doing things, whenever a transaction was successfully completed, SQLite would "delete" the rollback journal file. There were two modes of operation for the "delete".  One mode of operation would literally delete the rollback journal file from the folder, and the next UPDATE transaction would recreate the rollback journal file in the folder. Creating and deleting files in a folder is an expensive and inefficient operation, but RM did use this mode of operation. The other mode of operation was that the rollback journal file would be set to zero length rather than being deleted from the folder. This was much more efficient. There is a PRAGMA setting for this mode, and RM used the less efficient setting.

With the WAL-mode of operation of operation, the WAL file seems to remain in place for the duration of the RM session. I don't know if the WAL mode has the same two options for "deleting" the WAL file as does the rollback journal mode and RM just chose the more efficient setting this time, or if the WAL mode only has the one setting and that's just the way it works. In any case, the WAL file will be there for the duration of an RM session and will be deleted at the end of the session unless there is a crash of RM or of the computer. In that case, the WAL file will be processed by SQLite the next time RM starts. This processing should be completely transparent to RM itself.

Quote from Charles Banks on 2025-10-07, 4:47 pm

I wrote a custom application that renames downloaded media to my standards, then copies the file to my media folder.  The application will also create a citation for an existing source and add information from notes.  I read up on WAL mode and tried to understand record locking in SQLite.  This post was brought to my attention by a friend which I am grateful for.  First, I did not realize RM 11 switched to WAL mode.  Second, you really helped my understanding of WAL mode.  The only part I still don't understand is with checkpointing.  Manual checkpointing, I get with SQLite but not sure how automatic would work.  Just like you can't run stored procedures.

Thank you for providing such a detailed post.  As always, you post are greatly appreciated.

Charles

Quote from thejerrybryan on 2025-10-07, 6:16 pm

I became curious about the same check-pointing questions as you. So I asked ChatGPT one more question. I'll paste the answer below, but let me first make a couple of comments. The answer from ChatGPT is pretty simple, but it doesn't tell us how RM does it.

There is an implicit checkpoint at obvious times, like when the app shuts down. The WAL is written to the database and then the WAL is deleted. Other than that, the checkpoints can be automatic or manual. The automatic checkpoints take place when the WAL reaches a certain size. The fact that a checkpoint is going on is transparent to the app, and the app can keep reading and writing all through the checkpoint.

The manual checkpoints take place any time the app wants them to. The app just tells SQLite to do a checkpoint, and it does.  And just like with the automatic checkpoint, the app doesn't have to wait for the manual checkpoint to complete before it continues reading and writing.

I don't think there is a way to turn off the automatic checkpoint, but if the app did manual checkpoints often enough, the WAL might not ever grow big enough to trigger an automatic checkpoint.  What I don't know is whether RM does any manual checkpoints or not. I doubt that it does, but I also don't think it makes much difference whether it does or not. If there is a dangling WAL file when RM shuts down, the implicit checkpoint will take care of it.

Here follows ChatGPT stuff about the SQLite checkpoint stuff.

Skip to content

You said:

when SQLite is operating in WAL mode, how does it decide when to do a checkpoint? And how does it protect the integrity of the database if there is a system or disk failure in the middle of the checkpoint?

ChatGPT said:

In WAL (Write-Ahead Logging) mode, SQLite uses a WAL file to store changes instead of directly modifying the main database file. This improves concurrency and performance, but requires a checkpoint process to eventually transfer the changes from the WAL file back to the main database file.

---

🔄 When Does SQLite Perform a Checkpoint?

SQLite performs a checkpoint to merge the contents of the WAL file into the main database file. This happens under several conditions:

1. Automatic Checkpointing:

   • By default, SQLite uses auto-checkpointing.

   • When the WAL file grows to a certain size (default: 1000 pages, or ~4 MB with 4KB pages), a checkpoint is automatically attempted.

   • You can configure this threshold using:

• • PRAGMA wal_autocheckpoint = N; -- N is the number of pages


• Manual Checkpointing:

  • Applications can manually trigger checkpoints with:

2. • PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(FULL);
PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_checkpoint(TRUNCATE);


3. Implicit Checkpointing:

   • A checkpoint may be triggered by certain operations like closing the database or transitioning back to rollback mode.

---

🛡️ How Does SQLite Ensure Integrity During a Checkpoint?

Checkpointing is carefully designed to be safe, even in the event of a crash, power loss, or disk failure. Here's how:

1. Atomic Updates:

• The changes are written to the main database file page-by-page.

• Each page is overwritten in-place, but SQLite uses checksums and page numbers to detect corruption.

2. WAL File Remains Untouched Until Done:

• The WAL file isn't deleted or truncated until the checkpoint completes safely.

• This means if a crash happens mid-checkpoint, the WAL file still has a complete record of changes, and recovery can resume from it.

3. Recovery After Crash:

• On next database open, if the WAL file still exists, SQLite automatically replays it to recover uncheckpointed changes.

4. WAL Index File Protection:

• The WAL index (in shared memory or on disk) helps coordinate access.

• If the index becomes corrupt, it can be reconstructed from the WAL file itself.

5. fsync and File System Syncs:

• SQLite uses fsync() (or equivalent) to ensure data is physically written to disk before claiming a write is complete.

• This helps avoid data loss due to OS or disk caching.

---

✅ Summary

Feature	Description
When	After WAL file reaches a size threshold, manually, or at shutdown
Why	To merge WAL changes back to the main database file
Crash Protection	WAL file is untouched during checkpoint; recovery uses WAL file
Atomicity	Pages are written individually, with checksums and ordering
Reliability	fsync ensures durability; WAL can be replayed after crash

---

If you’re managing SQLite in a production app, you can tune checkpointing behavior or even run background checkpoint threads to manage performance vs. durability trade-offs.

Would you like a diagram or code example for how checkpointing works?

You said:

What about when the checkpoint process cannot update in place because new data is being added. And what about when data is being deleted rather than being updated in place?

ChatGPT said:

Great follow-up! Let's break it down and answer how SQLite handles checkpoints when:

1. ✅ New data is being added

2. ❌ Data is being deleted

3. 🛑 Checkpointing cannot update in-place because the WAL file is still growing

---

1. ✅ What if New Data is Being Added During Checkpoint?

🔄 WAL and Concurrency

SQLite is designed to allow reads and writes to continue while a checkpoint is in progress. This includes adding new data (INSERT or UPDATE that affects new pages). Here's how it manages that:

• The checkpoint only processes WAL frames (transactions) that are complete and committed at the time it begins.

• New writes after the checkpoint starts go to new frames at the end of the WAL file.

• The checkpoint doesn’t interfere with new data—it just skips over any frames not part of a committed transaction or written after the checkpoint started.

🧠 Key Concept:

Checkpoints are not blocking writes, unless a FULL or RESTART checkpoint is requested and the system tries to delete/truncate the WAL file—then some coordination is needed.

---

2. ❌ What About Deletions? How Are They Handled in WAL Mode?

🗑️ DELETEs in SQLite:

In SQLite, deleting data doesn't shrink the database file immediately. Here's what happens:

• The page containing the deleted row(s) is modified to remove entries (like clearing a slot in a B-tree).

• That modified page is written to the WAL file (just like any other UPDATE).

• During checkpoint, this updated page overwrites the page in the main DB file.

So the deletion is treated like any other page change — SQLite doesn’t care whether the change is due to INSERT, UPDATE, or DELETE. The checkpoint just copies the most recent version of each page from the WAL file back to the main DB.

🚫 File Shrinking?

• Even if a row is deleted, the database file might not get smaller. SQLite reuses free pages rather than shrinking the file (unless you run VACUUM).

• WAL file may still grow if checkpoints are delayed or can't complete.

---

3. 🛑 What If Checkpoint Cannot Complete Due to Ongoing Writes?

This is a real-world scenario and SQLite handles it carefully.

🔁 Partial Checkpoints:

If a checkpoint can't complete (e.g., because pages being written are still in use):

• It performs a partial checkpoint — it copies as many pages as possible from the WAL to the DB.

• The rest (pages written after the checkpoint started or still in use by readers) stay in the WAL.

• The WAL file continues to grow.

You can check the result of manual checkpoints using:

PRAGMA wal_checkpoint(RESTART);


This returns a result like:

0|80|10 -- status_code | total_frames | checkpointed_frames


If checkpointed_frames < total_frames, then the checkpoint was partial.

🚧 Readers Block Truncation:

If any reader is still using older frames in the WAL (e.g., reading an older snapshot of the DB):

• SQLite cannot truncate the WAL file.

• Those frames must be preserved for the reader to maintain consistency.

• This is why WAL files can grow large if long-running reads are not managed.

---

✅ Summary

Scenario	What Happens
New writes during checkpoint	Go to new WAL frames, ignored by current checkpoint
Deletes	Treated like updates — modified pages copied during checkpoint
Checkpoint can't complete	Partial checkpoint; WAL continues to grow
Active readers	Prevent truncation of WAL, but don’t block writes
WAL integrity	Always preserved until changes are fully checkpointed and synced

---

Would you like a diagram or a simplified walkthrough of a WAL transaction + checkpoint lifecycle (step-by-step with what happens at each point)?

ChatGPT can make mistakes. Check important info.

Quote from Charles Banks on 2025-10-07, 7:17 pm

You have no idea how much you have helped me!  I have been using MS Copilot more and more.  I guess I did not know what the right question was because SQLite is not server based.  With what you came up with in ChatGPT I figured out what to ask.  Here is the response I got to my complex question.

SQLite uses a lazy, cooperative model for checkpointing:

✅ 1. Trigger-on-Write Threshold

• SQLite tracks the size of the .wal file in pages.
• When it exceeds a threshold (default: 1000 pages, configurable via PRAGMA wal_autocheckpoint), the next write operation triggers a checkpoint attempt.
• This is not a background thread — it's the same thread performing the write.  

✅ 2. Cooperative Checkpointing

• The writer thread that hits the threshold attempts a checkpoint.
• If successful, it flushes WAL contents to the main DB.
• If another process has the DB open or a reader is active, the checkpoint may be partial or deferred.

✅ 3. Manual Checkpointing Still Recommended

• For apps with predictable lifecycles (like yours), it's best to manually checkpoint on exit to ensure consistency and minimize .wal file size.
• This avoids relying on the threshold-based trigger, which may not fire if your inserts are small or infrequent.

The last part was because Copilot "thinks" I am talking about my application.

Thanks again.