ACID in SQLite

Original author: Official site
  • Transfer
This post describes the system of locking and maintaining atomicity, consistency, isolation and reliability (ACID) in SQLite, as well as algorithms for writing and reading from the database file.

Pager module


Locks and concurrent access in SQLite version 3 and higher are handled by the pager module. This module is responsible for ACID. The pager is not interested in the details of the encodings of the base, B - trees, indexes, etc., from its point of view, the database is a single file divided into equal-sized blocks (pages) numbered starting with 1. The pager communicates with the operating system using the OS layer Interface In this post, “thread”, “process” and “thread” are equivalent concepts.

Locks


From the point of view of one process, the database file can be in one of 5 lock states listed below:
  • UNLOCKED - The default state. The database is unlocked, any streams can read and write data.
  • SHARED - The database is readable but not writable.
  • RESERVED - The process plans to write to the file, but is currently reading. Only one RESERVED lock can be at a time. Together with this mode, SHARED locking can be used .
  • PENDING - The process expects the end of all SHARED locks to start recording and switch to EXCLUSIVE mode.
  • EXCLUSIVE - The process writes to the database file. No other database locks in parallel with this one are allowed.


Below are the algorithms for reading and writing data using the rollback log as a guarantee of database integrity.

Algorithm for reading data from the database


To read from the database, the process must perform the following steps:
  1. Open the database file and get a SHARED lock if this action cannot be returned by SQLITE_BUSY .
  2. Check if the base has a hot rollback log. If not, you can read data from the database. If yes, then step 3.
  3. Get PENDING and then EXCLUSIVE lock. If there is no way to get the lock data, then another process is already rolling back. In this case, remove all locks and return SQLITE_BUSY .
  4. Read the rollback log and the master log (when joining several databases, a special file (master journal) is created that stores data about rollback logs for each of the attached databases).
  5. Roll back
  6. Delete the rollback log file (depending on the journal_mode option of the PRAGMA command , deletion occurs in different ways).
  7. Delete the log wizard file, if possible.
  8. Release the PENDING and EXCLUSIVE locks, but leave the SHARED lock and read the database.


The algorithm for writing data to the database


To write data to the database, the process must first perform the following steps:
  1. Get SHARED lock (reading algorithm from the database).
  2. Get a RESERVED lock. If this is not possible, return SQLITE_BUSY .
  3. Create a rollback log. The size of the database and the name of the log wizard, if one exists, are written in the header of the log.
  4. Before making changes to any page in the database, the process first writes this page to the rollback log. Modified pages are primarily written to RAM, which means that the database file does not change, and other processes can read data from the database. If the data change has ended and the process is producing COMMIT , or if the memory is full, go to step 5.
  5. Verify that all rollback log data was actually written to disk.
  6. Get PENDING and then EXCLUSIVE lock. If it is not possible to obtain lock data, you must wait until the database file is released.
  7. Write all the data from RAM to disk in the database file (if the reason for writing was the RAM overflow, then go back to step 4).
  8. Delete the rollback log file (depending on the journal_mode option of the PRAGMA command , deletion occurs in different ways).
  9. Release PENDING and EXCLUSIVE locks.


The atomic commit algorithm is discussed in more detail in another post: habrahabr.ru/post/181584

Also popular now: