However, sqlite should never be used in production. It is important to remember that sqlite is single flat file, which means any operation requires a global lock
I don't know jack about sqlite's locking architecture or scalability, but this statement is just silly. There are a conceptually infinite number of ways to make fine-grained locking work on a single file, both within a single process, a single host, or across a network. Maybe the author is thinking fcntl() locking is somehow the only option.
I guess the corrolary to this article has to be "Don't let your startup's sysadmins diagnose development-side issues."
"""
An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.
"""
But compared to something like MySQL w/ InnoDB (or postgres, or Cassandra, or BerkeleyDB), which all have something closer to Row Level or Page Level locking, SQLite's concurrency for server side applications is a serious deficiency.
Yes, there are lots of ways to have fine grained locking, SQLite just doesn't do them.
I guess the corrolary to this article has to be "Don't let your startup's sysadmins diagnose development-side issues."
You'll have to add "Make sure your developers can diagnose development-side issues" to the list as well. Most web app developers I have met do not know how to diagnose problems, or simply defer immediately to the sysadmins if there's no syntax errors or logs to refer to.
As someone else mentioned, Sqlite does indeed use lock per database file. But of course that is not an argument for it to never be used in production, but another argument for not letting sysadmins diagnose development-side issues - there are plenty of production type scenarios where Sqlite's locking is perfectly fine (but scenarios that are write heavy enough to cause lock contention is not one of them - if you use sqlite you do need to understand the locking and what workloads it is unsuitable for; especially since some bindings will give an error rather than wait for the lock)
I read an interesting performance comparison a while back (which I can't find now) which had some surprising results for sqlite with concurrent access. As I recall, it turned out that it was much faster to close the db connection and open it again for each operation than to keep a connection open and rely on the file locking to mediate access.
"any operation requires a global lock" is incorrect. Only inserts/changes lock the database; there can be multiple selects: http://www.sqlite.org/faq.html#q5
However, sqlite should never be used in production. It is important to remember that sqlite is single flat file, which means any operation requires a global lock
I don't know jack about sqlite's locking architecture or scalability, but this statement is just silly. There are a conceptually infinite number of ways to make fine-grained locking work on a single file, both within a single process, a single host, or across a network. Maybe the author is thinking fcntl() locking is somehow the only option.
I guess the corrolary to this article has to be "Don't let your startup's sysadmins diagnose development-side issues."