Write-Ahead Logging

The default method SQLite implements atomic commit and rollback is to roll back the beginning of the log version 370,
A new "Write-ahead log" option (hereinafter referred to as "Wal") is available

Advantages and disadvantages of using Wal instead of a log rollback include:

1. Wal significantly accelerates in most cases.
2. WAL is related to the reader's provision of concurrency without compromising the specification and a writer does not prevent the reader from reading and writing a Simultaneous Optimization
3. Disk I/O operations SQL Server continuously uses multiple Wal.
4. Wal uses a large number of fewer fsync () operations, so the fsync () system call is interrupted when a problem occurs on the system.

However, you can also disadvantages:

1. Wal usually requires VFS to support the shared-memory primitive (exception: Wal
   Without shared memory built-in UNIX and Windows) vfses supports this, but third-party extensions of vfses for custom operating systems may not.
2. All processes must use databases on the same host computer; WAL file systems cannot be used on the network.
3. A transaction involves changing the number of attached databases to be atomic for each individual database, but you have not set it as an atomic cross-database.
4. The database page size cannot be entered in Wal mode. You can use the backup API to restore from a backup by using vacuum or vacuum. You must roll back and change the page size in log mode.
5. The read-only Wal database merging process cannot be opened. You must have the write permission"-SHM"Database associated with Wal-index shared memory files,
   If the file exists, or the directory specified in the-I option (if there is a database file for write access, if"-SHM"" File does not exist
6. Wal may be very fast (maybe 1% or 2% is slower than traditional rollback-Journal), and there are very few applications to improve its reading speed.
7. There is an additional quasi-persistent"-Wal"File and"-SHMIn each database associated with shared memory files,
   In this way, SQLite can be used to appeal with less application file-format.
8. There is not enough disk space for additional operations among them, although automatically by default, still need mindful application developers.
9. Wal works best with smaller transactions. Wal does not act as a very large transaction that handles more than 100 MB. the traditional inf diary mode may be faster for excessive gigabyte transactions, the wal mode may be the same as the I/O disk-full failure. The recommended rollback mode for journal entries is larger than the concept of the same MB.

How Wal works

The traditional rollback log writes the output copy of the Works database content to a separate log file and then rolls back the changes directly to the database. The written information is included in the rollback in a crash event or rollback, the original content is played to the log to restore the database file to its original state. "commit when the log rollback has been deleted

Reverse select this Wal method original content is retained in the database file and appended to a separate Wal file a commit when a special record, it indicates that the reason why the commit will be appended to Wal in commit may be caused by the problem and thus it has not been written to the original database. This allows the reader to view the number of Wals that the continuous running database has committed the changes to at the same time. the transaction can also be appended to the end of a single Wal File

Operation

Of course, the append of a transaction to be finally transmitted to the wal file is copied back to the original database. Moving the wal file back to the database is called a "checkpoint"

Another method is that the difference between rollback and the Windows Mobile Device Center lies in the rollback-Journal method. There are two elements to read and write, and one operation, the Windows Mobile Device Center primitive has three types of current operations: read, write, and operation.

By default, SQLite cannot be used when the wal file reaches a threshold of 1000 webpages (sqlite_default_wal_autocheckpoint during compilation) the application does not have to perform any operations on these checkpoints, but if they want to view, the application can adjust the automatic threshold of the checkpoint or close the idle minutes or separate threads or processes during the automatic checkpoint period.

Concurrency

When the wal-mode database reads data, it will remember the last valid position in the wal commit record to call this point "end tag" because Wal can expand and new commit records can be connected to the database by various readers, each reader may have its own ending tag, but for any specific reader end tag the duration of the transaction that has not changed a single read transaction to ensure that only the content in the database is, because of its time at a single point scale

When the reader needs the content of a page, it first checks whether Wal has marked the last copy of the page in the box and the end of the page that occurs on the web page on the wal reader if the page does not exist. before the ending mark of the wal reader, then, the reader reading the page from the original database can exist in a separate process. Therefore, to avoid following each reader to scan the entire Wal web page (WAL files can grow, run according to the checkpoint frequency of multiple megabytes. If you do not maintain a shared memory structure named "Wal-index", this helps you to locate the problem if you need, the lowest I/owal-index will greatly improve the performance of the server, but the use of shared memory indicates that all readers must exist on the same machine.
The mobile device center cannot implement a file system on the network.

Although the end Of the wal file that adds new content is because the writer do nothing interferes with the operation, the writer and the reader can run at the same time, because there is only one Wal file, only one writer can be created at a time.

Set the checkpoint operation from the wal file content and set the checkpoint for the file returned to the original database to run the reader's attention at the same time, however, the checkpoint must be stopped. When it reaches a web page and reads in Wal, it marks any current hosts and stops the new checkpoint at this point. Otherwise, it may overwrite some files in the database and is currently using the checkpoint. will remember (In Wal-index) how to get it far and transmit content from the wal to the database from its next call

Therefore, a long-running transaction can prevent a checkpointer from reading progress. Each read transaction will be terminated, but the checkpointer will be able to continue.

The number of checkpointer writer entries displayed per page each time a write operation occurs. Check if the change and Wal have been transferred to the database and if not, to prevent the reader from being Wal. Below is a separate window that will run Wal returning to and starting the wal at the end of the new transaction report can prevent the wal file from being grown and not bound.

Performance Considerations

Very fast), because they only involve writing content (with two rollback-Journal transactions) and because the write is further synchronized by all the order of the content to be passed to the disk is not required, as long as the application, if you want the sacriice to be persistent, use a power failure or soft reboot (writer synchronizes Wal at each transaction if committed Pragma
If synchronization is set to full but this sync Pragma is omitted, the synchronization is set to normal)

In other hand-read applications, each reader must check the content of the wal file as the size of the self-Wal file, and check that the wal file requires time similar to the size of the wal file. You need to use Wal-index to help find the content in the wal file faster, but the wal file size will still be disabled. Therefore, to maintain good performance, it is important to keep the wal file size at a fixed interval through the run checkpoint.

Operation because of the synchronization operation. to avoid database corruption, wal must be synchronized to persistent storage and moved content after power failure or soft reboot. The database from Wal to database and file must be checked before Wal synchronization is reset, additional information is also required for checkpointer to write to the database by executing any number of sequential pages (the page can be transferred from Wal to the database in descending order), but usually, even if a large number of seek operations are written to this page, such factors as unsupported checkpoints and subscribe to the server.

The default policy is to allow consecutive write transactions to grow Wal until Wal changes about 1000 web pages in size and then run the checkpoint operation for each subsequent commit, until the wal is reset to less than 1000 web pages, by default, the checkpoint will be automatically run by using the commit of the same thread, limit the wal size. This undefined majority of commit operations are very fast, leading to a much slower commit (one other checkpoint). If this effect is automatically configured, the application can also disable automatic operations and run the regular checkpoint in a separate thread, or the independent process is a command (the link and interface are used to complete this display as follows)

Note that the use of Pragma synchronization is set to normal, which is a checkpoint operation to issue an I/O barrier or synchronous operation (on UNIX fsync () or flushfilebuffers () on Windows) if the application therefore executes database queries and updates on a separate thread or process running the checkpoint main thread or process, the synchronization operation will never be blocked. This helps prevent "Latch-up ".
"The transaction is no longer configured here as described in summary in the busy drive application. The following may be a rollback power failure or a hard reset.

Please note that there are multiple architectures between them, and the compromise considers the average Read and Write Performance of the density to be read or read performance. Select one that wants to keep Wal as small as possible, as a result, the running checkpoint usually requires write performance per commit. The cost of amortize each checkpoint is as much as possible, which means that each checkpoint is expected to run less often and WAL is allowed to grow, before each checkpoint, the frequency value of the checkpoint is measured in seconds. Therefore, different applications may run the checkpoint once to another default policy based on the relative Read and Write Performance Requirements of the application. the wal reaches 1000 web pages and the checkpoint setting policy does not seem to be tested by the application on the workstation. However,
Other policies may have better performance on different platforms or for different workloads.

Activating and configuring Wal Mode

The default value of a SQLite database connection is journal_mode = Delete. to switch to the wal mode, Perform one of the following operations:

Pragma journal_mode = Wal;

Journal_mode returns a string that is returned when the mode of the newly created mail is complete"Wal"If the conversion to Wal cannot be completed (for example, if VFS does not support the necessary shared-memory
The log mode will remain unchanged and the string from the primitive will return the previous diary mode (for example"Delete").

Automatic checkpoint

By default, SQLite automatically sets the checkpoint's commit to 1000 webpages, resulting in Wal files or more sizes, or when the last connection file in the database is closed, the default configuration of the opened database is used as the majority of applications, but the program requires more control. To force the use of checkpoints, you can use wal_checkpoint.
You can use the sqlite3_wal_checkpoint () C interface to change the automatic checkpoint threshold or the automatic operation can be completely invalid.
You can also use a program sqlite3_wal_wal_hook (
) To register the callback function, call the script command. When any transaction is committed to Wal, call sqlite3_wal_checkpoint () it is necessary to think about a checkpoint based on any condition (using the checkpoint automatic mechanism is implemented as a simple surrounding packaging sqlite3_wal_hook (
))

For the same Wal Mode

Use "other log mode Pragma journal_mode = Wal default persistent display persistent information if the process sets Wal mode, then close and re-open the database, the database will be in the wal back mode in contrast if the process is set (for example) Pragma journal_mode = truncate
Then close and re-open the default settings for database rollback mode) and back up Delete instead of the previous truncate settings.

The wal Mode means that the persistence of the application can be converted to using SQLite. In the wal mode, the application itself runs without any modification"Pragma journal_mode = Wal;"File (s) command line shell or other utility in the database,
Then restart the application.

All connection files of the same database in Wal log mode will be set in any connection.

Read-Only Database

No SQLite database (whether it is in Wal mode) is readable (if it is in Read-Only media, it needs to be restored as a result, for example, if the application crashes SQLite database and the sample hot diary unless a process is opened, unable to open this database has the permission to write database files, including database files and directories. This is because the operation type is invalid. Before the rollback change, you must add it to the database through the crash and read it without the write permission,
All files that cannot be rolled back and directories that contain them

In Wal mode, you cannot open the read-only media, because even if the common Wal mode requires the recovery-like operation to read

The efficient Wal reading algorithm requires that a hash table share the content of memory in the wal file. The hash table is called the wal-index Wal-index network.
Whether it is in the shared memory, And the Enable-psbreakpoint cmdlet has a name in the host computer file system to customize VFS for free to implement shared memory. For any impact, see matching, however, the built-in v6.1.2 SQLite shared memory of UNIX and Windows drivers uses mmapped.
The file uses the suffix "named-SHM"The database file Wal-index in the same directory must be accessed for the first time. Even if you press the reader, to open the wal database, the write permission is required in"-SHM"Shared memory files
If the file already exists, otherwise. the directory specified in the-I option (if you need to access the database, so that Wal-index can be created. If it does not exist, this will not prevent access from the shared memory image implemented by custom VFS. long type. read-only. the wal database will block the database on the basic Wal read-only media of the default UNIX and windows.

Therefore, you should always convert to SQLite database Pragma journal_mode = Delete and then transfer it to read-only media

In addition, if multiple processes Access Wal-mode database files, then all processes should use user or group IDs to write them into database files, "Wal file, shared memory" will be displayed.-SHMFile and contains the Directory

Implementation of shared-memory in Wal-Index

"Wal-index stores the volatile shared-memory created in Wal-index/dev/shm in the Wal Mode Implemented by a common medium mmapped robustness earlier (" new snapshot, for example, if the file is in Linux or on other UNIX systems,/tmp must use this method to use a different root
Directory process (changed through chroot details) to different files and therefore, use different shared memory regions, leading database corruption other serious errors shared memory modification methods are not portable in different UNIX blocks of the entire flavor and we cannot find any methods, the only way to create a shared memory block with severe errors in Windows is. to ensure that all processes access the same database and find files using the same shared memory, the mmapping
Create the directory where a file in the shared memory is located. The database itself

The disadvantage of using a common disk file to provide shared memory is that it may write unnecessary disk with shared memory I/O to the disk. However, developers do not think this is a major problem because the wal-index rarely exceeds the 32 kib option and never synchronizes more prompts, the wal-index backup file is deleted. If the connection in the last table is disconnected, it will disable the angle tangent disk from having I/O

The default Implementation of dedicated Application Path shared memory is to implement the wal-index heap memory by using the design method VFS. For example, if a specific database is known to be accessed by clues in a single process, instead of true shared memory

Instead of shared-memory Wal.

Starting from SQLite 374, wal databases can be created, read, and written. Even if the shared memory is unavailable, as long as the locking_mode is set to the first one, exclusive access is performed in the process. In other words, A database that can interact with Wal without using shared memory. If the process is thread-safe and the process accesses the database, this function allows you to create Wal.
Traditional vfses for databases, reads, and writes lack the expressions of the "version 2" shared-memory method xshmmap, xshmlock, xshmbarrier, and xshmunmap sqlite3_io_methods objects.

If the exclusive locking mode is set to the first Wal-mode, it should be for database access and SQLite will never try to call any shared-memory method, therefore, any shared-memory Wal-index is created. In this case, it is not stored in the exclusive mode in the selected database, as long as the log mode is Wal; try to use "changed lock modePragma
Locking_mode = normal;"No-ops will be changed. The only way to fail to lock % exclusive is mode: change first, failed % Wal diary Mode

If normal is the first Wal-mode locked for access in the database, the shared-memory Wal-index is created, which means that the underlying VFS must support shared-memory "version 2". If VFS does not support the shared-memory method, try to open a Microsoft Access database in Wal mode. or a database attempt to send a connection to the wal mode will fail to be long. The connection is using shared-memory Wal-index, you can freely change the lock mode between normal and exclusive. It is only used when shared-memory
Wal-index is omitted. When the exclusive mode is applied to lock the first Wal-mode database to access this series, the exclusive mode is locked.

Backward compatibility

The SQL file format in Wal mode has not changed. However, if the wal parameter Wal-index is a new concept and SQLite is earlier, it will not know how to recover from it. The SQLite database is working in Wal mode. If a crash occurs, it will prevent the old version of SQLite from trying to recover the wal-mode database (and making it bad) file format in the database (Bytes 18
19. In version number, the database title is in Wal mode) so if it is an old version of SQLite, it will try to connect to the Database working in Wal mode of SQLite along a straight line "file it will report an error will not be encrypted or not database"

An explicit change can be made, and the Failure % Wal mode is used. Example:

Pragma journal_mode = Delete;

XFS changes the number of file formats that exceed Wal mode change database version return to 1 old version SQLite file can access the database again