hermes - 💡(How to fix) Fix [IMPROVEMENT] Mnemosyne SQLite DB may not be in WAL mode under concurrent access [1 participants]

TL;DR

Switching to Write-Ahead Logging (WAL) journal mode and setting synchronous to NORMAL can resolve the database locking issue.

Guidance

• Run the proposed SQL commands PRAGMA journal_mode=WAL; and PRAGMA synchronous=NORMAL; on the SQLite database to enable WAL mode and adjust the synchronous setting.
• Verify that the database is no longer locked by checking for database is locked errors or timeouts under load after applying the changes.
• Consider implementing the proposed fix via a hermes db migrate command for easier management and automation.
• Test the changes under concurrent access conditions to ensure the fix resolves the issue without introducing new problems.

Example

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

This SQL code snippet sets the journal mode to WAL and adjusts the synchronous setting, as proposed in the issue.

Notes

The effectiveness of this fix relies on the specific concurrent access pattern of the application. If the access pattern changes, the fix may need to be reevaluated.

Recommendation

Apply the proposed workaround by running the SQL commands to switch to WAL mode and adjust the synchronous setting, as this is a targeted solution to the described issue.