SQLite and concurrency

I don't think that SQLite would be a good solution for those requirements. SQLite is designed for local and lightweight use only, not to serve hundreds of requests.

I would recommend some other solution, for example MySQL or PostgreSQL, both can be scripted quite well. So, if I were you, I would put my efforts into the setup scriptings.

To avoid the flame war between SQLite believers and haters, let me draw draw your attention to the often referred SQLite When-To-Use document (I believe it is considered as a credible source). Here they state the following:

Situations Where A Client/Server RDBMS May Work Better

High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

I think that in the referred question involves many writes and if the OP would go for SQLite, it would result a non-scalable solution.

Here is what SQLite has to say about appropriate uses of SQLite: http://www.sqlite.org/whentouse.html In particular, that page says SQLite is good for low the medium traffic sites, exactly the sort of application that you're contemplating.

Seems like SQLite would work for you, unless you expect substantial growth. Depending on what you do in each request, I would expect that a query rate of 0.17 queries per second to be well within SQLite's capabilities.

For good user experience, you should design your site so that queries needed to service a single request take ~ 200 milliseconds. To achieve this, result sets should probably not touch more than a few score rows; and should rely on indeces, not full table scans. If you hit that, then you'll have enough headroom to serve 5 queries per second (at peak). That's 30x the requirement that you state in your question.

I try to avoid emotive replies and hyperbole but I am truly astonished at the lack of knowledge about sqlite displayed on this page. Different database implementations serve different needs and from the operational specs you provide, sqlite3 seems ideal for your needs. To elaborate:

sqlite3 is fully ACID compliant, meaning it ensures atomic commits, which is something neither MySQL (good as it may be) nor Oracle can brag about. See more here

Also, sqlite3 has a deceptively simple mechanism for ensuring maximum concurrency (which is also thread-safe) as described in their File locking and Concurrency document.

By their (sqlite3 developers') own estimation, sqlite3 is capable of up to 50,000 INSERTs per second - a theoretical maximum which is limited by disk rotation speed. ACID compliance requires sqlite3 to confirm that a database commit has been written to disk, so an INSERT, UPDATE or DELETE transaction requires two full disk rotations, thereby effectively reducing the number of transactions to 60/s on a 7200rpm diskdrive. This is outlined in the sqlite FAQ linked in another answer and the fact gives some idea of the engine's data throughput capability in production. But what about concurrent reading and writing?

The File locking and Concurrency document linked earlier, explains how sqlite3 avoids "writer startvation" - a condition whereby heavy database read access prevents a process/thread seeking to write to the database from acquiring a lock. The escalation of locking state from SHARED to PENDING to EXCLUSIVE happens as sqlite3 encounters an INSERT (or UPDATE or DELETE) statement and then again upon COMMIT, meaning that the full database lock is delayed to the last moment before an actual write is performed. The outcome of sqlite's clever mechanism for handling file locking means that should a writer join the queue (PENDING lock), existing reads (SHARED locks) will complete, grant an EXCLUSIVE lock to the writer process and then resume reading. This takes only a few milliseconds, meaning that the effective transaction throughput will hardly move from the 60/s rate quoted above.

I believe the default sqlite3 WAIT on an EXCLUSIVE lock is 3 seconds, so given the fact that 60 transactions per second is a reasonable expectation and that you seek to write to the database on average once every 10 seconds - I'd say sqlite3 is well up to the task and will only require the introduction of clustering once your traffic increases by a factor of 500.

Not bad and perfect for your requirement.