Improve Multithreading Performance of Sqlite Database by WAL (Write Ahead Logging)

By Default, the SQLite locks the database when there are simutanenous reading and writing. SQlite stores data in a file. For example we can use the following command to open a database named sample.db

1

sqlite3 sample.db

sqlite3 sample.db

Sqlite
If a thread A is performing a read query e.g. SELECT, and another thread B is performing a write query e.g. WRITE, then the SQLite Database will be locked temporarily with “Database is Locked.”

This is due to the fact that any write SQL operations (UPDATE, INSERT, DELETE, CREATE, DROP) will lock the entire table.

SQLite’s WAL (Write Ahead Logging) Mode

Fortunately, we can enable the WAL logging mode in SQlite, to avoid the locking when there are reading and writing queries at the same time. You can do this when opening the SQlite Database and connections:

1
2
3
4
5
6
7
8

conn = sqlite3.connect("sample.db", isolation_level=None)
try:
    conn.execute('pragama journal_mode=wal')
    ## other code goes here
    ## conn.execute('select * from table')
    ## ...
finally:
    conn.close()

conn = sqlite3.connect("sample.db", isolation_level=None)
try:
    conn.execute('pragama journal_mode=wal')
    ## other code goes here
    ## conn.execute('select * from table')
    ## ...
finally:
    conn.close()

When WAL (Write Ahead Logging) mode is enabled, Sqlite will keep the write changes in a separate file e.g. sample.db-wal and sample.db-shm (two additional files suffixed with -wal and -shm), and it will automatically merge write changes back to the main file e.g. sample.db

Drawbacks of WAL Mode in Sqlite

The downside of the WAL mode is that TLDR;:

• The read queries may not retrieve the latest changes since the up-to-date updates may not be merged back to main yet.
• The data may be subject to loss due unfortunate event of hardware failure.
• the file modification time to the main sqlite database file may not reflect the last time when records are updated. You might however check the timestamps of -shm and -wal files.

In SQLite, the Write-Ahead Logging (WAL) mode is a method of handling transactions that offers better performance compared to the traditional rollback journal mode. However, there are some considerations regarding data loss when using the WAL mode in SQLite.

WAL mode is designed to provide improved concurrency and performance by allowing multiple readers to access the database simultaneously while still allowing a single writer to perform updates. Transactions in WAL mode are first written to a separate WAL file, and then the changes are eventually merged into the main database file during a checkpoint process.

While WAL mode offers benefits, there are situations where data loss can occur:

Abrupt Shutdown

If there’s an abrupt shutdown, such as a power failure or a crash, there’s a risk of losing transactions that were in the process of being committed to the database. This is because not all changes may have been synced from the WAL file to the main database file at the time of the shutdown.

Corruption of WAL File

If the WAL file becomes corrupted, it could result in data loss. Corruption might occur due to hardware or software issues.

Disk Failure

If the disk storing the WAL file experiences a failure, the uncommitted transactions in the WAL file could be lost.

To mitigate the risk of data loss while using WAL mode, it’s important to:

• Regularly perform checkpoints to ensure that the changes in the WAL file are flushed to the main database file.
• Use proper backup and recovery procedures to maintain a backup of your database in case of unexpected data loss.

In summary, while the WAL mode in SQLite offers performance benefits, there is a potential risk of data loss in certain scenarios. It’s essential to manage the database properly, perform regular checkpoints, and implement reliable backup and recovery strategies to minimize the impact of these risks.

Sqlite Database File Modification Time Not Changed

It’s possible for the modification time of an SQLite database file to not change even when modifications are made to the database. This behavior can occur due to the way SQLite handles its transactions and file updates. SQLite uses a “write-ahead logging” mechanism, where changes are written to a separate log file before being applied to the main database file. This helps maintain the integrity of the database in case of crashes or other failures.

The main database file might not change its modification time because of this asynchronous nature of writing changes to the log and then applying them to the main database. This behavior is often observed, and it’s considered normal for SQLite databases.

If you need to track modifications to an SQLite database, relying solely on the modification time of the database file might not be accurate. Instead, you could consider using triggers or timestamps within your database schema to keep track of when specific changes occur.

If you’re working on a program that uses SQLite and you’re concerned about tracking changes, you might want to implement a custom mechanism to monitor and log modifications, rather than relying solely on the modification time of the database file.

–EOF (The Ultimate Computing & Technology Blog) —