On Tue, Jan 31, 2017 at 12:15 PM, Jens Alfke <j...@mooseyard.com> wrote:
> > > On Jan 31, 2017, at 9:39 AM, James K. Lowden <jklow...@schemamania.org> > wrote: > > > > According the SQL standard, every SQL statement is atomic. SELECT has > > no beginning and no end: the results it returns reflect the state of > > the database as of the moment the statement was executed. If you fetch > > the last row six days after the first, it still belongs to the database > > as it stood when you began. > > That is the behavior I was assuming and desiring, but it’s not what > actually occurs. If there are concurrent mutations in the same connection, > the rows returned by SELECT do _not_ reflect the prior state of the > database, but suffer from “undefined” behavior. In other words, there is a > lack of isolation between the SELECT and the concurrent UPDATEs. > > It’s possible I’m misunderstanding your point, though! > > My immediate workaround (implemented last night) is to iterate over the > statement at the moment the query is run, saving all the rows in memory. > Our enumerator object then just reads and returns successive rows from that > list. > > In the medium term I have ideas for optimizations that can let us avoid > this memory hit in most circumstances (since most queries are not made at > the same time as mutations.) For example, I could use the original > enumerator behavior by default, but when the client requests a mutation I > first notify all in-progress enumerators [on that connection], which will > immediately read the rest of their rows into memory. > I think you said something earlier about a fear that the record set might be too big to fit in memory (or wanting to avoid that possibility). You could select the record set you want to a temp table then select *that* while running updates on the original tables. Probably something you already thought of (or maybe I subconsciously read it from someone else already; sorry if adding noise), but thought I'd toss it out. SDR _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
