On Thursday, 24 September, 2015 08:53, Allen <allenpmd at gmail.com> said:
> Suggestions for SQLite features: > 1. It would be nice to have read-only transactions for use with WAL > databases. When a read-only transaction was started, it would take a > "lock" on the WAL and then not advance further into the WAL until the > transaction ended. Thus, all select statements issued while the read > transaction was open would see all writes that had been committed prior > to the start of the read transaction, but would not see any writes that > are not yet committed when the read transaction is started or writes > that start or complete while the read transaction is open. This would > apply to all (multiple) select statements that would all see the same > view of the data during the duration of the read transaction. WAL does this already. You just need to BEGIN a transaction when you want it to BEGIN and COMMIT or ROLLBACK when you are done with it. Of course the Repeatable-Read isolation only applies within the transaction only on the single connection. If you perform an UPDATE during the transaction the changes will, of course, be visible on that connection during the transaction. That is to say that if on one connection you do: BEGIN; SELECT col FROM table; (go away and do some stuff) SELECT col FROM table; ROLLBACK; BEGIN; SELECT col FROM table; ROLLBACK; and during the (go away and do some stuff) part A DIFFERENT CONNECTION does: BEGIN; INSERT INTO table VALUES (10),(11),(12); COMMIT; the second select in the first connection will see and obtain the same results as the first select did. The new data inserted into table will not be visible until the transaction is ended. Those inserts will be visible only to the third select in the first connection. Your thinking that this is not the case probably arises from not using explicit transactions. For example, if you executed the same code but did not explicitly put the BEGIN/ROLLBACK on the first connection, SQLITE would automatically wrap each SELECT in an implicit transaction. Since each SELECT is now in a separate transaction, Repeatable Read does not apply between selects and the second select would see the inserts that were done on the second connection. WAL mode selects using implicit transactions is effectively Cursor Stability isolation level. If you use explicit transactions, you effectively place the connection into Repeatable Read for the duration of your explicit transaction.
