SQLite Forum

Statements outside of explicit transactions in SQLite
There's a lot of waffle below, but I suppose you could summarise it all as: **What happens to statements outside of explicit transactions?** The documentation seems a bit unclear on this (although it's usually excellent by the way, many thanks for that).

**Autocommit mode vs implicit transactions:** Oddly, the section on [implicit transactions](https://sqlite.org/lang_transaction.html#implicit_versus_explicit_transactions) doesn't mention autocommit mode. Autocommit mode only seems to be mentioned in passing on the [sqlite3_get_autocommit()](https://sqlite.org/c3ref/get_autocommit.html), which conversely doesn't mention implicit transactions! But both talk about statements not executed within an explicit transaction (`BEGIN` and `COMMIT`/`ROLLBACK`), so those are related... right? Am I right in thinking that an implicit transactions are *only* ever started when in autocommit mode, and conversely any statement executed in autocommit mode (except `BEGIN ...`) will start an implicit transaction?

**Implicit transactions with multiple statements:** This is my key confusion, where the documentation doesn't seem to match reality. The section on implicit transactions, linked above, says:

> An implicit transaction ... is committed automatically when the last active statement finishes. A statement finishes when its last cursor closes ...

So I tried a test: In one thread, I ran a `SELECT` statement, but in between retrieving rows (2 at a time) I `INSERT`ed some more rows (1 at a time) and did deliberate pauses. In another thread, using a different connection, I repeatedly did a `SELECT COUNT(*)`. From what I understand of that quote, the `SELECT` and `INSERT`s in the first thread should have consisted of a single implicit transaction, because the "last active statement" on that connection had not yet finished (I hadn't reset the `SELECT`, and indeed continued to retrieve more rows through it afterwards). But, instead, the `COUNT(*)` kept increasing on the other thread, showing that the `INSERT` had committed immediately.

Is that quoted text correct? Does it mean something different than I interpreted it?

(Just to be clear: I'm actually very happy that statements not in a transaction are committed immediately, even when a cursor for a SELECT statement is also open. It's just that I'm confused about what the documentation is referring to.)

**Can an implicit transaction hold up an explicit transaction being committed:** The document [File Locking And Concurrency In SQLite Version 3](https://www.sqlite.org/lockingv3.html#transaction_control) mentions:

> The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur. 

But according to that earlier quotation, an implicit transaction won't be committed if a cursor is still open. Does this mean that even an explicit `COMMIT` statement for an explicit transaction might not actually commit data right away, if a cursor is still open from a statement? That would be very surprising! It seems not, given my previous experiment, but I just wanted to confirm this.