File Locking and Concurrency Mechanism in SQLite Version 3.0
A new file locking and concurrency mechanism has been introduced in SQLite version 3.0, to overcome writer starvation problem. Let us discuss a little bit about the problem. In earlier version, 2.0 of SQLite there may have been the case that no more active readers will be there even if many processes are reading from the database. And if there is, then there should at least be one read lock on the database. No other process would be able to make alteration to the database, the reason behind which is the hard to acquire write lock on the process. This situation is called “writer starvation”.
In version 3.0, the new mechanism allows atomic commits of transaction including database files.
The locking and concurrency mechanism are controlled by pager module. It is responsible for making ACID (Atomic, Consistent, Isolated and Durable) properties. It takes care of changes made to the database either to the database or not. In addition to that it also handles processes and their accessibility to the database. The pager provides memory cache of the disk file contents
READ MORE: How To Backup And Restore Database In SQL Server
According to the page point of view, database is a single file of uniform-sized blocks. Each block is called a “page” and is sized 1024 bytes. The pages are numbered sequentially starting with 1, like page 1, page 2 and so on. Other encoding is handled by higher layers of the library. The pager communicates or interacts with the OS using few modules like; os_unix.c, os_win.c etc. These provide uniform abstraction for OS services. The pager module also maintains separate threads or processes.
Locking: If we talk about a single process, the database file can be in one of the following file locking states:
Unlocked: This is the default state where no locks are held on the database and neither read or write can occur on it. Other internally cached data is considered to be suspected and is verified against the database before being used.
Shared: In this case the database can be read but not written upon. In this lock there can be multiple simultaneous readers because any number of processes can hold shared locks at the same time. If any shared lock is active then no other process or thread is allowed to write to the database.
Reserved: In this case the process plans to write on the database file but that it is just reading the file at that moment. Multiple shared locks can coexists with a single reserved lock because only a single reserved lock can remain active at a time.
Pending: In this lock, the process holding back the lock, wants to write on the database as far as possible. It also looks forward for shared locks to release from the database so that it can acquire exclusive lock. Until the pending lock is active, no shared locks are permitted to the database. So existed shared locks are continued.
Exclusive: Exclusive lock is used to write to the database file. Only one exclusive lock is allowed at a time and none other is allowed to acquire the lock on the same file holding on to the exclusive lock. To maximize the concurrency, SQLite works on the minimization of the time consumed for exclusive locks to be held.
The OS interface layer understands all the locking mechanism and pager module follows only four locking states. It doesn’t support pending lock because of its path being temporary to the Exclusive lock.