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?

SteelToe
  • 1,539
  • 3
  • 13
  • 22
  • 5
    related (possibly a duplicate): [SQLite with two python processes accessing it: one reading, one writing](http://softwareengineering.stackexchange.com/questions/213799/sqlite-with-two-python-processes-accessing-it-one-reading-one-writing) – gnat Jan 19 '17 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: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems – Thomas Carlisle Jan 21 '17 at 17:10

2 Answers2

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().

whatsisname
  • 27,463
  • 14
  • 73
  • 93
Jonathan Eunice
  • 9,710
  • 1
  • 31
  • 42
  • 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 '17 at 21:21
  • 13
    Also, some of the environments that SQLite executes in may not even support multiple processes. – david25272 Jan 19 '17 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 '17 at 22:39
  • The authors of SQLite must have decided that even if all the writers are trying to be well behaved, it is too hard to allow more than one to write. – bdsl Jan 19 '17 at 22:40
  • 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 '17 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 '17 at 00:28
  • Managing (well) the locking and multi-version concurrency control in multi-user databases requires the extra training and skill of DBAs. SQLite is designed to require virtually no management and thus provides the performance and query power of a large RDBMS in a form the average programmer can manage. – DocSalvager Jan 27 '17 at 08:21
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.

Lightness Races in Orbit
  • 8,755
  • 3
  • 41
  • 45
Stop harming Monica
  • 835
  • 1
  • 8
  • 11
  • 2
    This explains the technical reason why concurrent writes are not allowed, but not why the design decision was made. – Robert Harvey Jan 19 '17 at 20:53
  • Not sure what you mean. The decision of making it serverles? The decision of using files for storage? – Stop harming Monica Jan 19 '17 at 21:17
  • 3
    The decision to design SQLite so that it only handles one write at a time. – Robert Harvey Jan 19 '17 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). –  Jan 19 '17 at 22:11
  • @Snowman At most that would improve concurrency between threads of a single process, not inter-process. Anyway SQLite is single-threaded AFAIK and there is a rationale for that, see my edit. – Stop harming Monica Jan 19 '17 at 22:45
  • Also, for what it's worth, the link to "threads are evil" doesn't answer the question either. That's a completely different design consideration (thread safety), and has nothing to do with concurrent writes. – Robert Harvey Jan 19 '17 at 22:54
  • @RobertHarvey the link was not intended to answer the question. I don't give links as answers. The answer to "why doesn't SQLite use threads to improve concurrency?" is "Because Dr Hipp thinks that threads are evil". The link is provided as proof that Dr Hipp thinks so. Hope it's clearer now. – Stop harming Monica Jan 19 '17 at 23:23
  • You're answering the wrong question. The question is not "why doesn't SQLite use threads to improve concurrency." The question is "why are concurrent writes not allowed on an SQLite database," which is not the same question at all, and doesn't have the same answer. – Robert Harvey Jan 19 '17 at 23:26
  • @RobertHarvey I don't claim they are the same question or that they have the same answer. My answer to the original question is different. – Stop harming Monica Jan 19 '17 at 23:54
  • Why are you trying to answer a question that wasn't asked? If you're responding to @Snowman, you should do so in the comments, where the conversation originated (not in the answer, where it is irrelevant). – Robert Harvey Jan 19 '17 at 23:55
  • @RobertHarvey Because it illustrates a fact that in my opinion is very relevant to the original question: using threads to support concurrent writers is not an option by design. Snowman found a flaw in my original answer and I tried to fix it. – Stop harming Monica Jan 20 '17 at 00:31
  • 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 '17 at 06:21
  • 1
    Microsoft Access has this ability without a server or the application itself and can be locked at the row level. – JeffO Jan 20 '17 at 11:29
  • 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 '17 at 12:13
  • 1
    @Mat Internal thread makes perfect sense if one wants multiple simultaneous writers in a single process. You are correct that SQLite also supports locking across several processes - but locking is, by definition, not simultaneous access. Though I guess one could do something funny with the "byte range locking" that is available on some platforms. – jpa Jan 20 '17 at 12:18
  • @jpa: if you want simultaneous _anything_ in a process you need threads (handwaves signals/asynch IO/...). I read "internal thread" as something that SQLite would have to implement (a "manager thread" or whatever") - that is not necessary any more than a "server process" - the source of concurrent writes are user threads/processes, those issuing the SQL. There is no theoretical need for a server (process or thread) to handle those. – Mat Jan 20 '17 at 12:26
  • 1
    You don't need a "server" architecture to have access to a single file handle concurrent writes, but you do need that functionality to exist somewhere in the application stack. SQLite was designed as it was, and specifically not to put that functionality in. The rationale for that decision would need to come from Dr. Hipp, but since it is in wide use for many years I think it is safe to assume there are plenty of use cases where SQLite fits the requirements well. The question was "Why was SQLite designed liked this?" not "what would have to be done to transform SQLite in this manner?" – Thomas Carlisle Jan 21 '17 at 17:20