Sqlite File Locking and Its Different States

Introduction To Sqlite File Locking

For improving Sqlite concurrency level, a new concept of Sqlite file locking and journaling is being infixed. It also introduces processing that enables atomic commits and that too with more than one database file.

Basically, the job of Sqlite concurrency and Sqlite file locking is performed by the pager module that does its best to make a transaction pass the ACID (Atomicity, Consistency, Isolated, and Durable) property. In order to process and implement the Isolation property of the transaction, the pager uses file locking mechanism. It helps in concurrent access of the database by multiple threads or processes.

The pager considers that the database comprises of a file that has fixed size blocks wherein an individual block is a page of 1024 bytes. These pages are numbered, starting with one and thus 1024 bytes at the start is considered Page 1 and this continues depending upon the number of pages. However, rest of the encoding details are managed by the upper layers of the library. If we talk about a single thread, then a locked database file can exist in following states:

UNLOCKED: Here, a database has no lock and this is the default sate. In this situation, the database cannot be read and neither there is scope to write to it. Internal data caching is considered for verification and thus other threads are allowed to read/write the database as per the state of lock.

SHARED: In this case, the database can be read but there is limitation to write upon it. At a time, multiple threads are allowed to have shared lock and thus it can be estimated that at a single point of time, there can be multiple readers of the database. But if shared lock is active, no process is permitted to write on the database.

RESERVED: This means that the thread is reading from the database and will write to it at some point in the coming time. At a time, there can only be single reserved lock on the database but the reserved lock can have multiple shared locks.

PENDING: This means that a thread is waiting to write to the database and is waiting for shared lock to be cleared so that the thread can have Exclusive lock. If a database file is locked with pending lock, then no new Shared lock can be assigned to the database.

EXCLUSIVE: If Exclusive lock on the database exist, then no other lock can exist on it. In order to ensure that Sqlite concurrency is maintained, a specific time is defined for which a process can held the Exclusive lock.

All the Sqlite file locking states described above are tracked by the Operating System layer of Sqlite architecture. However, the pager module tracks only 4/5 locks and the one that it does not track is the Pending lock.