Take A Deep Insight Into Write Ahead Logging in Sqlite

Introduction

In the last blog we discussed about Sqlite and Multiple thread now this time we will move towards and important aspect of Sqlite that is WAL files. Sqlite is currently used by a wide range of applications whether desktop based or smartphone based. The method through which it implements atomic commit operations and rollback is called a – rollback journal. With the release of version 3.7.0, Sqlite was introduced to a new option called as ‘Write-Ahead Log’, which is better known as the ‘WAL’.

The WAL File

Normally the working of rollback journal is to write the original database to a copy when in unchanged form to a different rollback file. Later, the changes that have been executed are then written to the database directly. Therefore, in case of crash or execution of ROLLBACK, the original content that is held by the rollback journal file is played to the database in order to revert it to the previous or original state, which was retained by the journal. COMMIT takes place when deletion of rollback journal happens.

ROLE Of Write Ahead Logging in Sqlite

When the above-discussed situation takes place, the role of WAL is to invert the occurrence. Therefore, preserving the original content in the DB and appending the changes to a separate WAL. This way a COMMIT can take place without having to write to the original DB. This is beneficial for readers as it allows them to continue operating on their end using the original unaltered database while on the other end, changes continue to take place at the same time to the WAL. A WAL file is capable enough to handle more than one transaction to be appended to its end.

A Deep insight into Write ahead Logging in Sqlite

There are plenty of benefits associated with the usage of a WAL file, and we have shortlisted some below. These will help you understand why using WAL over rollback journal proves advantageous:

• Write-Ahead Log acts more instantly in most of the conditions.
• Readers do not block writers and vice versa, thus, comparatively making WAL provider of additional concurrency. Therefore, both read and write can be executed simultaneously.
• Using WAL makes disk I/O operations take place more sequentially.
• As compared to a rollback journal, a WAL file uses lesser fsync() operations. Therefore, it is also less susceptible to issues that arise on system where system call for fsync() is broken.

However, there are some disadvantages associated with using WAL too. Following are some that may affect a user:

• A WAL generally needs VFS to support the primitives of shared-memory. The only exception is when WAL is without shared memory. Integrated Unix & VFSes from Windows support this. However, third party VFS extension may not offer support for custom OS.
• It is technically impossible to change page size of a DB once entered the WAL mode. This is applicable to all conditions whether by using empty DB or VACUUM or backup restoration. Change of DB page size is only possible if you are in a rollback journal mode.
• WAL file do not function on network FS thus, all processes must take place on the host machine.
• Transactions with changes taking place against more than one ATTACHED DBs are set to be atomic for every single database. However, the transactions will not be atomic across databases as a set.

Observation: Therefore, usage of WAL files does prove helpful but only for certain circumstances and not in all cases necessarily. Therefore, it is advised to use WAL files in Share-Memory mode and when processes taking place on host machine. In addition, when the transactions taking place are smaller, better results are acquired by using WAL. Moreover, they also offer relatively slower performance with applications that primarily read and rarely execute write operations. Considering, these points before using WAL mode is advised to achieve optimum results and better performance.