SQLite tutorial (12): detailed explanation of lock and concurrency control, and detailed description of sqlite

SQLite tutorial (12): detailed explanation of lock and concurrency control, and detailed description of sqlite

I. 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 transactions that contain data modifications, this module ensures that all or all data modifications are committed or rolled back. At the same time, this module also provides some Disk File Memory Cache functions.
In fact, the pager_module does not care about the details of database storage, such as B-Tree, encoding method, and index. It only treats it as a unified size (usually 1024 bytes) data blocks constitute a single file, each of which is called a page ). In this module, the start Number of the page is 1, that is, the index value of the first page is 1, and the subsequent page number is similar.

Ii. file lock:

In the current version of SQLite, the following five methods are provided.
1). UNLOCKED:
The file does not hold any lock, that is, the current database does not have any read or write operations. Other processes can perform any read/write operations on the database. This status is the default status.
2). SHARED:
In this status, the database can be read but cannot be written. At the same time, there can be any number of processes holding shared locks on the same database, so read operations are concurrent. In other words, as long as one or more shared locks are active, database file write operations are no longer allowed.
3). RESERVED:
If a Process intends to perform write operations in the current database at a certain time in the future, but only reads data from the database, we can simply understand that database files already have a retention lock. When the retention lock is active, the database can only have one or more shared locks, that is, the same database can only have one or more shared locks at the same time. In Oracle, such locks are called pre-write locks. The difference is that the granularity of Oracle locks can be refined to tables or even rows, therefore, the impact of the lock on concurrency in Oracle is not as large as that in SQLite.
4). PENDING:
The PENDING lock means that a process is planning to perform write operations on the database, but there are many shared locks (read Operations) in the database ), the write operation must be in the waiting state, that is, wait until all the shared locks disappear. At the same time, new read operations will no longer be allowed to prevent the hunger of write locks. During the waiting period, the lock status of the database file is PENDING. After all the shared locks disappear, the database file in the PENDING lock status enters the EXCLUSIVE status after obtaining the EXCLUSIVE lock.
5). EXCLUSIVE:
Before executing the write operation, the process must first obtain the exclusive lock of the database. However, once an exclusive lock exists, no other lock type can coexist with it. Therefore, to maximize the concurrency efficiency, SQLite will minimize the total amount of time that exclusive locks are held.

It should be noted that, compared with other relational databases, such as MySQL and Oracle, all data in the SQLite database is stored in the same file, it only provides coarse-grained File locks. Therefore, SQLite is incomparable with other relational databases in terms of concurrency and scalability. It can be seen that SQLite has its own application scenarios. As mentioned in the beginning of this series, it is quite limited to interconnectivity with other relational databases.

Iii. rollback log:

When a process wants to change the database file, it first records the previous content that has not changed to the rollback log file. If a transaction in SQLite is trying to modify data in multiple databases, each database will generate a rollback log file of its own to record the changes of its own data separately, at the same time, a log file of the primary database is generated to coordinate multiple database operations. The log file of the primary database contains the file name of the rollback log file of each database, each rollback log file also contains the file name information of the log file of the primary database. However, for rollback log files that do not require the primary database log file, the information of the primary database log file is retained, but the value of this information is blank.
We can regard rollback logs as "HOT" log files because they exist to restore Database Consistency. When a process is updating the database, the application or OS suddenly crashes, so that the update operation cannot be completed smoothly. Therefore, we can say that "HOT" logs are generated only when exceptions occur. If everything goes smoothly, the file will never exist.

Iv. Data Writing:

If a process wants to perform write operations on the database, it must first obtain the shared lock and then obtain the reserved lock after the shared lock is obtained. Because the retention lock indicates that the process will perform write operations at a certain time point in the future, only one process can hold a retention lock at the same time point, however, other processes can continue to hold a shared lock to read data. If the process to perform the write operation cannot obtain the lock, it indicates that another process has obtained the lock. In this case, the write operation fails and the SQLITE_BUSY error is returned immediately. After the lock is successfully obtained, the write process creates a rollback log.
Before changing any data, the write process will write the original content on the page to be modified to the rollback log file. However, pages with changed data are not directly written to disk files at first, but are kept in the memory so that other processes can continue to read data from the database.
Or because the cache in the memory is full, or the application has committed the transaction, the write process updates the data to the database file. However, before that, the write process must ensure that no other process is reading the database and the data in the rollback log is physically written to the disk file. The steps are as follows:
1) ensure that all the rollback log data is physically written to the disk file so that the database can be restored to a consistent state when the system crashes.
2 ). obtain the PENDING lock and then obtain the exclusive lock. If other processes still hold the shared lock at this time, the writing thread will have to be suspended and wait until the shared lock disappears before obtaining the exclusive lock.
3) write the modification pages in the memory to the original disk file.
If the reason for writing data to the database file is that the cache is full, the write process will not submit the data immediately, but will continue to modify other pages. However, before the subsequent modification is written to the database file, the rollback log must be written to the disk again. Note 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 writing process is ready to submit, follow these steps:
4). Obtain the exclusive lock and ensure that all change data in the memory is written to the disk file.
5) Physically write the changed data of all database files to the disk.
6). Delete the log file. If a system fault occurs before the deletion, the process will continue to restore the database based on the HOT log the next time it opens the database. Therefore, the transaction can be considered successful only after the log file is successfully deleted.
7). Delete all exclusive and PENDING locks from the database file.
Once the PENDING lock is released, other processes can start to read the database again.
If a transaction contains modifications to multiple databases, the commit logic is more complex. See the following steps:
4) Make sure that each database file has an exclusive lock and a valid log file.
5). Create a log file for the primary database, and write the name of the rollback log file for each database to the log file of the primary database.
6). Then, write the file names of the log files of the primary database to the specified location for each database rollback log file.
7). persists all database changes to the database disk file.
8). Delete the master log file. If a system fault occurs before the deletion, the process will continue to recover from the HOT log the next time it opens the database. Therefore, the transaction can be considered successful only after the master log file is successfully deleted.
9). Delete the log files of each database.
10). Remove the exclusive lock and PENDING lock from all databases.

It should be noted that in SQLite2, if multiple processes are reading data from the database, that is to say, there are always read operations in the database, that is, the database holds at least one shared lock at each time point, which will lead to no process being able to perform write operations, because the database cannot obtain the write lock when holding the read lock, we call this situation "Write hunger ". In SQLite3, the PENDING lock is used to effectively avoid "Write hunger. When a process holds a PENDING lock, the existing read operation can continue until it ends normally, but the new read operation will not be accepted by SQLite, therefore, after all the existing read operations are completed, the processes holding the PENDING lock can be activated and attempt to further obtain the exclusive lock to complete data modification.

V. SQL-level transaction control:

SQLite3 has made some exquisite changes in implementation of lock and concurrency control, especially for the SQL language-level features of transactions. By default, SQLite3 puts all SQL operations in antocommit mode, so that all database modification operations 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, subsequent SQL statements are not automatically submitted after execution, instead, you need to wait until the SQL command "COMMIT" or "ROLLBACK" is executed to consider whether to submit or roll back. It can be inferred that after the BEGIN command is executed, no locks of any type are obtained immediately, but a shared lock is obtained only when the first SELECT statement is executed, or, a retained lock is obtained only when the first DML statement is executed. As for the exclusive lock, the exclusive lock can be held only when the data is written from the memory to the 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 must be returned during the command execution, if the thread processing the result set is temporarily suspended and in the waiting state due to the need of the business logic, other threads may be executing the INSERT, UPDATE, or DELETE command on the database on the connection at this time. All data modifications made by these commands must be submitted after the SELECT search is complete.

This is the last blog on SQLite Theory and Application in this series. Two blogs about how to use SQLite programming will be published later, it also contains four typical application code examples. We hope you will continue to pay attention to them.