Ok, so here's a question (trying an experiment to see if this will work, but throwing it out here as well).
What if the source db handle for the backup is opened to use private cache? Will this have any effect at all or is it the cache mode of db2 (using your example below)? On Sep 28, 2012, at 1:49 AM, Dan Kennedy wrote: > On 09/28/2012 03:32 AM, Jonathan Engle wrote: >> I've been picking away at this for the last few days and have it >> narrowed down fairly well. >> >> It looks like if I turn off shared cache, it works fine (same >> application code). >> >> If I run with SQL_DEBUG enabled, the first issue I run into in an >> assertion in sqlite3BtreeEnter: assert( >> sqlite3_mutex_held(p->db->mutex) ); The call stack from it is >> >> sqlite3BackupUpdate backupOnePage sqlite3BtreeGetReserve(p->pSrc) >> sqlite3BtreeEnter >> >> Look up the stack, it looks like sqlite3BackupUpdate locks the mutex >> on the destination database but not the source. > > Say you have an active backup operation (one created by backup_init() > but not yet completed) using source database handle db1. In > non-shared-cache mode. The backup is half-way done - 50% of the source > database pages have been copied to the destination. > > If the source db is written by another process at this point, or using > a database handle other than db1, the backup operation has to start over > from the beginning on the next call to sqlite3_backup_step(). > > However, if the app writes to the source database using handle > db1, SQLite will automatically update the backup database as well. So > that the backup operation doesn't have to restart. That's the call to > sqlite3BackupUpdate() above. As you say, the code assumes that the > mutex on the source database handle (i.e. db1) is already held. > > Turns out that this assumption is only true in non-shared-cache mode. > Because of the way the code is structured, in shared-cache mode, this > call to sqlite3BackupUpdate() will be made even if the source database > is updated using a second database handle - db2. But the backup code > still calls routines that assume the db1 mutex is held... Bug. > > In the deadlock scenario, all the threads are blocked in > lockBtreeMutex(). This routine is supposed to prevent deadlock > by ensuring that mutexes are only obtained in a globally defined > order. But that could malfunction in unpredictable ways if two threads > were running the lockBtreeMutex() code on behalf of the same database > connection simultaneously. The mutex on the database handle is > supposed to prevent that from happening, but since the bug above > allows lockBtreeMutex() to be called without actually holding > the mutex it easily might. > > I think the fix will likely be to have shared-cache mode work like > non-shared-cache mode - force the backup to start over if the source > database is written via a second database handle (i.e. db2). > > Dan. > > > > > >> Tried as a test adding locking the source db, bad results. Altered >> the definition of asserts to make them not fatal, got a ton of >> assertions then deadlocking again. >> >> Haven't tried to make a sample program yet, but the gist of it would >> be to have one (or more threads) doing lots of small transactions >> updating the database while simultaneously having another thread >> continuously making a backup of the db (unrealistic scenario, just >> makes the race easier to see). >> >> It may or may not matter whether or not encryption is used, or more >> importantly whether SQLITE_HAS_CODEC is defined, since the portion of >> code that's asserting is only there when SQLITE_HAS_CODEC is >> defined. >> >> At this point, I guess I'll just run without enabling shared cache, >> which seems to work just fine (a little better with regards to >> backups actually) and just hope this gets fixed in a future release. >> >> Jon >> >> >> >> It looks like it's unhappy that the mutex for the source database in >> the >> >> On Aug 25, 2012, at 1:33 PM, Jonathan Engle wrote: >> >>> No, the deadlock is deeper than that, it's stuck trying to lock >>> mutexes. My current theory is that the thread trying to update the >>> page in the backup destination database is what's causing trouble. >>> >>> I also forgot to mention, each thread is using a different >>> connection object and that it's using shared cache mode. >>> >>> Jon On Aug 25, 2012, at 12:57 PM, Patrik Nilsson wrote: >>> >>>> Do you test for the backup errors, i.e. SQLITE_BUSY and >>>> SQLITE_LOCKED? >>>> >>>> Do you test for step errors, i.e. SQLITE_BUSY? >>>> >>>> If you get the busy error, you can wait a while and try again or >>>> start over. >>>> >>>> /Patrik >>>> >>>> On 08/24/2012 05:46 PM, Jonathan Engle wrote: >>>>> Ran into this recently, it's happened on one machine running a >>>>> beta test of our software. This is a multi-threaded >>>>> application, and I've run into a sequence of steps that >>>>> deadlocks hard that as far as I can tell from the documentation >>>>> shouldn't. This is using SQLite 3.7.13 with SEE. The source >>>>> database is using WAL mode, all transactions are done as >>>>> IMMEDIATE, synchronous mode is set to 0, and it is encrypted. >>>>> The destination database for the backup is not encrypted, and >>>>> is default (non-WAL, full synchronous) modes. >>>>> >>>>> >>>>> There are multiple threads active: >>>>> >>>>> - one performing a write - two performing reads - one closing a >>>>> connection - one is in the middle of a backup operation >>>>> >>>>> Here are the call stacks for the threads: >>>>> >>>>> >>>>> Writing thread: >>>>> >>>>> sqlite3_step sqlite3VdbeExec sqlite3VdbeHalt >>>>> sqlite3BtreeCommitPhaseOne sqlite3PagerCommitPhaseOne >>>>> pagerWalFrames sqlite3BackupUpdate backupOnePage >>>>> sqlite3BtreeEnter lockBtreeMutex pthread_mutex_lock >>>>> __psynch_mutexwait >>>>> >>>>> Closing a connection thread: >>>>> >>>>> sqlite3_close sqlite3BtreeEnterAll sqlite3BtreeEnter >>>>> lockBtreeMutex pthread_mutex_lock __psynch_mutexwait >>>>> >>>>> Reading thread: sqlite3_step sqlite3VdbeExec sqlite3VdbeEnter >>>>> sqlite3BtreeEnter lockBtreeMutex pthread_mutex_lock >>>>> __psynch_mutexwait >>>>> >>>>> Backing up thread: sqlite3_backup_step sqlite3BtreeEnter >>>>> lockBtreeMutex pthread_mutex_lock __psynch_mutexwait Reading >>>>> thread: >>>>> >>>>> sqlite3_step sqlite3VdbeExec sqlite3VdbeEnter >>>>> sqlite3BtreeEnter lockBtreeMutex pthread_mutex_lock >>>>> __psynch_mutexwait >>>>> >>>>> >>>>> >>>>> Also, the destination database for the backup is created on the >>>>> stack by the the thread doing the backup and is never passed >>>>> out to anybody (explicitly). >>>>> >>>>> What looks like is happening to me is that the writing and >>>>> backing-up thread are deadlocking with each other, with >>>>> 'sqlite3BackupUpdate' attempting to update the backup >>>>> destination database. Unfortunately, this is not something >>>>> I've reproduced locally, so I can't look parameters or lock >>>>> states. I'm going to try, as a kind of hail-mary, putting a >>>>> BEGIN IMMEDIATE transactions around the backup to block writing >>>>> during the database backup. >>>>> >>>>> If anyone has any suggestions or ideas about what I might be >>>>> doing wrong here, I'd appreciate it. >>>>> >>>>> >>>>> _______________________________________________ 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 >>> >>> _______________________________________________ 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 >> > > _______________________________________________ > 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
