On Sat, Apr 28, 2012 at 8:24 AM, Black, Michael (IS) <michael.bla...@ngc.com > wrote:
> 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. > > That would be a disadvantage to shared-cache mode. The effect is the same regardless of your journal mode setting. > > > > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
