83

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?

Improve this question
2
  • 5
    related (possibly a duplicate): SQLite with two python processes accessing it: one reading, one writing
    – gnat
    Jan 19, 2017 at 20:27
  • 5
    Because SQLite was designed to be "lite". Low memory and low processing with performance. Think through how you would make SQLite handle multiple writes to the same file. The current design is easy to implement -- the entire file is locked and others have to wait. To handle write concurrency at a lower level of granularity requires row/page locking which you get from the RDMS's. If the requirement demands write concurrency, then SQLite isn't a candidate and instead, you should look to lightweight RDBMS: en.wikipedia.org/wiki/…
    – Thomas Carlisle
    Jan 21, 2017 at 17:10

2 Answers 2

Reset to default
163

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:

SQLite does not compete with client/server databases. SQLite competes with fopen().

Improve this answer
7
  • 42
    +1. SQLite is an in-process database. There is no central arbiter like there is in a server-based DB. The writers would have to directly cooperate with and trust each other. A single malicious writer could wreak havoc just by not cooperating.
    – Jörg W Mittag
    Jan 19, 2017 at 21:21
  • 13
    Also, some of the environments that SQLite executes in may not even support multiple processes.
    – david25272
    Jan 19, 2017 at 22:31
  • 1
    Presumably a malicious write can already wreak havoc by not cooperating. They can't use the SQLite code to do it, but they can have their own code, which could just be a call to unlink()
    – bdsl
    Jan 19, 2017 at 22:39
  • 12
    In concurrent apps, there isn't a lot of middle ground. Either you get concurrency, consistency, and data integrity right essentially 100% of the time, including under challenging conditions and edge cases, or you don't. If you don't, it's fragile, slow, and crash-prone, and people stop trusting it very quickly. But getting it routinely right is fiendishly hard. There aren't that many circumstance in which writers can, absent oodles of core support, coordinate interleaved writes on their own.
    – Jonathan Eunice
    Jan 19, 2017 at 22:50
  • 8
    @bdsl Databases of any sort have to assume writers won't be well-behaved so they don't lose data. The authors SQLite position it as a competitor to fopen(), so consider all of the hairiness that comes with concurrent writing to a plain text file.
    – Blrfl
    Jan 20, 2017 at 0:28
12

Because there's no server which can tell whether things are to be written to the same place or not. There are just two processes trying to write to a file.

As pointed in a comment, concurrent writes could also be supported by an internal thread. Not sure how well this would work (did not think much about it either). Anyway here is why SQLite does not use threads: Dr Hipp thinks that threads are evil.

The fact that DR Hipp thinks that threads are evil is documented in the SQLite FAQ.

Improve this answer
17
  • 2
    This explains the technical reason why concurrent writes are not allowed, but not why the design decision was made.
    – Robert Harvey
    Jan 19, 2017 at 20:53
  • 3
    The decision to design SQLite so that it only handles one write at a time.
    – Robert Harvey
    Jan 19, 2017 at 21:38
  • 7
    @Goyo a server is not required to handle concurrent writes: much as a database server is a separate process, there could be a separate thread internal to SQLite that serves the same purpose. Robert Harvey is correct: there was a design decision made by the SQLite team, and it has nothing to do with the ability of a single library to handle concurrent writes (because it can, in theory).
    – user22815
    Jan 19, 2017 at 22:11
  • 1
    This answer seems fine to me. To handle concurrent writes, it seems one would need to have either a server, or a multi-thread sqlite implementation. Because both of those have been ruled out by other design decisions, implementing concurrent writes would be exceedingly difficult.
    – jpa
    Jan 20, 2017 at 6:21
  • 5
    @jpa: SQLite manages to synchronize locking between several processes/threads without a "server" already. A "server" isn't required - using OS-provided locking/synchronization/IPC/whatever is sufficient, but gets complex real fast. The "internal thread" mentioned in the post doesn't make sense.
    – Mat
    Jan 20, 2017 at 12:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.