SQLite - update via a Web App vs Client/Server app

(1) Since a SQLite database is a single file, when SQLite is used, is there a difference between the following two scenarios ? -

Case A - 2 users making an Update to the same database using a Web Application

Case B - 2 WinForms apps making an Update to the same database (i.e Client/Server model)

This question came to me after reading the following FAQ question on SQLite FAQ - http://www.sqlite.org/faq.html#q5 - Can multiple applications or multiple instances of the sam开发者_运维知识库e application access a single database file at the same time?

"Multiple processes can have the same database open at the same time....."

(2) In Case A (web app), is the number of processes connecting to the database = 1? While in Case B (client/server), is it 2 ?

(3) For Case A, will it make a difference if the SQLite database file is on the same machine as the web server versus one on a network share ?

Multiple processes can be reading the database at the same time, performing queries and such, but writes can only be one-at-a-time. Usually the first process to open the the database gets to write to it and others who attempt to write get a SQLITE_BUSY, which you can handle with the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

Be aware that after a successful write to the sqlite db, data that was read by other processes on the same database may now be stale (the cache, as it were, is now incoherent). You'll need to handle that scenario.

Cases A and B could be both 1 or 2: it depends on whether the web application and client-server app are accessing the database concurrently or sequentially. Since you don't know a priori which will happen, you should assume concurrent access and handle accordingly.

The sqlite database can be on a network share or not; the locking mechanism is internal.

You may also want to consider using Berkeley DB. The Berkeley DB SQL API is SQLite compatible, but the underlying locking mechanism allows concurrent writers to co-exist at the same time. Like SQLite, Berkeley DB is a library which links into your application. BDB also supports a mix of concurrent threads or processes accessing the database.

There is are a couple of interesting white papers that compare and contrast Berkeley DB against SQLite. There's a Benefits and Integration white paper as well as a Performance Comparison white paper, written by Mike Owens, the author of The Definitive Guide to SQLite.