#readwise # SQLite Transactions  ## Metadata - Author: [[sqlite.org]] - Full Title: SQLite Transactions - URL: https://www.sqlite.org/lang_transaction.html ## Summary SQLite transactions ensure that all database reads and writes occur within a controlled environment. Transactions can be started automatically or manually and are finalized with `COMMIT` or `ROLLBACK` commands. There are different types of transactions, including `DEFERRED`, `IMMEDIATE`, and `EXCLUSIVE`, each with specific behaviors. Errors during a transaction may cause an automatic rollback, but applications should explicitly use `ROLLBACK` to handle errors effectively. ## Highlights **No reads or writes occur except within a transaction.** Any command that accesses the database (basically, any SQL command, except a few [PRAGMA](https://www.sqlite.org/lang_transaction.html/pragma.html#syntax) statements) will automatically start a transaction if one is not already in effect. **Automatically started transactions are committed when the last SQL statement finishes.** ([View Highlight](https://read.readwise.io/read/01jjhs3akf0eqfy34pmkdh9vbj)) --- Transactions created using `BEGIN...COMMIT` do not nest. For nested transactions, use the [[SQLite Savepoints|SAVEPOINT]] and [[SQLite Savepoints|RELEASE]] commands. ... An attempt to invoke the `BEGIN` command within a transaction will fail with an error --- **SQLite supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction.** ^l33yhl A read transaction is used for reading only. A write transaction allows both reading and writing. **A *read transaction* is started by a `SELECT` statement, and a *write transaction* is started by statements like `CREATE`, `DELETE`, `DROP`, `INSERT`, or `UPDATE` (collectively "write statements"). If a write statement occurs while a read transaction is active, then the read transaction is upgraded to a write transaction if possible. If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with [`SQLITE_BUSY`](https://www.sqlite.org/lang_transaction.html/rescode.html#busy).** While a read transaction is active, any changes to the database that are implemented by separate database connections will not be seen by the database connection that started the read transaction. If database connection X is holding a read transaction, it is possible that some other database connection Y might change the content of the database while X's transaction is still open, however X will not be able to see those changes until after the transaction ends. While its read transaction is active, X will continue to see an historic snapshot of the database prior to the changes implemented by Y. ([View Highlight](https://read.readwise.io/read/01jjhs94abdyfgc00shfxn0xdh)) --- ### Transaction Types Transactions can be `DEFERRED`, `IMMEDIATE`, or `EXCLUSIVE`. **The default transaction behavior is `DEFERRED`.** ([View Highlight](https://read.readwise.io/read/01jjhsf2k0jv1g62t1xejd2b62)) ^0foyux #### `DEFERRED` **`DEFERRED` means that the transaction does not actually start until the database is first accessed.** ^vm7v2b Internally, the `BEGIN DEFERRED` statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes. This causes the transaction that is automatically started to persist until an explicit `COMMIT` or `ROLLBACK` or until a rollback is provoked by an error or an `ON CONFLICT ROLLBACK` clause. **If the first statement after `BEGIN DEFERRED` is a `SELECT`, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return `SQLITE_BUSY`. If the first statement after `BEGIN DEFERRED` is a write statement, then a write transaction is started.** ^eirkgc #### `IMMEDIATE` **`IMMEDIATE` causes the database connection to start a new write immediately, without waiting for a write statement.** ^yigjnr The `BEGIN IMMEDIATE` might fail with [`SQLITE_BUSY`](https://www.sqlite.org/lang_transaction.html/rescode.html#busy) if another write transaction is already active on another database connection. #### `EXCLUSIVE` **`EXCLUSIVE` is similar to `IMMEDIATE` in that a write transaction is started immediately. `EXCLUSIVE` and `IMMEDIATE` are the same in [[SQLite Write-Ahead Logging|WAL mode]], but in other journaling modes, `EXCLUSIVE` prevents other database connections from reading the database while the transaction is underway.** ^uvyqsm ---