SQLite Learning Note (ix):P ager module

Overview

through the analysis of the previous article, we know where the pager module is located throughout SQLite. It is the core module of SQLite, which plays a number of important roles. As a transaction manager, it implements the acid characteristics of transactions through concurrency control and failback, is responsible for the atomic commit and rollback of transactions, as a page manager, it handles reading and writing data pages from a file, and performs file space management work; As a log manager, it is responsible for writing logging to the log file, and as a lock manager, It ensures that transactions are locked for data files before accessing the data page, enabling concurrency control. Essentially, the pager module implements the persistence of storage and the atomicity of transactions. From Figure 1 we can see that the pager module consists of 4 sub-modules: Transaction management module, lock management module, log module and cache module. The implementation of the transaction module relies on the other 3 sub-modules. Therefore, the core function of the pager module is done by the cache module, log manager and lock manager. The tree module is upstream of the pager module, and the tree module needs to create a pager object to manipulate the file through the pager object before accessing the data file. The pager module uses pager objects to track file lock-related information, log status, database status, and so on. For the same file, a process may have more than one pager object, and the objects are independent of each other. For shared cache mode, there is only one pager object per data file, and all connections share this pager object.

Figure 1

Cache module

The cache management mentioned here is actually the page cache module. When an application accesses a database file, the pager module is cached in blocks and each connection has its own unique pager module, so each connection has its own unique cache.

1. Cache Lock Status

the pager module is in the No_lock state when the page cache of the file is initialized. When the Btree module first calls Sqlite3pagerget to read a page from a data file, the pager state transitions to the Shared_lock state. When the tree module calls Sqlite3pagerunref to release the page, the pager state is back to the No_lock state. When the REE module first invokes the Sqlite3pagerwrite access page, the pager state becomes Reserved_lock state. Note that the page that calls Sqlite3pagerwrite access must be the page that was previously read, and the pager state is converted from Shared_lock to Reserved_lock state. When the pager module transitions to the Execlusive_lock state before writing to the data file page, the pager module returns to No_ when the transaction commits sqlite3btreecommitphasetwo or rolls back Sqlite3pagerrollback Lock state.

2. Cache Organization

As shown in 2, the cache is actually made up of a hash table and multiple linked lists. When the pager module accesses the page, it first takes the page number as key, finds it in the hash table, and quickly determines whether the desired page is cached. When a hash table is created, if more than one page number is mapped in the same bucket, it is connected by a chain link. In addition to the hash table, the cache organization includes an LRU linked list and a dirty linked list, respectively, for cache substitution and cache brush dirty.

Figure 2

3. Caching policies

SQLite is not like some databases have prefetching mechanism, it may be for the sake of simplicity, or because SQLite is mainly used for end devices, the cache itself is relatively small. Therefore, the cache is read from the file only if the upper module requires the specified page. Because the cache is certain, and generally less than the database file capacity, there must be a cache substitution problem. SQLite uses the LRU algorithm, basically the mainstream relational database uses this algorithm. LRU (Leastrecent used), which predicts access to future pages by accessing the page over a period of time. This means that if a page is now accessed, it can be thought that the page is likely to be accessed again, and if a page has not been accessed for a long time in the past, it can be considered that the page will not be accessed for a certain period of time. Then, when the cache pool is full, select the least recently visited page replacement. If the page you choose to replace is a dirty page, you will need to write the replaced page to the data file before replacing the cache (the old-page that corresponds to the dirty page need to be brushed into the log file first). When we know that the dirty page in the cache is actually written to the file, then when does the dirty page in the cache brush the disk? SQLite does not provide a brush dirty page interface, so users cannot actively trigger the Brush page (write datafile) operation, which is triggered by the pager module under certain circumstances. There are two main cases: the number of cached page has exceeded page_size, and the other is that the transaction is in the process of committing.

4. Core processes

The process of reading a page (assuming page number is P)
(1). on page The cache looks for the
page number, search in the hash table, navigate to the specified bucket, and then compare whether the page is needed by Pghdr1.pnext. If found, the Pghdr.nref is added 1 and the page is returned to the upper calling module.
(2). If not found in page cache, get an idle slot, or create a new slot directly, As long as you do not exceed the slot threshold Pcache1.nmax.
(3). If no slots are available, select a slot that can be reused (the page that corresponds to the slot needs to be freed, via the LRU algorithm)
Span style= "FONT-SIZE:16PX;" > (4). If you choose to reuse a slot that corresponds to a page that is dirty, write the page to a file (for Wal, dirty pages are written to the log file before the dirty page is brushed)
(5). Load page

The process of updating the page
This assumes that the page has been read into memory. Before the tree module writes data to the page, it needs to call the Sqlite3pagerwrite function so that a page becomes writable, otherwise the pager module does not know that the page needs to be modified. The pager module adds a reserved lock to the data file and creates a log file. If the lock fails, a sqlite_busy error is returned. It copies the page's original information to the log file, and if the page already exists in the log file, the copy action is not made, but the page is marked as dirty. When the page is written to the file, the dirty tag is cleared.

Log Manager

1. Write a log policy

There are two main ways of database logging: The first is Wal (Write aheadlogging) and the other is Shadow paging (Shadow paging). SQLite has implemented these two kinds of logging methods to ensure the acid characteristics of transactions, can be controlled by the parameter Journal_mode log mode, by default, the use of shadow paging technology. In shadow paging mode, each page saves only one copy of the log file, regardless of how many times the page has been modified. Log file, only the original page of the transaction before the beginning of the information, to recover, only need to take advantage of the page in the log file to overwrite. For newly generated pages, it is not recorded in the log, but the number of the data file page when the log header records the start of a transaction, only the data files need to be truncated when recovering, and no new page data is needed.

2. Shadow Paging
(1). Writes old-page to the log file and Fsync
(2). Modify the log header, update the number of log records, and Fsync, this value is initially 0
(3). Get execlusive lock on the data file, and if there is a read transaction at this time, the sqlite_busy error is reported
(4). Writes dirty-page to the log file and marks the cache as clean, indicating that it can be reused if the cache is full, the write datafile operation is required, and the pager module does not commit the transaction because the user has not initiated the transaction submission.
The pager module repeats the above 1,2,3,4 points until the transaction commits. To prevent transactions from being read to dirty data before the transaction is committed, the execlusive lock is required on the file when the dirty page is being brushed, and the execlusive lock is not released until the transaction is committed, causing all other read and write transactions to be blocked in this case. As a result, major events can reduce overall concurrency performance.

Lock Manager

SQLite's concurrency control is implemented by blocking, which guarantees the acid of the transaction based on the two-phase lock protocol. SQLite's concurrency control relies on file locks to achieve mutual exclusion by locking a specific area of a file. SQLite mainly contains 4 kinds of locks, shared_lock (Shared Lock), (reserved_lock) reserved lock, (Pending_lock) pending lock and (Exclusive_lock) exclusive lock, where shared lock and exclusive lock in the same area of the file. Reserved_lock is mainly used to write and write mutually exclusive, Pending_lock is mainly used for reading and writing mutual exclusion, and has the effect of delaying mutual exclusion. About the lock concurrency can be seen in the SQLite blocking mechanism.

Figure 3

Key interfaces

Sqlite3pagercommitphaseone//COMMIT TRANSACTION First stage: File modification counter 1, swipe log file into disk, and swipe the dirty page of transaction modification into disk.
sqlite3pagercommitphasetwo//COMMIT TRANSACTION Second stage: Delete log file, release lock
sqlite3pagerrollback//ROLLBACK TRANSACTION: ROLLBACK TRANSACTION in data file modification, downgrade lock to shared lock, all cached pages revert to state before modification, delete log file.
Pcache1resizehash "Extended hash"//MAX Cache 2000 PAGE,LRU linked list, insert team header, delete from end of team
Setsharedcachetablelock//table-lock Interface
PAGERLOCKDB//File Lock interface
wallockshared wal//File share read lock interface
wallockexclusive wal//File exclusive lock interface
Sqlite3pageracquire//Get a page
readdbpage//Read page
Pcache1fetchnomutex//Find cache
pcache1fetchstage2//Add Cache
Pcache1removefromhash//Remove Cache

Reference documents

SQlite Database System Design and implementation

SQLite Learning Note (ix):P ager module