RC6SQLite WAL-Mode + Encryption + Multi-Process Access via ActiveX/OLE - Any Caveats?

[tmighty2]

Hello everyone,

I’ve enabled WAL (Write-Ahead Logging) in my RC6 SQLite database using:

cn.ExecCmd "PRAGMA journal_mode=WAL"

It seems to work fine – the -wal and -shm files are created as expected.
I'm also using RC6's built-in encryption layer.

My Setup

I have multiple ActiveX/OLE worker objects, designed in the style of Olaf Schmidt’s MBSet architecture.
Each worker potentially runs in a separate process (EXE) and accesses the same encrypted SQLite database concurrently.

Examples:

Worker 1: Fetches and sends emails.
It reads from the DB, writes incoming messages, logs send results, and keeps the connection open indefinitely.
This runs every 10 minutes.

Worker 2: Handles forwarding of specific messages.

Worker 3: Parses emails and stores structured data.

All workers share the same database file, potentially accessed at the same time from different EXEs.

I enabled WAL mode specifically to avoid locking issues and improve write concurrency.

The Problem
After I had to forcefully terminate one of the workers, I couldn’t open the database as usual anymore.
Looks like it was left in an inconsistent state – probably due to incomplete WAL commits or locked *.shm state.

My Questions
Has anyone worked with encrypted SQLite + WAL mode in a multi-process environment using ActiveX/OLE components?

Are there any known caveats when keeping multiple connections open across EXEs?

Is it safe to leave connections open long-term, or should I periodically close and reopen them?

Could the combination of WAL and RC6 encryption introduce issues during abrupt termination?

I'm going to investigate this further, but I’d really appreciate any shared experience or best practices on this topic.

Thanks in advance!

[tmighty2]

Edit: When I remove the -wal and -shm files files (I just put them into a subdir), the DB opens again successfully.
So I am pretty sure that it is actually related to WAL.

[tmighty2]

Also, can you please tell me how to pass a password to such a worker? Or should it instead be passed an already open connection?

[wqweto]

> I enabled WAL mode specifically to avoid locking issues and improve write concurrency.

There is no write concurrency in sqlite i.e. it's always only one writer which locks the entire database so no one else can write to it.

In WAL mode when DB is locked for write, readers are *not* blocked which is good and the difference with non-WAL modes.

Another caveat, you have to turn off OS level write-cache on the volume your DB file is placed on. Otherwise when a writer process is terminated or machine is restarted mid transaction the DB can be left in inconsistent state as you've already noticed. Such problems in WAL mode stem from OS level write-cache, nothing inherently wrong with sqlite's implementation.

Start->Run: devmgmt.msc to start Device Manager. Expand Disk Drives, open Properties of your disk and select tab Policies. Uncheck Enable write caching on the device and expect the respective degradation in performance if this is your system disk -- OS boot, VB6 compilation speed, etc. everything will be slower. But. . . your sqlite DB files (and your JET .mdb files) have stellar chances to survice hard crashes w/ no data loss.

cheers,
</wqw>

[tmighty2]

Thank you very much for the insights. I am using a vhxd to store all my projects and data, and there was one situation where I put my laptop to rest and did not eject the mounted drive before, and the vhxd was corrupted.
I believe that you have also solved this issue for me...
Thank you!

[tmighty2]

I didn't find it at first. I had to click the Admin button first. Hope it helps somebody else.

[Zvoni]

An alternative might be to separate the Database-File into 3 Files (one for each worker), especially if the Data itself have no correlation to each other during WRITING to Database.
For querying across all Data you can attach the Database-Files to, say, a "central" DB-File.
That way you could avoid concurrency and/or WAL.

But only you know the Data, and how it is interconnected

https://sqlite.org/lang_attach.html
https://www.sqlitetutorial.net/sqlite-attach-database/

[tmighty2]

Thank you. Of course I am glad about any helpful suggestion.
You said that only I know the data and how it's interconnected.
I thought I had given just this info in my post?!

[tmighty2]

Edit: vhxd is just bad. Even though I deactived failsafe mode, a .frm file was written only half when I ejected the mounted volume. Around half of the text was just missing.
Therefore I am not using vhxd anymore.