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 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 [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users
mailing list [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users
mailing list [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users
mailing list [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users
mailing list [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing
list [email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
