SQLite Tutorial (12): Detailed lock and concurrency control _sqlite

First, 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 that contains data modifications, the module ensures that all data modifications are committed or all are rolled back. At the same time, the module also provides memory cache features for some disk files.
In fact, the Pager_module module does not care about the details of database storage, such as B-tree, encoding, indexing, and so on, it simply treats it as a single file consisting of a uniform size (usually 1024 bytes) of data blocks, each of which is called a page. The starting number for the page in this module is 1, that is, the index value of the first page is 1, and then the page number and so on.

Second, file Lock:

In the current version of SQLite, the file lock status is mainly provided in the following five ways.
1). Unlocked:
The file does not hold any locks, that is, no read or write operations exist for the current database. Other processes can perform arbitrary 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 to. At the same time, any number of processes can hold shared locks on the same database, so read operations are concurrent. In other words, an operation that has a database file write is no longer allowed if one or more shared locks are active.
3). RESERVED:
If a process is going to write in the current database at some point in the future, but just read the data from the database, then we can simply understand that the database file already has a reserved lock at this time. When a reservation lock is active, the database can have only one or more shared locks, that is, only one reserved lock and multiple shared locks exist at the same time in the same database. This type of lock in Oracle is called a pre write lock, and the difference is that the granularity of the lock in Oracle can be refined to a table or even to a row, so the effect of this kind of lock on concurrency in Oracle is not as large as in SQLite.
4). PENDING:
The pending lock means to say, while a process is planning to write on the database, but there are many shared locks (read operations) in the database, the write operation must be in a waiting state, waiting for all shared locks to disappear, while new read operations will no longer be allowed. In order to prevent the writing lock hunger phenomenon occurs. During this wait, the database file has a lock status of pending, and after all the shared locks have disappeared, the pending lock state's database file will enter the exclusive state after acquiring an exclusive lock.
5). EXCLUSIVE:
Before performing a write operation, the process must first obtain an exclusive lock on the database. However, once an exclusive lock is possessed, no other lock type can coexist with it. Therefore, in order to maximize concurrent efficiency, SQLite will minimize the total amount of time that exclusive locks are held.

Finally, compared with other relational databases, such as MySQL, Oracle, and so on, all the data in the SQLite database is stored in the same file, while it only provides coarse-grained file locks, SQLite is unmatched in terms of concurrency and scalability, and other relational databases. This shows that SQLite has its own application scenario, as mentioned in the opening of this series, the interchangeability between it and other relational databases is very limited.

Third, roll back log:

When a process wants to change the database file, it first logs the unchanged contents to the rollback log file. If a transaction in SQLite is attempting to modify data in more than one database, then each database will generate a rollback log file of its own to record changes to its own data, while at the same time generating a primary database log file to coordinate multiple database operations. The file name of each database rollback log file is included in the primary database log file, and the file name information for the primary database log file is also included in each rollback log file. However, for rollback log files that do not require a primary database log file, information about the primary database log file is preserved, except that the value of the information is now empty.
We can treat the rollback log as a "hot" log file because it exists to restore the consistency state of the database. When a process is updating a database, the application or OS suddenly crashes, so that the update operation is not completed successfully. So we can say that "hot" log only in exceptional conditions will be generated, if everything is very smooth, the file will never exist.

Iv. Data writing:

If a process wants to perform a write operation on the database, it must first acquire a shared lock and then acquire the reserved lock after the shared lock is acquired. Because preserving a lock indicates that the process will perform a write operation at some point in the future, only one process can hold a reserved lock at the same time, but other processes can continue to hold shared locks to complete the data read operation. If the process that is performing the write operation cannot acquire a reserved lock, this will indicate that another process has acquired the reserved lock. In this case, the write operation will fail and immediately return the SQLITE_BUSY error. After the reserved lock is successfully acquired, the write process creates a rollback log.
Before any changes are made to any data, the write process writes the original contents of the page to be modified into the rollback log file, however, the pages that change the data are not written directly to the disk file at first, but remain in memory so that other processes can continue to read the data in the database.
Either because the cache is full in memory, or if the application has committed a transaction, the write process will eventually update the data to the database file. Before this, however, the write process must ensure that no other processes are reading the database, and that the data in the rollback log is physically written to the disk file as follows:
1. Ensure that all rollback log data is physically written to the disk file so that the database can be restored to a consistent state when a system crash occurs.
2. Get the pending lock, and then acquire the exclusive lock, if the other process still holds the shared lock, the write thread will have to be suspended and wait until those shared locks disappear before the exclusive lock can be obtained.
3. Write the modified page in memory to the original disk file.
If the reason for writing to the database file is because the cache is full, the write process will not commit immediately, but continue to modify the other pages. However, before the subsequent modifications are written to the database file, the rollback log must be written to disk again. Also note that the exclusive locks acquired by the write process must be held until all changes are committed. This also means that the data is first flushed to the disk file until the transaction is committed, and the other process cannot access the database.
When the write process prepares to commit, the following steps are followed:
4. Acquire an exclusive lock while ensuring that all changes in memory are written to the disk file.
5. Physically write changes to all database files to disk.
6). Delete the log file. If a system failure occurs before the deletion, the process will still be restored based on the hot log the next time the database is opened. Therefore, only after the log file has been successfully deleted can we assume that the transaction completed successfully.
7. Remove all exclusive and pending locks from the database file.
Once the pending lock is freed, other processes can begin to read the database again.
If a transaction contains more than one database modification, its submission logic will be more complex, as in the following steps:
4. Ensure that each database file already holds an exclusive lock and a valid log file.
5. Create the primary database log file, and write the file name of the rollback log file for each database to the primary database log file.
6. The file name of the primary database log file is then written to the specified location of each database rollback log file.
7. Persist all database changes to the database disk file.
8. Deletes the primary log file, and if a system failure occurs before the deletion, the process will still be restored based on the hot log the next time the database is opened. Therefore, only after the master log file has been successfully deleted can we assume that the transaction completed successfully.
9. Deletes individual log files for each database.
10. Remove exclusive and pending locks from all databases.

Finally, in SQLite2, if more than one process is reading data from the database, that is, the database always has a read operation occurring, that is, at every moment the database holds at least one shared lock, which will cause no process to perform the write operation, Because it is impossible to acquire a write lock when the database holds the read lock, we call it "write hunger." In SQLite3, the use of pending locks effectively avoids the "write hunger" scenario. When a process holds a pending lock, a read operation that already exists can continue until its normal end, but the new read operation will no longer be accepted by the SQLite, so after all the read operations have been completed, Processes that hold pending locks can be activated and attempt to further acquire exclusive locks to complete data modification operations.

five, SQL-level transaction control:

SQLite3 does make some subtle changes to the lock and concurrency controls on the implementation, especially for the SQL language level features of the transaction. By default, SQLite3 puts all SQL operations in Antocommit mode so that all modifications to the database are automatically committed after the execution of the SQL command is completed. In SQLite, the SQL command "BEGIN TRANSACTION" is used to explicitly declare a transaction, that is, subsequent SQL statements are not automatically committed after execution, but rather wait until the SQL command "commit" or "ROLLBACK" is executed to consider committing or rolling back. It can be inferred that after the BEGIN command is executed, no lock of any type is acquired immediately, a shared lock is obtained when the first SELECT statement is executed, or a reserved lock is obtained when the first DML statement is executed. As for exclusive locks, only when data is written to disk from memory will the exclusive lock be held until the transaction commits or rolls back.
If multiple SQL commands are executed at the same time as the same database connection, the autocommit will be deferred until the last command completes. For example, if a SELECT statement is being executed, during the execution of this command, all retrieved row records need to be returned, if the thread that processes the result set is temporarily suspended and waiting for the needs of the business logic, and other threads may be performing an insert on that database at this time, Update or delete commands, all of the data modifications made by these commands must wait until the select retrieval is complete before being submitted.

This is the last blog in the series on SQLite theory and application, and will post two blogs about how to use SQLite programming, which will also contain four typical application code examples, and hope you'll continue to keep your focus.