4 ms·

Please do not use SQLite for multiuser systems. You will have a bad time.

by Thetawaves 10y ago

koistya 10y ago
Why to build a car from the beginning, while you can start with a skateboard (free)? Lean startup, WTF :)
juskrey 10y ago
Why not. I am having a good time with this for years.
- Thetawaves 10y ago
  You must not be doing very many writes, because all writes lock the particular table from all other writers. Better databases have row level locking which allows many users to update the same table at the same time. - And this isn't all that strange, say you wanted to store a timestamp for the last time a user visited your site, session ids, etc.
  - juskrey 10y ago
    Good point. Still with thousands of users per day I can hardly experience any difficulties. I guess with a sane app architecture and coding this is not a problem up to a million/day.. for, say, some generic CMS site. UPD: Good explanation http://sqlite.org/whentouse.html http://sqlite.org/whentouse.html
  - Arcsech 10y ago
    > Better databases have row level locking which allows many users to update the same table at the same time. Nitpick: Databases that make different tradeoffs have row level locking. SQlite is very good, for the niche that it's in, which is a lightweight, embeddable database for primarily single-user applications. It's incredibly popular in that space, to the point of likely being the most-used database on the planet by number of running instances since it's in so many smartphones/embedded devices.
    - Thetawaves 10y ago
      None of the use cases you mention are 'multiuser' so I'm going to take this as just a helpful hint to the internet at large.
gecko 10y ago
In all seriousness, ever since SQLite got WAL support, it's been fine for light multiuser systems. I would define "multiuser" as "around ten or fewer concurrent users", and I think there are some major backup considerations that go with this, but I don't think it's an inherent nonstarter.
- Thetawaves 10y ago
  WAL still requires a table level write lock.
  - batbomb 10y ago
    SQLite uses only file locking.
    - rarrrrrr 10y ago
      That was true for a long time, but newer sqlite can do table level locking when in shared cache mode. Section 2.2 here: https://www.sqlite.org/sharedcache.html https://www.sqlite.org/sharedcache.html

Please do not use SQLite for multiuser systems. You will have a bad time.

koistya 10y ago

Why to build a car from the beginning, while you can start with a skateboard (free)? Lean startup, WTF :)

juskrey 10y ago

Why not. I am having a good time with this for years.

Thetawaves 10y ago
You must not be doing very many writes, because all writes lock the particular table from all other writers. Better databases have row level locking which allows many users to update the same table at the same time. - And this isn't all that strange, say you wanted to store a timestamp for the last time a user visited your site, session ids, etc.
- juskrey 10y ago
  Good point. Still with thousands of users per day I can hardly experience any difficulties. I guess with a sane app architecture and coding this is not a problem up to a million/day.. for, say, some generic CMS site. UPD: Good explanation http://sqlite.org/whentouse.html http://sqlite.org/whentouse.html
- Arcsech 10y ago
  > Better databases have row level locking which allows many users to update the same table at the same time. Nitpick: Databases that make different tradeoffs have row level locking. SQlite is very good, for the niche that it's in, which is a lightweight, embeddable database for primarily single-user applications. It's incredibly popular in that space, to the point of likely being the most-used database on the planet by number of running instances since it's in so many smartphones/embedded devices.
  - Thetawaves 10y ago
    None of the use cases you mention are 'multiuser' so I'm going to take this as just a helpful hint to the internet at large.

gecko 10y ago

In all seriousness, ever since SQLite got WAL support, it's been fine for light multiuser systems. I would define "multiuser" as "around ten or fewer concurrent users", and I think there are some major backup considerations that go with this, but I don't think it's an inherent nonstarter.

Thetawaves 10y ago
WAL still requires a table level write lock.
- batbomb 10y ago
  SQLite uses only file locking.
  - rarrrrrr 10y ago
    That was true for a long time, but newer sqlite can do table level locking when in shared cache mode. Section 2.2 here: https://www.sqlite.org/sharedcache.html https://www.sqlite.org/sharedcache.html