APSW does the same thing. I suspect that the commit operation is invalidating the select (since it is performed while the select is running) -- the effect of performing a commit in the middle of a running select (on the same connection) is (or should be) undefined. It will free the rollback journal and release all the locks. It should also reset the select. I am more concerned that it does not always do so more than I am concerned that it did so only once.
In any case, on a database where transactions are "per connection" and locks apply "to the database", the behaviour seen is not unexpected (by me, at any rate). Using a separate connection for the INSERT/COMMIT also will not work because it will not be able to get a write lock while the select is running. This might provide a clue that the user code is, shall we say, somewhat ill conceived. The issue can be resolved by: (a) moving the commit out of the loop; (b) retrieving the whole resultset before running the loop; or, (c) changing the journal_mode to WAL (using an on-disk database) and using a separate connection for the INSERT/COMMIT in the loop. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Simon Slavin >Sent: Wednesday, 24 December, 2014 17:26 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] COMMIT nested in SELECT returns unexpected > > >On 24 Dec 2014, at 6:39pm, Jim Carroll <j...@carroll.com> wrote: > >> I actually tried this same idea yesterday, but it made no difference. >Even >> manually creating cursors and executing all statements through them >yielded >> the exact same problem. >> >> For simplicity, I kept the code sample short, but I've tried dozens of >> different ideas over the last two days to get to the bottom of this. > >Dammit; I had high hopes I'd solved it. > >> I even >> spent time studying the _sqlite.c code base >> https://svn.python.org/projects/python/trunk/Modules/_sqlite/ to see if >I >> could track the source of the problem -- but I'm coming up blank. >> >> I was getting hung up with trying to understand whether or not the >concept >> being attempted was valid sqlite. The comments I hearing is that from >the >> sqlite perspective, the concept SHOULD work, but that there may in fact >be >> some sort of bug/feature in the pysqlite connector code? > >I feel that RSmith's post shows that the fault is not in SQLite itself. >I suspect it's in the Python library but I don't know nearly enough >Python to be able to tell for real. Could you try the same thing using >this python library > ><http://rogerbinns.github.io/apsw/> ><http://rogerbinns.github.io/apsw/download.html#source-and-binaries> > >instead ? > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
