#readwise # Atomic Commit in SQLite  ## Metadata - Author: [[sqlite.org]] - Full Title: Atomic Commit in SQLite - URL: https://www.sqlite.org/atomiccommit.html ## Summary SQLite uses a rollback journal to make database changes appear atomic, even if a crash happens during a transaction. It writes original data to the journal before changing the database, allowing recovery by restoring from the journal if needed. The system assumes disk writes can be interrupted or reordered, so it uses checksums and careful syncing to prevent corruption. Although robust, SQLite's atomic commit can still fail if the operating system or hardware behaves unexpectedly. ## Highlights SQLite has the important property that transactions appear to be atomic even if the transaction is interrupted by an operating system crash or power failure. ([View Highlight](https://read.readwise.io/read/01k2wb7q7w6hzgbc2y24jynb8y)) --- The information in this article applies only when SQLite is operating in "rollback mode", or in other words when SQLite is not using a [[SQLite Write-Ahead Logging|write-ahead log]]. SQLite still supports atomic commit when write-ahead logging is enabled, but it accomplishes atomic commit by a different mechanism from the one described in this article. See the [[SQLite Write-Ahead Logging|write-ahead log documentation]] for additional information on how SQLite supports atomic commit in that context. ([View Highlight](https://read.readwise.io/read/01k2wb72h0z3kjnbgdhv5tb9k8)) --- ### Acquiring A Read Lock The first step toward reading from the database file is obtaining a shared lock on the database file. A "shared" lock allows two or more database connections to read from the database file at the same time. But a shared lock prevents another database connection from writing to the database file while we are reading it. This is necessary because if another database connection were writing to the database file at the same time we are reading from the database file, we might read some data before the change and other data after the change. This would make it appear as if the change made by the other process is not atomic. ([View Highlight](https://read.readwise.io/read/01k2wbjvpsmaaj30a8hhjg39bx)) --- Notice that the shared lock is on the operating system disk cache, not on the disk itself. File locks really are just flags within the operating system kernel, usually. (The details depend on the specific OS layer interface.) Hence, the lock will instantly vanish if the operating system crashes or if there is a power loss. It is usually also the case that the lock will vanish if the process that created the lock exits. ([View Highlight](https://read.readwise.io/read/01k2wbkgx7ks0ke4qsaj3mjsc5)) --- ### Obtaining A Reserved Lock The idea behind a reserved lock is that it signals that a process intends to modify the database file in the near future but has not yet started to make the modifications. And because the modifications have not yet started, other processes can continue to read from the database. However, no other process should also begin trying to write to the database. ([View Highlight](https://read.readwise.io/read/01k2wbnqt5rbcbm95zd9a6bwvc)) --- ### Creating A Rollback Journal File Prior to making any changes to the database file, SQLite first creates a separate rollback journal file and writes into the rollback journal the original content of the database pages that are to be altered. The idea behind the rollback journal is that it contains all information needed to restore the database back to its original state. ([View Highlight](https://read.readwise.io/read/01k2wbqnmkz4gkw9f5m7ntk1p6)) --- ### Changing Database Pages In User Space After the original page content has been saved in the rollback journal, the pages can be modified in user memory. Each database connection has its own private copy of user space, so the changes that are made in user space are only visible to the database connection that is making the changes. Other database connections still see the information in operating system disk cache buffers which have not yet been changed. And so even though one process is busy modifying the database, other processes can continue to read their own copies of the original database content. ([View Highlight](https://read.readwise.io/read/01k2wbshkscpkq01e94q03w6qz)) --- ### Flushing The Rollback Journal File To Mass Storage The next step is to flush the content of the rollback journal file to nonvolatile storage. As we will see later, this is a critical step in insuring that the database can survive an unexpected power loss. This step also takes a lot of time, since writing to nonvolatile storage is normally a slow operation. ([View Highlight](https://read.readwise.io/read/01k2wbtdmeg56pcjwa4x5d40cp)) --- ### Obtaining An Exclusive Lock Prior to making changes to the database file itself, we must obtain an exclusive lock on the database file. Obtaining an exclusive lock is really a two-step process. First SQLite obtains a "pending" lock. Then it escalates the pending lock to an exclusive lock. A pending lock allows other processes that already have a shared lock to continue reading the database file. But it prevents new shared locks from being established. ([View Highlight](https://read.readwise.io/read/01k2wbwrseb2xqcbbakqvb2wtr)) --- ### Writing Changes To The Database File Once an exclusive lock is held, we know that no other processes are reading from the database file and it is safe to write changes into the database file. Usually those changes only go as far as the operating systems disk cache and do not make it all the way to mass storage. ([View Highlight](https://read.readwise.io/read/01k2wby1xcjp9gmfkw0r7f63v9)) --- ### Flushing Changes To Mass Storage Another flush must occur to make sure that all the database changes are written into nonvolatile storage. This is a critical step to ensure that the database will survive a power loss without damage. However, because of the inherent slowness of writing to disk or flash memory, this step together with the rollback journal file flush in section 3.7 above takes up most of the time required to complete a transaction commit in SQLite. ([View Highlight](https://read.readwise.io/read/01k2wbz9khexmcrrwvby8ypkds)) --- ### Deleting The Rollback Journal After the database changes are all safely on the mass storage device, the rollback journal file is deleted. This is the instant where the transaction commits. If a power failure or system crash occurs prior to this point, then recovery processes to be described later make it appear as if no changes were ever made to the database file. If a power failure or system crash occurs after the rollback journal is deleted, then it appears as if all changes have been written to disk. Thus, SQLite gives the appearance of having made no changes to the database file or having made the complete set of changes to the database file depending on whether or not the rollback journal file exists. ([View Highlight](https://read.readwise.io/read/01k2wc0dpp5sf32m7qgxeatpk7)) ---