4 ms·

Thank you for the article, but I do have one quibble: > We could ensure that any process takes out an exclusive lock > on a file before reading or writing

Thank you for the article, but I do have one quibble: > We could ensure that any process takes out an exclusive lock > on a file before reading or writing it, or we could push all > operations through a single flow control point so that they > only run one at a time. Not only are these workarounds slow, > but they won’t scale up to allow us to make our database fully > ACID-compliant As much as I like Postgres, I also like SQLite, which uses a simple file lock and yet is utterly acidic (https://www.sqlite.org/transactional.html https://www.sqlite.org/transactional.html). From SQLite's FAQ: > SQLite allows multiple processes to have the database file > open at once, and for multiple processes to read the database > at once. When any process wants to write, it must lock the > entire database file for the duration of its update. But that > normally only takes a few milliseconds. . . . > > If your application has a need for a lot of concurrency, then > you should consider using a client/server database. But > experience suggests that most applications need much less > concurrency than their designers imagine. --- https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5

felixge 9y ago
AFAIK SQLite doesn't achieve atomicity by virtue of its global write lock. It does so by using a rollback journal (default) or WAL. So it's not really all that different from PostgreSQL in that regard and the authors point remains valid :). You might also enjoy this talk by the main author of SQLite: https://www.youtube.com/watch?v=ZvmMzI0X7fE https://www.youtube.com/watch?v=ZvmMzI0X7fE (SQLite: Protégé of PostgreSQL)
- combatentropy 9y ago
  The paragraph I quoted is talking about two things, one right after the other: concurrency and acidity. First, it talks about concurrency. It says that a naive solution to concurrency is file locking. It says this is slow. So I first trot out SQLite as an example that is not slow. It says, "Not only are these workarounds slow, but they won't scale up to allow us to make our database fully ACID-compliant." To me it sounded like he said choosing file-locking for concurrency will mop you into a corner, that you won't ever become ACID, regardless of what you tack on, like a journal. One other clarification about concurrency: SQLite says it's fast enough for most websites. Then it turns around and says that if you need a lot of concurrency, you should go with another database. The contradiction evaporated when it dawned on me that when database makers say "concurrency," they're talking about many users at a time connecting to a database directly: picture a bunch of SQL gurus connecting directly to an SQLite file and issuing raw SQL statements. Or replace the gurus with several "application" servers that all write to a database on yet another server. For that, SQLite would be bad, we all agree, including SQLite. But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html, see Server-side database). Anyway, again, I'm not against Postgres. It's what I use. I'm not talking about SQLite because I'm invested in it but because I admire it. It's just an example of a simple solution that meets many people's needs, without resorting to more complexity.
  - kuschku 9y ago
    > But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html, see Server-side database). It doesn’t. At all. The quassel project supports PostgreSQL and SQLite as backends. And, as expected when you use a database as log storage for an IRC bouncer, you end up with many concurrent threads writing and reading to the same table. The result is that if a user is reading a table right now, no one can write to it - and that, in turn, means that the thread receiving messages (and responding to PINGs, which is required to keep the connection to the IRC server alive!) gets blocked, and has to wait on the threads currently requesting messages. So if you connect and set your client to, on connection to the bouncer, request the last 500 messages for each channel, you might actually time out. Yes. When you have enough data, then connecting to the bouncer can kill the bouncers other connections. If you have more than one thread doing writes or reads – in a usual web application you’ll have dozens or hundreds of worker threads reading and writing concurrently – then SQLite just isn’t useful. Disclaimer: Not speaking for the quassel project here. This comment is just my opinion.
    - combatentropy 9y ago
      > if a user is reading a table right now, > no one can write to it In 2010 SQLite came out with a mode that keeps the writer from blocking readers: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html > > 99% [...] SQLite would still hold up > an IRC bouncer I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for. https://www.sqlite.org/features.html https://www.sqlite.org/features.html
      - kuschku 9y ago
        > I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for. The issue with those is still that if you have a too high writing load, SQLite will become a bad fit. It’s perfect for any low writing, high read situations, as many websites are, but for more dynamic applications it seems to be less usable. Also, the FAQ of SQLite still lists > SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

felixge 9y ago

AFAIK SQLite doesn't achieve atomicity by virtue of its global write lock. It does so by using a rollback journal (default) or WAL. So it's not really all that different from PostgreSQL in that regard and the authors point remains valid :). You might also enjoy this talk by the main author of SQLite: https://www.youtube.com/watch?v=ZvmMzI0X7fE https://www.youtube.com/watch?v=ZvmMzI0X7fE (SQLite: Protégé of PostgreSQL)

combatentropy 9y ago
The paragraph I quoted is talking about two things, one right after the other: concurrency and acidity. First, it talks about concurrency. It says that a naive solution to concurrency is file locking. It says this is slow. So I first trot out SQLite as an example that is not slow. It says, "Not only are these workarounds slow, but they won't scale up to allow us to make our database fully ACID-compliant." To me it sounded like he said choosing file-locking for concurrency will mop you into a corner, that you won't ever become ACID, regardless of what you tack on, like a journal. One other clarification about concurrency: SQLite says it's fast enough for most websites. Then it turns around and says that if you need a lot of concurrency, you should go with another database. The contradiction evaporated when it dawned on me that when database makers say "concurrency," they're talking about many users at a time connecting to a database directly: picture a bunch of SQL gurus connecting directly to an SQLite file and issuing raw SQL statements. Or replace the gurus with several "application" servers that all write to a database on yet another server. For that, SQLite would be bad, we all agree, including SQLite. But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html, see Server-side database). Anyway, again, I'm not against Postgres. It's what I use. I'm not talking about SQLite because I'm invested in it but because I admire it. It's just an example of a simple solution that meets many people's needs, without resorting to more complexity.
- kuschku 9y ago
  > But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html, see Server-side database). It doesn’t. At all. The quassel project supports PostgreSQL and SQLite as backends. And, as expected when you use a database as log storage for an IRC bouncer, you end up with many concurrent threads writing and reading to the same table. The result is that if a user is reading a table right now, no one can write to it - and that, in turn, means that the thread receiving messages (and responding to PINGs, which is required to keep the connection to the IRC server alive!) gets blocked, and has to wait on the threads currently requesting messages. So if you connect and set your client to, on connection to the bouncer, request the last 500 messages for each channel, you might actually time out. Yes. When you have enough data, then connecting to the bouncer can kill the bouncers other connections. If you have more than one thread doing writes or reads – in a usual web application you’ll have dozens or hundreds of worker threads reading and writing concurrently – then SQLite just isn’t useful. Disclaimer: Not speaking for the quassel project here. This comment is just my opinion.
  - combatentropy 9y ago
    > if a user is reading a table right now, > no one can write to it In 2010 SQLite came out with a mode that keeps the writer from blocking readers: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html > > 99% [...] SQLite would still hold up > an IRC bouncer I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for. https://www.sqlite.org/features.html https://www.sqlite.org/features.html
    - kuschku 9y ago
      > I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for. The issue with those is still that if you have a too high writing load, SQLite will become a bad fit. It’s perfect for any low writing, high read situations, as many websites are, but for more dynamic applications it seems to be less usable. Also, the FAQ of SQLite still lists > SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.