I am doing database programming using Java with SQLite.
I have found that only one connection at a time to the database has write capabilities, while many connections at once have read capability.
Why was the architecture of SQLite designed like this? As long as the two things that are being written are not being written to the same place in the database, why can't two writes occur at once?
Best Answer
Because "multiple concurrent writes" is much, much harder to accomplish in the core database engine than single-writer, multiple-reader. It's beyond SQLite's design parameters, and including it would likely subvert SQLite's delightfully small size and simplicity.
Supporting high degrees of write concurrency is a hallmark of large database engines such as DB2, Oracle, SQL Server, MySQL, PostgreSQL, NonStop SQL, and Sybase. But it's technically hard to accomplish, requiring extensive concurrency control and optimization strategies such as database, table, and row locking or, in more modern implementations, multi-version concurrency control. The research on this problem/requirement is voluminous and goes back decades.
SQLite has a very different design philosophy from most of those server-centric DBMSs that support multiple writers. It's designed to bring the power of SQL and the relational model to individual applications, and indeed to be embeddable within each application. That goal requires significant tradeoffs. Not adding the significant infrastructure and overhead needed to handle multiple concurrent writers is one of those.
The philosophy can be summed up by a statement on SQLite's appropriate uses page: