hello,
how should one ensure correctness when multiple processes accesses one single SQLite database file?
Show me the code please :-)
/tom
views:
926answers:
5
+2
A:
Any SQLite primitive will return SQLITE_BUSY if it tries to access a database other process is accessing at the same time. You could check for that error code and just repeat the action.
Alternatively you could use OS synchronization - mutex on MS Windows or something similar on other OSes. The process will try to acquire the mutex and if someone else already holds it the process will be blocked until the other process finishes the operation and releases the mutex. Care should be taken to prevent cases when the process acquires the mutext and then never releases it.
sharptooth
2009-06-30 12:46:21
Hmm, does not SQLite provide locks for protecting the database file access??
2009-06-30 13:18:26
+6
A:
First, avoid concurrent access to sqlite database files. Concurrency is one of sqlite's weak points and if you have a highly concurrent application, consider using another database engine.
If you cannot avoid concurrency or drop sqlite, wrap your write transactions in BEGIN IMMEDIATE; ... END;. The default transaction mode in sqlite is DEFERRED which means that a lock is acquired only on first actual write attempt. With IMMEDIATE transactions, the lock is acquired immediately, or you get SQLITE_BUSY immediately. When someone holds a lock to the database, other locking attempts will result in SQLITE_BUSY.
Dealing with SQLITE_BUSY is something you have to decide for yourself. For many applications, waiting for a second or two and then retrying works quite all right, giving up after n failed attempts. There are sqlite3 API helpers that make this easy, e.g. sqlite3_busy_handler() and sqlite3_busy_timeout() but it can be done manually as well.
You could also use OS level synchronization to acquire a mutex lock to the database, or use OS level inter-thread/inter-process messaging to signal when one thread is done accessing the database.
laalto
2009-06-30 13:44:57
I know that one must use transactions within one process. However, my situation is that I have multiple processes, in contrast to multiple threads, accessing the same DB concurrently. Does SQLite transactions really handle such concurrency!?!?
2009-06-30 14:10:24
@Tom: Yes, down in the sqlite3 OS-specific porting layer there is functionality for locking that works across processes. See http://www.sqlite.org/lockingv3.html for more
laalto
2009-06-30 14:16:44
I read that FAQ before I posted my question here. I should of course have said that (this was lesson number two that I've learned today).I could not see exactly what they meant. Am I supposed to handle all the locking stuff myself, or does SQLite have support for it?"When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business." It would take some effort to implement this for myself.
2009-06-30 14:15:29
A:
Basically you need to wrap your data access code with transactions. This will keep your data consistent. Nothing else is required.
In SQLite you are using
BEGIN TRANSACTION
COMMIT TRANSACTION
pairs to delimit your transactions. Put your SQL code in between in order to have it execute in a single transaction.
However, as previous people have commented before me - you need to pay close attention for concurrency issues. SQLite can work reasonably fast if it used for read access (multiple readers are not blocked and can run concurrently).
However - the picture changes considerably if your code interleaves write and read access. With SQLite - your entire database file will be locked if even a single writer is active.
Good Luck
Liron Levi
(Creator of the SQLite Compare diff/merge utility)
Liron Levi
2009-07-27 09:13:27