Atomic Commit in Multiple Files Of SQLite Database

Introduction to Atomic Commit

SQLite has become the most favorite database management system of developers, as it supports many applications. The smartphone era became a success because of this lightweight database. So far, we have heard of SQLite database from many sessions. Now, let us discuss the facts on the atomic commit in SQLite. What does atomic commit specify? What does the term mean?

“Atomic Commit” represents the transactions of the database that are either committed or uncommitted.It is an important feature of the SQLite database, the read or write of the transaction is recorded. The hardware serializes write operation to the disk and takes few amount of time for being committed. Therefore, cannot be called as an instant or parallel operation. Nevertheless, the logic of atomic commit is that transitional changes will be written to the database.

Note: Even if there is power failure or any interruption by OS, the transaction appears atomic. To implement atomic commit, “Rollback Journal” is the default method. First, let us see what is the procedure of this method. The session also covers about the atomic commit in multiple files of the SQLite database.

What is Rollback Journal?

Rollback journal is the method that helps SQLite for atomic commit as well as for rollback. To separate rollback journal file, the replica of the original database information is copied before the changes are made to the file. At the time of crash, the content stored in rollback journal file is restored for the original information. If the file is deleted, commit occurs and marks as the end of the transaction. The atomic commit takes on both single as well as multiple files. Here, let us see the techniques of performing commit on multiple files.

Atomic Commit On Multiple Files

Obtaining Atomic commit on multiple files are not that easy as it works for the single database file.

Different Rollback Journals

In case of multiple files, there are separate rollback journals for each database as shown in the figure and the database is reserved with locks separately.

The blue shade stands for the original content and yellow represents new content. As said, the original content will be written to the journal file and changes are not made to the content.

Master Journal File Creation

Creating master file is the next process. Here, the name of the master file is same as the original file and is a temporary file. Actually, the master file does not contain any original file; it contains the pathname of rollback file. Once when the master file is created, the content gets flushed to disk.

Master file is created only when the database is not modified by the committing transaction. Or can be said that the file is created when single transaction is processed against multiple by ATTACH DATABASE statement.If there is no master journal file, it means transaction committed will be atomic but changes will not be updated. The next steps are updating rollback journal header and database file. Let us see what happens in that.

Updating Rollback Journal Header

After the creation of master journal file, the full pathname of master file should be recorded to the header of rollback journal. Then, the content of rollback file is flushed before and after the filename is written into rollback journal file header.

Update Database File

Once when the journal files have been flushed, updating database files can be done safely. Before doing the write operation, lock should be provided to the database file.Once if the changes are made, they are to be flushed to the disk in-order to preserve from failure.

Providing the lock is similar to the exclusive lock provided in the single database. It takes place in two steps, first ‘pending’ lock must be provided. Pending lock idea is to prevent writer starvation since there will be many processes trying to read database file. It is this pending lock then,step up into an exclusive lock.

Delete Master File

This is the step where the master file is been deleted. The deletion of the master file is similar to that takes place when the rollback journal file is deleted. The deletion process is however not atomic. At this point all the transaction i.e. the multi-file commits. In case of a system crash, the process or transaction will not be roll backed when a system reboot occurs, no matter there is a rollback file. You will get a clear picture from the diagram.

Clean the Rollback Journals

The exclusive lock given is removed and each rollback journal file is also deleted.

Once the lock is removed, it can access the database file again. The cleaning up process of the rollback file is similar to the one of single database file which has already been described in the single file atomic commit early. Since the transactions are already committed, there is no timing or order for the deletion or unlocking. After going through the above session, hope all have got an idea of the atomic commit in multiple files of SQLite database. you can also use SQLite file viewer for quickly analysis of these file.