1. Overview:
In SQLite, the lock and concurrency control mechanisms are handled by the pager_module module, such as ACID (Atomic, Consistent, Isolated, and Durable). In a transaction containing data modifications, the module will ensure that all data modifications are submitted or all rolled back. At the same time, the module also provides memory cache functions for some disk files.
In fact, the pager_module module does not care about the details of database storage, such as B-Tree, encoding method, index, etc. It simply treats it as a single file composed of data blocks of uniform size (usually 1024 bytes), where each block is called a page. In this module, the page starts with 1, that is, the index value of the first page is 1, and the following page number is so on.
2. File lock:
In the current version of SQLite, the following five ways of file lock status are mainly provided.
1). UNLOCKED：
The file does not hold any locks, that is, there is no read or write operation in the current database. Other processes can perform any read and write operations on the database. This state is the default state.
2). SHARED：
In this state, the database can be read but cannot be written. At the same time, any number of processes can hold shared locks on the same database, so read operations are concurrent. In other words, as long as one or more shared locks are active, the database file writing operation is no longer allowed.
3). RESERVED：
If a process intends to perform a write operation in the current database at a certain moment in the future, but only reads data from the database at this time, then we can simply understand that the database file already has a reserved lock at this time. When the retained lock is active, only one or more shared locks can exist in the database, that is, only one retained lock and multiple shared locks can exist at the same time as the same database. In Oracle, such locks are called pre-write locks. The difference is that the granularity of locks in Oracle can be refined to tables or even to rows. Therefore, the impact of this lock on concurrency in Oracle is not as large as in SQLite.
4). PENDING：
PENDING lock means that a process is planning to perform a write operation on the database, but at this time there are many shared locks (read operations) in the database. Then the write operation must be in a waiting state, that is, wait until all shared locks disappear. At the same time, new read operations will no longer be allowed to prevent the occurrence of write lock starvation. During this waiting period, the lock status of the database file is PENDING. After waiting for all shared locks to disappear, the database file with the PENDING lock status will enter the EXCLUSIVE state after acquiring the exclusive lock.
5). EXCLUSIVE：
Before performing a write operation, the process must first acquire the exclusive lock of the database. However, once an exclusive lock is possessed, no other lock type can coexist with it. Therefore, to maximize concurrency efficiency, SQLite will minimize the total amount of time the exclusive lock is held.
Finally, it should be noted that compared with other relational databases, such as MySQL, Oracle, etc., all the data in the SQLite database is stored in the same file. At the same time, it only provides coarse-grained file locks. Therefore, SQLite is still incomparable to other relational databases in terms of concurrency and scalability. It can be seen that SQLite has its own applicable scenarios. As mentioned in the beginning of this series, the interchangeability between it and other relational databases is still very limited.
3. Roll back the log:
When a process wants to change a database file, it first records the content that has not changed before it is recorded into the rollback log file. If a transaction in SQLite is trying to modify the data in multiple databases, then each database will generate its own rollback log file, which is used to record its own data changes separately. At the same time, a main database log file is also generated to coordinate multiple database operations. The main database log file will contain the file names of each database rollback log file, and each rollback log file will also contain the file name information of the main database log file. However, for rollback log files that do not require the primary database log file, the information of the primary database log file will also be retained, but the value of the information is empty at this time.
We can treat the rollback log as a "HOT" log file because it exists to restore the consistent state of the database. When a process is updating the database, the application or OS suddenly crashes, so the update operation cannot be completed smoothly. So we can say that the "HOT" log is generated only under exceptional conditions, and the file will never exist if everything goes very well.
4. Data writing:
If a process wants to perform a write operation on the database, it must first acquire the shared lock and then acquire the reserved lock after the shared lock is acquired. Because the reserved lock indicates that the process will perform a write operation at a certain time in the future, only one process can hold a reserved lock at the same time, but other processes can continue to hold a shared lock to complete the data reading operation. If the process to perform the write operation cannot acquire the reserved lock, this will indicate that the other process has acquired the reserved lock. In this case, the write operation will fail and an SQLITE_BUSY error will be returned immediately. After successfully obtaining the retain lock, the write process creates a rollback log.
Before making any data changes, the write process will write the original content in the page to be modified into the rollback log file first. However, the pages where these data have changed will not be directly written to the disk file at first, but will be kept in memory, so that other processes can continue to read the data in the database.
Either because the cache in memory is full, or the application has submitted a transaction, the write process finally updates the data to the database file. However, before this, the write process must ensure that no other process is reading the database, and the data in the rollback log is indeed physically written to the disk file. The steps are as follows:
1). Make sure that all rollback log data is physically written to disk files so that the database can be restored to a consistent state in the event of a system crash.
2). Acquire the PENDING lock and then acquire the exclusive lock. If other processes still hold the shared lock at this time, the write thread will have to be suspended and wait until those shared locks disappear before they can obtain the exclusive lock.
3). Write the modified pages held in memory into the original disk file.
If the reason for writing to the database file is that the cache is full, the write process will not submit immediately, but will continue to modify other pages. But before the next modification is written to the database file, the rollback log must be written to disk again. It should also be noted that the exclusive lock obtained by the write process must be held until all changes are committed. This also means that other processes cannot access the database from the first time the data is refreshed to the disk file until the transaction is committed.
When the write process is ready for submission, the following steps will be followed:
4). Acquire the exclusive lock while ensuring that all memory changes are written to the disk file.
5). Physically write all database files' changing data to disk.
6). Delete the log file. If a system failure occurs before deletion, the process will still perform recovery operations based on the HOT log the next time the database is opened. Therefore, we can only consider that the transaction completed successfully after the log file is successfully deleted.
7). Delete all exclusive locks and PENDING locks from the database file.
Once the PENDING lock is released, other processes can start reading the database again.
If a transaction contains modifications to multiple databases, its commit logic will be more complex, see the following steps:
4). Make sure that each database file already has an exclusive lock and a valid log file.
5). Create a primary database log file, and write the file name of the rollback log file of each database to the primary database log file.
6). Then write the file name of the main database log file to the specified location of each database rollback log file.
7). Persist all database changes into the database disk file.
8). Delete the main log file. If a system failure occurs before deletion, the process will still perform recovery operations based on the HOT log the next time the database is opened. Therefore, we can only consider that the transaction completed successfully after the main log file is successfully deleted.
9). Delete the respective log files of each database.
10). Remove exclusive locks and PENDING locks from all databases.
Finally, it should be noted that in SQLite2, if multiple processes are reading data from the database, that is, the database always has read operations, that is, at each moment, the database holds at least one shared lock, which will result in no process being able to perform write operations, because the write lock cannot be acquired when the database holds the read lock. We call this situation "write hunger". In SQLite3, the use of PENDING lock effectively avoids the occurrence of "write hunger" situation. When a process holds a PENDING lock, the existing read operation can continue until it ends normally, but the new read operation will no longer be accepted by SQLite. Therefore, after all existing read operations have been completed, the process holding the PENDING lock can be activated and attempts to further acquire the exclusive lock to complete the data modification operation.
5. SQL-level transaction control:
SQLite3 has indeed made some exquisite changes to lock and concurrency control in its implementation, especially for transactions, the SQL language-level feature. By default, SQLite3 will place all SQL operations in antocommit mode, so that all modification operations for the database will be automatically submitted after the SQL command is executed. In SQLite, the SQL command "BEGIN TRANSACTION" is used to explicitly declare a transaction, that is, the subsequent SQL statements will not be automatically submitted after execution, but need to wait until the SQL command "COMMIT" or "ROLLBACK" is executed before considering whether to submit or rollback. From this, it can be inferred that no lock of any type is obtained immediately after the BEGIN command is executed, but a shared lock is obtained only when the first SELECT statement is executed, or a reserved lock is obtained only when the first DML statement is executed. As for exclusive locks, exclusive locks cannot be held only when data is written from memory to disk until the transaction is committed or rolled back.
If multiple SQL commands are executed in the same database connection at the same time, autocommit will be delayed until the last command is completed. For example, if a SELECT statement is being executed, all retrieved row records need to be returned during this command execution. If the thread processing the result set is temporarily suspended and is in a waiting state due to business logic needs, while other threads may be executing INSERT, UPDATE, or DELETE commands on the database on the connection, then all data modifications made by these commands must wait until the SELECT search is completed before being submitted.
This is the last blog on SQLite theory and application in this series. Two blogs on how to use SQLite programming will be published later. They will also include four typical application code examples. I hope everyone will continue to pay attention.