2 ms·

mvsqlite doesn't rely on the SQLite WAL or rollback journal for safety. Actually, it enforces journal_mode=memory! The atomic commit semantics is guaranteed by

mvsqlite doesn't rely on the SQLite WAL or rollback journal for safety. Actually, it enforces journal_mode=memory! The atomic commit semantics is guaranteed by FDB instead. The limit on max number of open DBs looks low, but maybe short-lived attaches can be done? Like attach just before the transaction and detach after commit. This should be enough for common transactions involving just a few DBs.

liuliu 4y ago
I am a bit out of depth here for obvious reasons (I work above SQLite, not under SQLite). But from my understanding, `journal_mode=memory` simply means it is in rollback mode, but rollback pages are not maintained on disk. Therefore, the modifications from a transaction will be applied in place in these pages. If the process that runs SQLite crashes in the middle of a transaction, these details will be leaked unless you know how to rollback these pages (through rollback journal, you can alternatively found these from FDB, but you should have no idea which one corresponding to which transaction?). More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well, practically becomes not only single-writer, but also single-reader (you probably can break the global lock (that through VFS layer)? But it doesn't make this thing safe (i.e. you will have things in a transaction leaked?)).
- losfair 4y ago
  > Therefore, the modifications from a transaction will be applied in place in these pages. With mvsqlite it is applied to the transaction's own snapshot of the database. Changes are not visible globally until transaction commit. That's what we get from page-level MVCC. > More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well Reads are also MVCC. Data is fetched from a consistent and clean snapshot of the database, without uncommitted data.
  - rockwotj 4y ago
    This approach provides SERIALIZABLE transaction isolation right? What happen if there are page conflicts during the write? The transaction fails and you retry? Does SQLite have a good way of of signalling that this is the case (vs for example a network failure or other write failures).
    - losfair 4y ago
      Yes it's the serializable isolation level. For applications targeting upstream SQLite, mvsqlite enables pessimistic locking by default - when a transaction is promoted to EXCLUSIVE, it acquires a one-minute lock lease from mvstore. At this point we have the chance to fail gracefully and return a "database is locked" error if multiple clients want to acquire lock on the same DB. This is a best-effort mechanism to prevent conflict on commit (which causes the process to abort). A future feature is "MVCC-aware clients". Compatible clients can opt-in to full, optimistic MVCC, and avoid pessimistic locking. After `COMMIT`, the client should call a SQLite custom function provided by mvsqlite to check whether the commit actually succeeds, and retry if not.
  - Witness13 4y ago
  - liuliu 4y ago
    Read a bit of your code. Seems most magic happens around xLock / xUnlock implementation (which were used to identify a txn). I wonder if SQLite should expose one layer up (on the actual pager level). A lot of attempts (such as yours, or LiteFS) try to construct the pager / txn concept from VFS layer as well, which seems to be one layer lower than actually is.

liuliu 4y ago

I am a bit out of depth here for obvious reasons (I work above SQLite, not under SQLite). But from my understanding, `journal_mode=memory` simply means it is in rollback mode, but rollback pages are not maintained on disk. Therefore, the modifications from a transaction will be applied in place in these pages. If the process that runs SQLite crashes in the middle of a transaction, these details will be leaked unless you know how to rollback these pages (through rollback journal, you can alternatively found these from FDB, but you should have no idea which one corresponding to which transaction?). More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well, practically becomes not only single-writer, but also single-reader (you probably can break the global lock (that through VFS layer)? But it doesn't make this thing safe (i.e. you will have things in a transaction leaked?)).

losfair 4y ago
> Therefore, the modifications from a transaction will be applied in place in these pages. With mvsqlite it is applied to the transaction's own snapshot of the database. Changes are not visible globally until transaction commit. That's what we get from page-level MVCC. > More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well Reads are also MVCC. Data is fetched from a consistent and clean snapshot of the database, without uncommitted data.
- rockwotj 4y ago
  This approach provides SERIALIZABLE transaction isolation right? What happen if there are page conflicts during the write? The transaction fails and you retry? Does SQLite have a good way of of signalling that this is the case (vs for example a network failure or other write failures).
  - losfair 4y ago
    Yes it's the serializable isolation level. For applications targeting upstream SQLite, mvsqlite enables pessimistic locking by default - when a transaction is promoted to EXCLUSIVE, it acquires a one-minute lock lease from mvstore. At this point we have the chance to fail gracefully and return a "database is locked" error if multiple clients want to acquire lock on the same DB. This is a best-effort mechanism to prevent conflict on commit (which causes the process to abort). A future feature is "MVCC-aware clients". Compatible clients can opt-in to full, optimistic MVCC, and avoid pessimistic locking. After `COMMIT`, the client should call a SQLite custom function provided by mvsqlite to check whether the commit actually succeeds, and retry if not.
- Witness13 4y ago
- liuliu 4y ago
  Read a bit of your code. Seems most magic happens around xLock / xUnlock implementation (which were used to identify a txn). I wonder if SQLite should expose one layer up (on the actual pager level). A lot of attempts (such as yours, or LiteFS) try to construct the pager / txn concept from VFS layer as well, which seems to be one layer lower than actually is.