4 ms·

> ...I periodically hear about projects that use/have used sqlite as their sole datastore. SQLite==exclusive access, no sharing, unless read-only. Basically,

> ...I periodically hear about projects that use/have used sqlite as their sole datastore. SQLite==exclusive access, no sharing, unless read-only. Basically, it provides a SQL convenience for local usage.

sicp-enjoyer 4y ago
If you need to safely have multiple processes read and write to the same data, it does that great. The writes are serialized, but that's typically how an in-memory shared resource would be implemented as well. Do you mean shared across networks?
- chasil 4y ago
  I have seen SQLite disconnect random sessions of multiple writers, rather than blocking them. Setting WAL mode made no difference. This database can safely be used by a single writer at all times. To implement the serialization that SQLite does not, do this in the POSIX shell: until mkdir ~/.dpapplock 2> /dev/null do sleep 3 done trap "rmdir ~/.dbapplock" EXIT sqlite3_app ... The mkdir() system call is defined as atomic by POSIX, so the shell can safely serialize for you, assuming there are never more than a handful.
- zoomablemind 4y ago
  My understanding is that SQLite suppprts only system locks. So multiple writers will need to be either blocking on system level or implement some other form of locking to ensure integrity. A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance. I wonder if one day SQLite would support synchronization/replication protocol. In a way Fossil SCM is an attempt at SQLite replication, albeit a specialized one.
  - sicp-enjoyer 4y ago
    > A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance. Agreed. > My understanding is that SQLite suppprts only system locks Is this any different than a process blocking on a mutex while another writes to a shared resource? I understand DBMS do it better, but I don't see why it's viewed as a non-starter for SQLite.

sicp-enjoyer 4y ago

If you need to safely have multiple processes read and write to the same data, it does that great. The writes are serialized, but that's typically how an in-memory shared resource would be implemented as well. Do you mean shared across networks?

chasil 4y ago
I have seen SQLite disconnect random sessions of multiple writers, rather than blocking them. Setting WAL mode made no difference. This database can safely be used by a single writer at all times. To implement the serialization that SQLite does not, do this in the POSIX shell: until mkdir ~/.dpapplock 2> /dev/null do sleep 3 done trap "rmdir ~/.dbapplock" EXIT sqlite3_app ... The mkdir() system call is defined as atomic by POSIX, so the shell can safely serialize for you, assuming there are never more than a handful.
zoomablemind 4y ago
My understanding is that SQLite suppprts only system locks. So multiple writers will need to be either blocking on system level or implement some other form of locking to ensure integrity. A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance. I wonder if one day SQLite would support synchronization/replication protocol. In a way Fossil SCM is an attempt at SQLite replication, albeit a specialized one.
- sicp-enjoyer 4y ago
  > A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance. Agreed. > My understanding is that SQLite suppprts only system locks Is this any different than a process blocking on a mutex while another writes to a shared resource? I understand DBMS do it better, but I don't see why it's viewed as a non-starter for SQLite.