Should another "disadvantage" of WAL mode be added to http://www.sqlite.org/draft/wal.html
Something that says rolled back transactions will cause an abort on any reads in progress if shared cache is enabled. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Friday, April 27, 2012 6:04 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case! 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
