On 24/06/2012 12:55 PM, Simon Slavin wrote:
Depending on the order and timing of how the two threads/processes run,
something will eventually happen to your second writer. It will probably reach
whatever timeout you've set (which defaults to zero) and then return a result
code indicating either 'locked' or 'busy'. As the programmer it's your job to
be checking your result codes and taking appropriate action, but if your
application does crash out and you haven't closed your connections properly,
SQLite will notice this the next time it opens the database, and what you'll
see is everything rolled back to the last COMMITted transaction.
Yes, I do check for those codes and I have loops in the code to re-try
the statement until it succeeds or too many retries occurs (but this is
never hit in my case). So, if the db is busy, I Sleep(1) and retry. I'm
not using the built-in busy handler.
You should be able to test this, using the sqlite3 shell utility tool,
downloadable from the SQLite download page. You can open two sessions in two
windows and try to get one to lock the other out, or crash out of one session
to see what happens to the other. Or you could run your own app in one window,
and use the shell tool to try to mess with your application and make it crash.
Use a dummy test copy of your database, of course.
There are any number of ways to get SQLite to return error codes. What has us
concerned is that you are reporting that you've found a way to make SQLite
return the wrong result /without/ returning an error code. That's supposed to
be impossible.
I'd probably be best off to write a simple program to do this. The idea
is to have this program write to the DB, do a count and read from the db
and report what it's reading/writing along with sql result codes. Then,
run two instances of the app on the same db and crash one of those
instances at random. I don't think I can do the same using the sqlite3
shell utility and my main app is too large for constant testing like this.
I also wonder if my increased WAL size has anything to do with it (5000
pages) - flushing will take longer than default, so there is more chance
of things going wrong during the flush, I suppose?
I had transactions in my code previously, but I removed them after switching to
WAL, so at least that's one less worry.
SQLite can only access a database inside a transaction (not only changes, but
also SELECT). If you issue a SQL command without having first issued a BEGIN,
SQLite wraps your command inside a BEGIN … END structure.
I understand that, I meant I had explicit transactions surrounding
multiple statements.
Dennis
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
