I have inherited a large Sqlite project recently. The project is
multi-threaded, but has thus far kept Sqlite calls in a single thread.
Recently, I have complicated this somewhat by adding a second thread that deals
with other, essentially independent databases. No connections, statements, etc.
are shared. Each thread operates on independent .DB files.
Nevertheless, I am getting some very puzzling errors. In particular, there are
cases where sqlite3_prepare() is the first call to cause an error, typically
SQLITE_MISUSE. Here are more details:
The main thread (i.e. the one that has always interacted with Sqlite)
periodically closes its DB file, renames it, and moves on to open and write to
a new database using the same file name as the original file that was just
renamed. The renaming is done at the file level, using a Windows API call.
After all of this is complete (from the "close" of the first database to the
"open" for the new one), a new thread is created by the first thread. This
second thread then opens the renamed .DB file, and creates a third .DB file
with yet another name. This thread then uses SELECT and INSERT to copy a subset
of the second .DB file's data into this third .DB file.
For example, suppose thread 1 is writing Info.db. After some time, it will
close this database, rename Info.db to be Info.0415Mar090040.db using an API
call, and then re-create Info.db, open it, and start writing it. After doing
this, it will create a second thread that will open Info.0415Mar090040, create
a new database called Info.0415Mar090040.Incident.db, and copy a subset of the
former to the latter using SQL commands.
So, each database should be used exclusively by a single thread. Assuming each
thread calls sqlite3_prepare, sqlite3_bind, sqlite3_finalize, etc. in the
correct sequence, then everything should always work regardless of how the
threads are time-sliced.
Unfortunately, this is not the behavior I have observed. Instead, things seem
to work most of the time, but behavior differs from run to run. This makes me
suspect some kind of threading problem. So, my questions are:
1) Is my basic threading model workable? For example, if there's some kind of
database name string stored internally that makes (using my example above)
Info.db and Info.0415Mar090040.db to be really "the same" in some way, even
after the latter is renamed, then that would explain my problems. In that case,
I would presumably need to make a renamed copy of the database using Sqlite
calls.
2) How can sqlite3_prepare() ever return SQLITE_MISUSE for a statement that was
just successfully finalized? Specifically, how can the following ever reach the
second call to exit(1)?:
// (Done using pStmt against dbSrc)
rc = _sqlite3_finalize(pStmt);
if(SQLITE_OK != rc)
{
exit(1);
}
sprintf(szQuery,"SELECT * FROM ACCESSORIES");
rc = _sqlite3_prepare(dbSrc,szQuery, -1, &pStmt, 0);
if(SQLITE_OK == SQLITE_MISUSE)
{
exit(1);
//How can this ever happen? If there was a problem with
//the finalize call, then the program should have exited then.
//Does this imply that the threads, and their databases, are
//really connected somehow?
}
3) How am I supposed to troubleshoot this? Presumably *pStmt was in an
inappropriate state, and it would be nice to know what state that was.
Alternately, I guess SQLITE_MISUSE would be the appropriate error is dbSrc were
actually closed, and I would like to develop the troubleshooting skills to
detect whether this is the case or not. (It doesn't seem to be in this bug,
i.e. dbSrc seems to be open).
4) Is there an update I should apply? The version of Sqlite3.h that I am
including contains the defines SQLITE_VERSION as 3.4.0, but I also seem to be
missing a few things, such as sqlite_prepare_v2(), sqlite3_threadsafe(), and
sqlite3_stmt _status(). So, I am not at all sure that I'm using exactly what I
should be for v3.4.0. (And I think that some of the functions I mentioned might
help my basic problem.)
Many thanks in advance for your help. To some extent, I can and will continue
debugging this (and perhaps answering the above questions) on my own. But at
this point I'm really overwhelmed and far behind my schedule, and really want
some help with Sqlite. If I start complaining about it, no doubt management
will request I use something more "full-featured," and I think that would be a
shame.
________________________________
The information contained in this e-mail is privileged and confidential
information intended only for the use of the individual or entity named. If you
are not the intended recipient, or the employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
any disclosure, dissemination, distribution, or copying of this communication
is strictly prohibited. If you have received this e-mail in error, please
immediately notify the sender and delete any copies from your system.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
