SQLite and concurrency [closed]

Question

I need to integrate a database in one of our products and I wonder which one would be more suited to our needs (easy automatic deployment, no administration, good performance), and sqlite seems to be a good solution. The problem is that the database could potentially face high concurrency issues: it is accessed through PHP (Apache) each time a client connects to the server the database is running on. One client connects (and execute an INSERT query) approximatively every 10 seconds to the server, and it could possibly have more than 100 clients running.

When executing an INSERT query, sqlite locks the entire database at a certain time for a certain duration. Is there a way to compute that duration? If this is not possible, do you think sqlite (v3.3.7) is still adapted with the above conditions?

Answer 1

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.