Database Write process

?

1. Reserved state
   After entering reserved state, SQLite can modify the contents of the database, but write the modified content to the pager cache, the size is specified by the page cache . After entering this state, pager begins initializing the log file, user rollback, and exception recovery. (In fact, it is to copy the contents of the file in the log to the database file) This mechanism allows the database to be read at the same time as the write operation. However, because there is only one reserved or exclusive lock, there can only be one write operation
2. Pending state
   From reserved to exclusive to experience a pending state, the pending lock is acquired. Pending is a gateway lock.
   • There is no transaction from the unlock state to the shared state, ensuring that no new read and write operations are made
   • Transactions that already have a shared lock can function correctly. Write transactions await the completion of these transactions, releasing the lock.
3. Exclusive State
   Once all the other database links have been freed, the entire database has only one write operation. Enter the exclusive state.
   1. Pager Check log file has been written to the disk, call the Fsync () system call (if this system call hangs, SQLite can not do)
      • If synchronous pragma is the default setting, a sync operation is called.
      • If synchronous pragma is full, two sync operations will be called
      • If synchronous pragma is NONE, the sync operation is not called
   2. Pager write the modified content to the database file
   3. Clean up log files, release locks

The principle of lock implementation

?

The implementation of SQLite locks is a standard-based file lock. SQLite has three locks on the database file, 1 reserved byte, a pending byte, and a shared region.

1. Unlocked->shared
   Gets the read lock for pending byte. After the success, a byte read lock is randomly obtained in the shared region and the read lock of pending byte is freed.
2. Shared->reserved
   Gets the write lock for reserved byte.
3. Reserved->exclusive
   First get the write lock for pending byte.
   Once successful, there will be no unlocked->shared because pending byte has been locked. Next, you will try to obtain a write lock for the entire shared region. Because the shared region has some read locks held by active transactions, the database waits for those transactions to complete and release the lock.
   

Recovery mechanism

Use reserved byte to determine if recovery is required.
In general, log files and reserved lock are synchronized, i.e. both appear and disappear.
If SQLite discovers the log file, but does not find reserved lock, then it can be assumed that crash or system power down.
An integrity check is done when pager first opens the database or reads from the database file into memory. If inconsistencies are found (with log files but no reserved lock), then the database enters recovery mode. The database log file at this time is called Hot Journal.
After entering recovery mode, the pending state is entered directly from the shared state, as shown in the Gray Line of the first diagram. This way, you can guarantee
1. There is no new database connection
2. The database connection to the shared state does not enter the recovery mode (it does not actually occur because the first database connection goes into recovery mode, which blocks other database connections from entering shared state)

Simply put, a hot journal is a implicit exclusive lock. If the write operation is crash, the database will not have additional operations until the database connection is successfully restored. Crash after the first operational database pager will see hot journal, and database recovery.

Database Write process