> I/and the original implementer, am/were more familiar with PostgreSQL's > MVCC. So I think the issue was the assumption that the query being > stepped through would only ever see the rows as they were at the start > of the query and would walk through them from first to last. > > Would > PRAGMA journal_mode=WAL; > provide that?
Yes. WAL journal mode provides "Repeatable Read" isolation within a transaction. This means that if you issue: BEGIN; SELECT ..... wait 3 hours with lots of intervening updates to all the tables in the database including the ones in your select above, done on a different connection. SELECT ..... COMMIT; SELECT ..... Assuming that all the selects are the same, then the first two selects will return identical results. All the updates will not be seen until after the read transaction is committed, so the third select will see the updates done in the updates that occurred on different connections. If you do not explicitly BEGIN and COMMIT transactions, then SQLite3 effectively wraps each statement in its own transaction. Note that without WAL, the above example will prohibit updates on other connections during the 3 hour perios you are holding the transaction open since without WAL, readers block writers and writers block readers. Even in WAL mode changes to the database made ON THE SAME CONNECTION are immediately visible to every statement on that connection. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Reid Thompson > Sent: Tuesday, 11 April, 2017 07:17 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] sqlite3_step and ORDER BY random() question. > > On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote: > > On 11 Apr 2017, at 12:33pm, Reid Thompson <reid.thomp...@ateb.com> > wrote: > > > > > Does 'more complicated' in the below scenario mean that there is > > > potential for records to be returned more than once if I use random()? > > > > The problem is not to do with random(). The problem is to do with > modifying a field used in your SELECT command. Suppose you have an index > on the column "name" and you execute > > > > SELECT name FROM treetypes ORDER BY name > > > > with the following names > > > > apple > > lemon > > oak > > orange > > teak > > willow > > > > You execute _step() twice, returning the top two, then issue > > > > UPDATE treetypes SET name="citrus" WHERE name="orange" > > > > What do you expect SQLite to do for the SELECT ? Should it return a > list of names which is clearly not in the order you asked for ? Should it > miss out one of the rows even though you asked for > > them all ? Should it return "orange" even though you have already > changed that value ? > > > > That’s the "more complicated". And for the sanity of people debugging > your code it’s better to ensure it never happens. > > > > Simon. > > Thanks again for the explanation. > > > That’s the "more complicated". And for the sanity of people debugging > your code it’s better to ensure it never happens. > > ;) yes. I'm debugging some inherited code. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
