Re: [sqlite] interesting deadlock.
Thanks for your help Dan, works like a charm (the work-around, haven't tried the new code yet). One question, usage-related. So the reason I'm all of this with the hot backup is that when certain tables are changed, we want to create a snapshot of the database. We do this by marking an 'IsDirty' field in a table, then set a timer for a few seconds down the road and run the backup in the timer (in a background thread). If another change happens before the timer is fired, we cancel the timer and set a new one. Works great except for the case where we have a transaction that takes longer than the timer timeout. On top of that, we have something that goes thru and checks for databases that are dirty that have been missed just to be sure. So here's my question. I found that I could easily end up in a state where, due to the amount of work going on, the backup is just going to restart over and over again. When running with shared-cache, we would get a bunch of BUSY or LOCKED results from backup_step. Now that it's using private cache, it never gets those. I ended up putting something that I feel like is a hack, using the sqlite3_backup_remaining call after each call to step, keep the last result and comparing that to determine if the number of pages remaining has increased and using that to indicate that the backup restarted. Then tossing the towel in after 10 tries, letting it get picked up at a less busy time. Is that a reasonable way to detect backups restarting? Is there a better way? On Oct 5, 2012, at 11:59 AM, Dan Kennedy wrote: > On 10/05/2012 04:29 AM, Jonathan Engle wrote: >> 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)? > > That sounds like it will work around the problem. If db1 is > using a private cache the problem cannot occur. > > Fix is here: > > http://www.sqlite.org/src/info/89b8c377a6 > > Should appear in 3.7.15. > > > >> >> >> 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 loc
Re: [sqlite] interesting deadlock.
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
Re: [sqlite] interesting deadlock.
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. 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 >>> >&
Re: [sqlite] Pragma Synchronous=OFF is not working
Try WAL mode. On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote: > Hi Pavel, > > Thanks for the information . I am using all insert under one transection > hence may be the reson its not effecting much . But do you suggest me the > way to increase more insert speed what i need to do in this scenario. Is > same happen for all pragma like Page_Size or Journal_Mode or Cache_size ? i > tried to change the values of all pragma but i think no one effect the > spped . > > In case i will bind the values and only prepare the stament once then do > you think any improvement ? > > Cheers > kritesh > > On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanovwrote: > >> If all your inserts are in one transaction then pragma synchronous = >> OFF won't affect your transaction speed too much. To understand >> whether this pragma works or not you should measure how long it takes >> to execute COMMIT (just this one statement). With synchronous = OFF >> COMMIT will be executed much faster. >> >> Pavel >> >> >> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh >> wrote: >>> Hi , >>> >>> I am executing all below mentioned pragma before start the (BEGIN >> --COMMIT) >>> transaction in sqlite version (3.6.4) >>> >>> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, ); >>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL, >> ); >>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL, >> ); >>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL, >> ); >>> >>> >>> In transaction , I am inserting the values in the table but I dnt know >> the >>> specific reason why does not pragma effecting the insert speed .. I am >>> getting the same speed even i use the pragma or not . please help >>> >>> Is these pragma effect take place in transaction ? >>> >>> Cheers >>> kritesh >>> >>> >>> >>> >>> -- >>> View this message in context: >> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> ___ >>> 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 >> > > > > -- > Regards > kritesh tripathi > ___ > 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
Re: [sqlite] interesting deadlock.
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] interesting deadlock.
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