I had major problems with multithreaded access to a sqlite database file
on an NFS mount. While not exactly your scenerio there might be some
similarities. I had to come up with a crazy scheme to allow only one
thread to access the sqlite database at a time. See below.
I came up with a scheme where only one process would handle updating the
database directly. All other processes locally or remotely would update
the database through a file hand shaking protocol.
Here is an example
Database Updater Process (Server)
Database Client Process (Client)
Server defines two directories (queries and responses).
Client wants to insert, update or delete data from a database.
1. client creates a file with the necessary information
2. client moves file into queries directory
3. server sees new file in queries directory
4. server parses file
5. server inserts, updates or deletes data from database.
Client wants to select data from a database.
1. client creates a file with the appropriate sql statement
2. client moves file into queries directory
3. server sees new file in queries directory
4. server parses file
5. server preforms select statement
6. server creates response file
7. server moves response file into response directory
8. client sees new response file in response directory
9. client parses file
10. client obtains data
This scheme is preferred over sockets because if the database updater
process dies you won't lose information. All inserts, updates and
deletes will be sitting in the queries directory waiting for the
database updater process to start again.
This is just one solution to work around the NFS problem I was having.
If you find NFS does not work for you I would try either some sort of
sockets implementation or some sort of file hand shaking protocol.
While I thought this was originally an NFS only problem I'm not 100%
convinced it is. Anyway just thought I would let you know a work around
scheme that completely avoids locks.
Rafal Rusin wrote:
Maybe you want to call this:
" int sqlite3_busy_timeout(sqlite3*, int ms);
This routine sets a busy handler that sleeps for a while when a table
is locked. The handler will sleep multiple times until at least "ms"
milliseconds of sleeping have been done. After "ms" milliseconds of
sleeping, the handler returns 0 which causes sqlite3_exec() to return
SQLITE_BUSY.
Calling this routine with an argument less than or equal to zero turns
off all busy handlers. "
Like I said, I tried to use it without success. There was a deadlock
when I tried to use it with python pysqlite2 with 10 threads
simultanously accessing DB.
Best Regards,
Rafal Rusin
TouK Company
(www.touk.pl)
--
Software Engineer
[EMAIL PROTECTED]
301.286.7951
