Hello,
I encounter the following difficulties with sqlite3:
I have 4 different processes working on an sqlite3 database. When the "sql
timeout" (set by "sqlite3_busy_timeout") is set to 5 seconds and that all
processes are running on a same windows machine, it seems OK (I am not 100%
sure of that, need to do more tests).
When the configuration is the following:
3 processes are running on a windows XP pro (service pack 2) machine (with
NTFS filesystem)
The 4th process and the database are located on another windows machine
(same OS), NTFS filesystem.
I cannot let the sql timeout set to 5 seconds because I get a lot of
"database locked" errors; doing the same tests with a timeout set to 30
seconds really improves the results: I get really less "database locked"
errors so, I have set it to 1 minute and it works better again ( I got only
2 "database locked" messages whereas the 4 processes had been running and
working on the database (reading/writing and doing immediate transaction)
for 18 hours.
To give you an idea, with an sql timeout of 5 seconds, I got the first
"database locked" after les than 30 minutes activity. With a timeout set to
30 seconds, I got the "database locked" problem after more than 3 hours.
As I read in the sqlite3 documentation that when a process locks the
database using a reserved lock (which is what I use as I do "immediate"
transactions) it took only a few milliseconds, I am very surprise to find
that I need a such long timeout.
Does anyone have an explanation or has someone already had that problem?
To try to understand what happens, i have added many traces in my code (each
trace is written with the hour in the format hour:minute:second;
My code looks like this:
Trace1
sqlite3_exec(handle,"begin immediate transaction something");
trace2
...
Trace3
Sqlite3_exec(handle,"end transaction something");
Trace4
I have noticed that when a processus crashes dued to a "database locked", in
the log files of the other processes, at nearly the same hour, 20 seconds
pass between trace1 and trace2 or between trace3 and trace4 whereas usually
(I do many transactions like that) it doesn't even take a second.
What can cause a processus to take 20 seconds to execute "end transaction"?
Is the following scenario possible?
A process writes "trace3" in the log file then is put in a queue by the
windows OS. Transaction is still active so, the database is locked and other
processes ("woken up" by the windows OS) cannot do their "begin
transaction"=> they write "trace1" in their log file and start to wait (does
sqlite3 have its own process queue to handle that? I have the impression
that it is not the processus that has been waiting for the longuest time
that is given the lock).
20 seconds after, the windows OS finally wakes up our first process that is
now able to perform its "end transaction" and write "trace4" in the log
file. Other processes can now perform their "begin transaction" and write
"trace2" in the log file.
If that scenario is possible, then it should also happen when all 4
processes are running on the same machine...
Any information is welcome.
Thank you in advance.
