Introduction and Analysis of SQLite (II)-Design and concept (continued)

Preface: This section discusses transactions. transactions are one of the core technologies of DBMS. in the history of computer science, three scientists won the ACM Turing Award for their achievements in the database field, and Jim Gray (former Microsoft) it is because of the achievements in transaction processing that the OLTP system became popular until today. there are many transaction processing techniques involved, so you can write a book at will. here I will only discuss some principles of SQLite transaction implementation. The implementation of SQLite transactions is relatively simple compared with that of large-scale common DBMS. these contents may be more theoretical, but not difficult. They are also the basis for understanding other content. now let's start section 2-transaction.

 

 

2. Transaction)

 

2.1. Transaction Lifecycles)
There are two things worth noting between a program and a transaction:
(1) What objects are running in transactions -- this is directly related to the API.
(2) the lifecycle of the transaction, that is, when and when the transaction ends, and when it begins to affect other connections (this is important for concurrency)-This involves the specific implementation of SQLite.
A connection can contain multiple statement, and each connection has a B-tree associated with the database and a pager. Pager plays an important role in the connection because it manages transactions, locks, memory caches, and is responsible for crash recovery ). When you perform database write operations, remember the most important thing: at any time, only one connection is executed in one transaction. These answer the first question.
Generally, the life of a transaction is similar to statement. You can also manually end it. By default, the transaction is automatically committed. Of course, you can also manually submit the transaction through BEGIN .. COMMIT. The next step is the lock issue.

2.2 Lock States)
Locks are very important for implementing concurrent access, while for large-scale generic DBMS, the implementation of locks is also very complex, while SQLite is relatively simple. Generally, the duration is the same as the transaction duration. When a transaction starts, it locks, ends, and releases the lock. However, if the system crashes without the end of the transaction, the next connection to the database will handle this situation.
There are five locks in different States in SQLite, and connection is in one of them at any time. The status and lock lifecycle are displayed.

Note the following points about this graph:
(1) A transaction can start in UNLOCKED, RESERVED, or EXCLUSIVE states. By default, it starts at UNLOCKED.
(2) UNLOCKED, PENDING, SHARED, and RESERVED in the white box can exist at the same time of a database.
(3) from the gray PENDING, things become strict, meaning that the transaction wants to get the EXCLUSIVE lock (note the difference from the white box ).
Although there are so many locks, there are only two physical situations: Read transactions and write transactions.

2.3. Read Transactions)
Let's take a look at the lock state change process when the SELECT statement is executed. It is very simple: a connection executes the select statement to trigger a transaction, from UNLOCKED to SHARED. When the transaction COMMIT, it returns to UNLOCKED, that's simple.
Consider the following example (for simplicity, the pseudo code is used here ):
Db = open ('Foods. db ')
Db.exe c ('begin ')
Db.exe c ('select * FROM episodes ')
Db.exe c ('select * FROM episodes ')
Db.exe c ('commit ')
Db. close ()

Because BEGIN and COMMIT are explicitly used, the two SELECT commands are executed in one transaction. When the first exec () is executed, the connection is in the SHARED state, and then the second exec () is executed. when the transaction is committed, the connection returns from the SHARED to the UNLOCKED state, as shown below:
UNLOCKED → PENDING → SHARED → UNLOCKED
If no BEGIN or COMMIT rows exist:
UNLOCKED → PENDING → SHARED → UNLOCKED

2.4. Write Transactions)
Next we will consider writing a database, such as UPDATE. Like a read transaction, it also goes through UNLOCKED → PENDING → SHARED, but then it goes through the gray PENDING,

2.4.1, The Reserved States
When a connection writes data to the database, it changes from the SHARED state to the RESERVED state. If it gets the RESERVED lock, it means it is ready for write operations. Even if it does not write the changes to the database, it can save the changes to the cache in pager (page cache ).
When a connection enters the RESERVED state, pager starts to initialize the recovery log (rollback journal ). In the RESERVED state, pager manages three types of pages:
(1) Modified pages: Contains records Modified by B-tree, which are located in page cache.
(2) Unmodified pages: Contains records not modified by B-tree.
(3) Journal pages: This is the previous version of the modified page, which is not stored in the page cache, but written to the log before the B-tree modification page.
Page cache is very important because of its existence. A connection in the RESERVED state can actually start working without interfering with other (read) connections. Therefore, SQLite can efficiently process multiple read connections and one write connection at the same time.

2.4.2. The Pending States
After a connection is modified, the transaction is committed. The pager that executes the process enters the EXCLUSIVE state. From the RESERVED status, pager tries to obtain the PENDING lock. Once obtained, it excludes it and does not allow any other connections to obtain the PENDING lock (PENDING is a gateway lock ). Since the write operation holds the PENDING lock, no other connections can enter the SHARED state from the UNLOCKED state, that is, no connection can enter data (no new readers, no new writers ). Only connections in the SHARED state can continue to work. Writer in PENDING state will wait until all these connections release their locks, and then apply the EXCUSIVE lock to the database to enter the EXCLUSIVE state, EXCLUSIVE database (discussed here, the locking mechanism for SQLite should be clear ).

2.4.3. The Exclusive State
In the EXCLUSIVE State, the main task is to write the modified page from the page cache to the database file, which is the real place for write operations.
Before pager writes modified pages, it must first do one thing: Write logs. It checks whether all logs are written to the disk, which is usually in the Operation buffer, so pager must tell the OS to write all files to the disk, this is done by the program synchronous (implemented by calling the corresponding API of the OS.
Logs are the only method for database recovery. Therefore, logs are very important to DBMS. If the log page is not completely written to the disk and crashes, the database cannot be restored to its original state, and the database is in an inconsistent state. After the log is written, pager writes all the modified pages to the database file. Next, it depends on the transaction commit mode. If it is automatic commit, pager clears the log, page cache, and then enters UNLOCKED from EXCLUSIVE. If it is manually submitted, pager continues to hold the EXCLUSIVE lock and save the log until COMMIT or ROLLBACK.

In short, in terms of performance, the process occupies the exclusion lock as short as possible, so DBMS usually occupies the exclusion lock only when actually writing files, which can greatly improve the concurrency performance.