3 ms·

> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https

> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution. If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application. That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.

TekMol 9y ago
SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something. No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.
- wyoung2 9y ago
  > What happens if process A reads from a file while process B writes it? If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens. You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.

TekMol 9y ago

SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? I'm not so much concerned about durability. More about SQLite not responding to "SELECT v FROM t WHERE id=123" with value v but instead with something like "Error: v is currently being written by another process. Try again later" or something. No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.

wyoung2 9y ago
> What happens if process A reads from a file while process B writes it? If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens. You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.