A detailed tutorial on SQLite database transaction processing

When it comes to business, it must refer to acid, the atomicity, consistency, isolation and permanence of the transaction. For a database, the ACID properties of transactions under normal and exceptional conditions are usually ensured through concurrency control and fault recovery methods. SQLite is no exception, although simple, still has its own concurrency control and recovery mechanism. SQLite Learning Notes (v) The &&sqlite blockade mechanism has already talked about the principles of locking mechanisms, this article will detail a transaction from the beginning, to execution, and finally to the process of submission, which will be interspersed with some of the SQLite in the lock management, cache management and log management mechanism, At the same time will be introduced in exceptional circumstances (hardware and software failures, such as program abnormal crash, host power off, etc.), sqlite How to restore the database to the state before the transaction. This article has a lot of reference to SQLite's official documents, combined with their own understanding, hope that the process can be made clear.

1. Transaction Submission

1.1 Open a transaction

Before writing data to a database file, SQLite first needs to access the Sqlite_master table to obtain metadata information, which is used for semantic analysis of SQL statements and to judge the legality of statements. Reading data from a database The first step 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 write transactions 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 the cache does not have our page, so we need to read the file to read the page we need. In this note, the SQLite database file is essentially a page of the same size, by default, a page size of 1024B. Usually, we need to read several page and cache the page in the application 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 the shared lock, while allowing other transactions to read the database. Reserved Lock is compatible with shared lock, but is mutually exclusive with Reserved lock, that is, only one Reserved lock is allowed at the same time. Holding reserved lock indicates that the transaction is ready to modify the data file, because the file has not been actually modified, allowing other transactions to continue the read operation, but not allowing other transactions to write the database operation.

1.4 Creating log files

In SQLite, there are two kinds of logging techniques, shadow paging technology and Wal (write ahead log) technology. Shadow paging is the default approach for SQLite, and the discussion behind it 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, which preserves all the original information of the recovery transaction. Whether it's a database file or a log file, the most basic unit of action is page.

1.5 Modifying data

As mentioned earlier, the page is read to the cache before SQLite modifies the data, so the modification modifies the data in the cache first. Because each connection has its own page cache, the write transaction modifies the data in its own page cache and does not affect other transactions, and other transactions still read the original page data and do not cause dirty reads. The red in the image below indicates a modified block, which can be seen only in the user's own cache page that turns red.

1.6 Brush Log file

When the modification is complete, the log file is written to disk first. This process is very important, only through the brush operation (Fsync) to the log to persist, in the case of power off, the log Restore data page. At the same time, this action is time-consuming.

1.7 Get exclusive Lock

Now we need to write the modification of page cache to the database file for the purpose of persistence. Before this action, the 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 read lock transactions to continue read operations, but new read transactions are not allowed in. Because the new read transaction is blocked, the number of read transactions is limited to a certain extent, and some read transactions will be completed sooner or later, write transactions can eventually get exclusive Lock, in this way to avoid writing a business starvation situation.

1.8 Write the modification to the data file and brush the disk

Once the exclusive Lock 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 a data file at this time. In order to ensure that the write action actually falls into the disk, you also need to do brush disk action. Like the brush log, the data file to modify the brush action is also to ensure that the power off, the update can still be persisted, the same operation is also time-consuming. The red block represents the modification block, at which point the user process space, OS buffer, and disk have been modified.

1.9 Deleting log files

In this step, the log files and data file modifications have been cured to disk. If a power off occurs before 1.8 steps, the database can be restored to the state before the transaction started because the log file has been safely dropped. Since the data file modification has been cured, we can delete the log file. By the presence or absence of the log file, we determine whether we need to rollback or commit the transaction. Since the deletion of the file is also a time-consuming action, sqlite optimization, through the parameter options, you can choose to initialize the log all 0, or directly truncated files, to improve performance.

1.10 Release Exclusive Lock

The final step is to release exclusive Lock 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 written to the file, then how other transactions perceive, their own local old-page clean, read from the file again? SQLite is controlled by a counter, This counter exists in the first page of the database file. This value also increases itself each time the data file is modified. At the beginning of the transaction, the counter is read, and when the page is read, the counter is checked again for changes, and if a change occurs, a transaction is committed, and the local cache is emptied and retrieved again from the database file.

2. Transaction rollback

Normally, the acid properties of a transaction can be guaranteed through the transaction submission process described above. However, an exception occurs during the execution of a transaction, which requires that the transaction be rolled back to restore the database to the state before the transaction started. The following assumes a situation to describe the rollback process.

2.1 Fault occurred

Suppose that before 1.8, a power failure occurred while writing the database file. When a failure is restored, the situation may look like the picture on the right, where only part of the page is written to disk, and even one page may be written only partially. Because the log has been safely dropped when you perform this step, you can recover it with the help of a log.

2.2 Thermal Log

Any connection will first determine whether a hot log exists before manipulating the database, because the presence of a hot log means that recovery may be required. The so-called thermal log, refers to the need for a transaction committed in the process of failure, need to take advantage of log recovery.

2.3 Rolling back unfinished operations

Before using the log to restore, first hold exclusive lock, so as to avoid multiple connections to fail back at the same time, hold exclusive Lock before you can start to modify the database file. SQLite reads the original data page from the log file and then writes the data page back into the data file. Because the log file header records the size of the data file at the start 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 began.

2.4 Deleting log files

When all the data pages in the log files have been copied to the data file, a brush operation is made to ensure that the modifications are persisted, and the log files can be deleted. After the restore is complete, demote exclusive lock to shared lock. After this process completes, the database completes the recovery. Because the whole process is sqlite automatic completion, the user is completely not aware. For users, it is safe to use SQLite to manipulate data files at any time, even in the event of an exception.

An example of SQLite processing transactions

Transactions are an important concept in the database, and the use of transactions can ensure data consistency and integrity. It can also improve efficiency. Take the persons table we created above, if I had to insert 20 names at a time to be a successful operation, it would be very likely to affect the integrity of the operation without using a transaction if an exception occurred during the insert process or some other database operation occurred during the insert process. So a transaction can be a good solution to a situation in which a transaction is the process of taking all actions in a startup transaction as a transaction. When all the procedures have been completed, we can determine whether the transaction is committed or rolled back based on the success of the operation. Committing a transaction commits all data to the database at once, and if the transaction is rolled back, the operation is discarded, and the data for the original table is not changed.

So how do you start, commit, and roll back the transaction? The SQLite are: BEGIN, commit and rollback. Here's a look at the example:

@try {

Char *errormsg;

if (Sqlite3_exec (_database, "BEGIN", NULL, NULL, &ERRORMSG) ==SQLITE_OK) {

NSLog (@ "Start transaction successful");

Sqlite3_free (ERRORMSG);

Sqlite3_stmt *statement;

if (Sqlite3_prepare_v2 (_database, [@ "INSERT into persons (name) values (?);" Utf8string],-1, &statement, NULL) ==SQLITE_OK) {

Binding parameters

const char *text=[@ "John" cstringusingencoding:nsutf8stringencoding];

Sqlite3_bind_text (statement, index, text, strlen (text), sqlite_static);


if (sqlite3_step (statement)!=sqlite_done) {

Sqlite3_finalize (statement);

}

}


if (Sqlite3_exec (_database, "COMMIT", NULL, NULL, &ERRORMSG) ==SQLITE_OK) {

NSLog (@ "Commit transaction succeeded");

}

Sqlite3_free (ERRORMSG);

}else{

Sqlite3_free (ERRORMSG);

}

}

@catch (NSException *e) {

Char *errormsg;

if (Sqlite3_exec (_database, "ROLLBACK", NULL, NULL, &ERRORMSG) ==SQLITE_OK) {

NSLog (@ "ROLLBACK TRANSACTION succeeded");

}

Sqlite3_free (ERRORMSG);

}

@finally {

}