On Friday, 15 January, 2016 11:14, James K. Lowden <jklowden at schemamania.org>,
> On Thu, 14 Jan 2016 16:54:04 +0100 Olivier Mascia <om at integral.be> wrote: > > Let a transaction (started with BEGIN TRANSACTION) which did only > > reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK > > TRANSACTION, or is it completely insignificant? > Why not do the right thing and remove the begin & commit transaction > statements? > An explicit transaction implies a unit of work, two or more changes to > the database that must cohere for the database be consistent. > Read-only operations (by which I suppose you mean SELECT) don't modify > the database and don't belong in a transaction. While it is true that without further action each statement is carried out in a separate transaction, it may very well be that the OP wants to have what is called Repeatable-Read. The only way to obtain Repeatable-Read between statements is to put the SELECTs in the *same* transaction, otherwise they will see different views of the database if there was an intervening update. Once you put multiple SELECTs in the same transaction, they will ALL see the same database, unchanging, notwithstanding concurrent updates. Of course, in SQLite the READ transaction will block all writers -- thus achieving stability. If WAL is used then updates can run concurrently and the view of the database inside the BEGIN ... COMMIT containing only SELECTs will not see the updates committed in a different transaction until the after the commit.
