SQLite Learning Notes (vii) && transaction processing

when it comes to business, it must refer to acid, the atomicity, consistency, isolation, and persistence of the so-called transactions. For a database, the acid characteristics of transactions in both normal and abnormal conditions are usually ensured through concurrency control and recovery methods. SQLite is no exception, although simple, still has its own concurrency control and recovery mechanism. SQLite Learning Note (v) &&sqlite blocking mechanism has already talked about the principle of some locking mechanism, this article will detail a transaction from the beginning, to the execution, finally to commit the process of experience, which will be interspersed with some sqlite in the lock management, cache management and log management mechanism, At the same time, in the exception of the situation (hardware and software failures, such as program exception crash, host power down, etc.), sqlite How to restore the database to the state before the transaction. This article is a lot of reference to SQLite's official documents, combined with their own understanding, I hope that the process can be clearly explained.

1. Transaction Submission
1.1 Opening a transaction
Before writing data to a database file, SQLite first needs to access the Sqlite_master table to obtain metadata information, which can be used for semantic analysis of SQL statements and to judge the legitimacy of statements. The first step in reading data from a database is to have a shared Lock on the database file. Shared lock allows multiple transactions to read a database file at the same time, but shared lock prevents the write transaction from writing data to the database file.

1.2 Reading data
After we get the shared lock, we can read the data from the database file. We assume that we don't have our page in the cache, so we need to read the page we need by reading the file. Here, the SQLite database file is essentially one size of the same page composition, by default, a page size of 1024B. Typically, we need to read several page and cache the page in the app's local cache so that the next visit does not need to be read from the file again. Here we assume that we need to read 3 page, represented by a green block.

1.3 Get reserved Lock
Before writing data to the database, SQLite needs to obtain a Reserved lock,reserved lock similar to shared lock while allowing other transactions to read the database. Reserved Lock is compatible with shared lock, but is mutually exclusive with Reservedlock, that is, only one reservedlock is allowed at the same time. Holding reserved lock means that the transaction is ready to modify the data file, because the file is not actually modified, allowing other transactions to continue reading, but not allowing other transactions to write the database operation.

1.4 Creating a log file
In SQLite, there are two kinds of log technology, the shadow paging technology and the Wal (write ahead log) technology. The shadow paging technique is the default approach for SQLite, and is based on this assumption. Before manipulating the data file, SQLite first creates a log file and writes the contents of the page to be modified to the log, preserving all the original information of the recovery transaction in this way. The most basic unit of action, whether it is a database file or a log file, is the page.

1.5 Modifying data
As mentioned earlier, SQLite modifies the data before it reads the page into the cache, so the modification modifies the data in the cache first. Because each connection has its own separate page cache, the write transaction modifies the data in its own page cache without affecting other transactions, and other transactions still read the original page data without causing dirty reads. In red indicates the change block, you can see that only the user's own cache page has become red.

1.6 Brush Log files
After the modification is complete, write the log file to disk first. This process is very important, only through the brush disk operation (Fsync) to persist the log, in order to power down, the log to recover the data page. At the same time, this action is very time consuming.

1.7 Get Exclusivelock
Now we need to write the previous modifications to the page cache to the database file for persistence purposes. Before this action, you first need to hold exclusive lock, get the lock actually contains two steps, first hold a pending lock, and then hold exclusive lock. Pending Lock allows a read lock transaction to proceed with the read operation, but does not allow the new read transaction to come in. Because the new read transaction is blocked, the number of read transactions is limited to a certain extent, but some read transactions will be executed sooner or later, the write transaction can eventually get to exclusivelock, in this way to avoid the write transaction starved of conditions.

1.8 Write changes to the data file and brush the disk
Once the Exclusivelock is held, there is only one transaction in SQLite and no other read transaction to read the file. Therefore, it is safe to write data to the data file at this time. To ensure that the write action actually falls into the disk, a brush disk action is required. As with the brush log, the data files are modified by the brush disc to ensure that the update can still be persisted, as well as the time-consuming operation. Where the red block represents the modified block, the user process space, OS buffer, and disk have all been modified.

1.9 Deleting log files
When you do this, both the log file and the data file modifications are cured to disk. If a power-down occurs before 1.8 steps, the database can be restored to its pre-transaction state because the log file has been safely dropped. Since the data file modification has been cured, we can delete the log file. Determine whether we need to rollback or commit a transaction through the presence or absence of a log file. Since the deletion of files is also a time-consuming action, SQLite optimized this, through the parameter options, you can choose to initialize the log all 0, or directly truncate the file, to improve performance.

1.10 Release Exclusive Lock
The final step is to release exclusivelock so that other transactions have the opportunity to read and write data files. Here is a problem, each connection has its own page cache, if the page cache content has been changed, and write to the file, then how other transactions are aware of, the local old-page clean up, and re-read from the file? SQLite is controlled by a counter that exists in the first page of a database file. This value is also self-increasing each time the data file is modified. When the transaction starts, the counter is read, and when the page is read, the counter is checked again for changes, and if there is a change to the transaction commit, the local cache is emptied and retrieved from the database file again.

2. Transaction rollback
Under normal circumstances, the acid characteristics of a transaction can be guaranteed through the transaction submission process described above. However, when a transaction occurs during execution, a transaction rollback is required to restore the database to the state before the transaction begins. Here's a scenario that describes the rollback process.
2.1 Failure
Assuming that the database file was written before 1.8, a power-down failure occurred. When the failure is restored, the situation might look like the picture on the right, where only some pages are written to disk, and even one page may be written only as part. As this step is performed, the logs are safely dropped, so the logs can be used for recovery.

2.2 Hot Logs
Any connection prior to the operation of the database will first determine if there is a hot log present, because the presence of a hot log means that a failure recovery may be required. The so-called hot log, refers to the need for transaction submission process failure occurred, need to use log recovery.

2.3 Rollback of unfinished operations
Before using the log for recovery, first hold exclusivelock, so as to avoid multiple connections at the same time failure recovery, after holding Exclusivelock, can start to modify the database file. SQLite reads the original data page from the log file, and then writes the data page back to the data file. Because the log file header records the size of the data file at the beginning of the transaction, SQLite uses this information to truncate the data file to its original size, keeping the file size back to the level at which the transaction started.

2.4 Deleting log files
When all the data pages in the log file have been copied to the data file, a brush disk operation is made to ensure that the modification is persisted, and the log file can be deleted. When the restore is complete, demote exclusive lock to shared lock. After this process is complete, the database completes the recovery. As the entire process is automatically completed by SQLite, the user is completely unaware. It is safe for users to manipulate data files at any time with SQLite, even in the event of an exception.

Reference documents

Https://www.sqlite.org/atomiccommit.html

SQLite Learning Notes (vii) && transaction processing