SQLite Forum

Statements outside of explicit transactions in SQLite
Many thanks for this very comprehensive reply. It's much appreciated, especially since I can see there's a continuous deluge of questions here.

Out of curiosity, I made a little of test of what you said, and verified it's correct (although I never doubted it): On one connection I executed `BEGIN`, then `SELECT` and started iterating over the results, then `COMMIT` and continued iterating over the earlier cursor. On another connection I repeatedly tried executing `INSERT` statements. This was in journaling mode so the `SELECT` held up the `INSERT`s as expected. Sure enough, as you said, this did not release on the `COMMIT`, and instead the `INSERT` only progressed once the `SELECT` was exhausted.

I have to admit, I find this very surprising. I'd expect `COMMIT` (or `ROLLBACK`) to release all locks, and an attempt to continue using an open statement from before to result in an error, something like "transaction for that statement has now been closed". Now I know, I'll be more careful about resetting incompletely iterated statements! (I'm actually using the Python sqlite3 wrapper module, so this translates to calling `.close()` on the `Cursor` object in that API.)

I have a follow up question: Are there are any statements that write to the database but return multiple rows? I just ask because, if so, that would mean that an open cursor to such a statement would cause the write lock to be retained even after a `COMMIT`. Assuming that's not the case, we can always be sure a successful `COMMIT` will at least store all data in the database, even if it potentially doesn't release a read lock.