On Fri, Apr 27, 2012 at 6:45 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > If two database connections share the same cache, and one connection > rolls > > back, that means it will be changing cache content out from under the > other > > database connection, so any queries ongoing in the other connection have > to > > abort. > > Richard, > > Could you please explain this? I understand that this statement is > true when read_uncommitted is set to 1. But without it in my > understanding two connections should behave as if they don't have > shared cache and select query in one connection shouldn't rely on any > pages changed in another connection. Thus it seems there's no need to > abort any queries. In what part this statement is not true? What > changed parts select query can rely on? > In theory, you probably could get it to work such that a rollback in one connection does not force aborts in another connection as long as read_uncommitted is not set. But that is yet another special case to write code for, to test, and to be a magnet for database-corruption bugs. I don't want to go there. The whole shared cache mechanism is way too complicated already, even without introducing yet another corner case optimization. > > > Pavel > > > On Fri, Apr 27, 2012 at 4:38 PM, Richard Hipp <d...@sqlite.org> wrote: > > On Fri, Apr 27, 2012 at 3:50 PM, Brad House <b...@monetra.com> wrote: > > > >> On 04/27/2012 02:00 PM, Brad House wrote: > >> > >>> > >>> Only the connection that does the rollback has its queries aborted. > >>>> > >>> > >>> That is not the behavior I am seeing in 3.7.11, but was the behavior > >>> I saw in 3.7.10. > >>> > >>> If you are seeing other connections get queries aborted, that is > >>>> something > >>>> new that I have not seen before and will need to investigate. > >>>> > >>> > >>> Correct. > >>> > >>> If you do a ROLLBACK in the middle of a query, why would you ever > want to > >>>> keep going with that query? What would you expect to see? > >>>> > >>> > >>> I wouldn't expect to keep going on that query. > >>> > >>> I'll try to write a test case. > >>> > >>> -Brad > >>> > >> > >> > >> As promised, I've attached a test case which uses the SQLITE > amalgamation. > >> > > > > Disable shared cache mode and you should be good to go. > > > > If two database connections share the same cache, and one connection > rolls > > back, that means it will be changing cache content out from under the > other > > database connection, so any queries ongoing in the other connection have > to > > abort. > > > > Two database connections in shared cache mode behave like a single > database > > connection in many ways, especially when you are talking about the cache > > that they share. > > > > > > > > > > > >> > >> Sorry about how ugly the code is, I know it is bad, but it should prove > the > >> point. I don't know if I'd consider this a _minimal_ test case, but I > >> tried to simulate everything we do like the options used to build the > >> amalgamation, > >> and the fact that we register threading callbacks. > >> > >> This test case creates a table, adds 100 rows, then spawns 2 threads > >> each with their own independent db handle. > >> > >> One is a reader, the other is a writer. It tries to make sure the > >> reader obtains its read lock on the table first, and cycles through the > >> rows. The writer simultaneously tries to insert another row... I've > >> added some synchronization between the threads on sqlite3_step() so > >> they go back and forth (failure happens quicker this way). > >> > >> What you'll see happen on 3.7.11 is the writer rolls back, and all of > >> a sudden, the reader is aborted (with message: > >> my_sqlite3_query(): sqlite3 returned 4: abort due to ROLLBACK). > >> > >> On 3.7.10, the writer does not cause the reader to rollback ... just > >> the writer keeps rolling back until the reader finishes, then the writer > >> is able to retry and complete. > >> > >> When compiling, copy sqlite3.c and sqlite3.h from the amalgamation into > >> the same directory as the source file and run (on Linux): > >> > >> gcc -Wall -W -o sqlite_test sqlite_test.c -lpthread -ldl > >> > >> (yeah, it has an #include "sqlite3.c" ... bad, but if you look at > >> it, you'll see why). > >> > >> Then to run it, just run: > >> > >> ./sqlite_test > >> > >> It will create a "./db.sqlite" database. This db must be removed for > >> each subsequent run or it will error out with a create table failure. > >> > >> Thanks. > >> -Brad > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > _______________________________________________ > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
