For the person having difficulty with locking and busy with Sqlite it is
important to recognize the LITE in the name. A single file is used to
hold the entire database and each user process has that file open which
means that the locking is restricted to file locks, which essentially
lock the entire file and hence the entire database.
The locking is essential if you dont want one process stomping all over
the changes to the database being simultaneoulsy made by another process.
If you want to have a great deal of concurrency, don't use SQLITE, use
PostgreSQL. If you want the simplicity, ease of management and the
small footprint of Sqlite, you need to work out a process whereby
multiple user processes and threads can co-operate to access the Sqlite
database without clashing.
If all the processes are co-operative you can sync with a semaphore or
mutex like device. If they don't you must rely on the file locking and
handle the BUSY and LOCKED messages to allocate control of the database
to one process or thread at a time by having all bar one process yield
when there is a contention. If two processes want to submit a
transaction, one must basically wait until the other finishes.
I find that syncing processes with a semaphore and threads with events
provides an efficient and speedy control of Sqlite concurrent access,
subject to the entire application obeying the rules. Polling is avoided
as is BUSY logic in the API calls. Waits are eliminated and blocking is
reduced to the minimum necessary to eliminate clashes.
- [sqlite] Sqlite Locking and Busy John Stanton
-
