4 ms·

Just wondering, do you have a specific use case for read transactions implemented on the database level here? In SQLite in general read transactions are useful

Just wondering, do you have a specific use case for read transactions implemented on the database level here? In SQLite in general read transactions are useful since you can access the same database from multiple processes at a time. Here, only a single process can access the database. So you can get the same effect as read transactions either by doing all reads in one synchronous function, or implement your own process-level locking.

emadda 2y ago
E.g. if you have many websocket connections and they each have a snapshot at a point in time (that spans over many different await function calls/ws messages). SQLite can have many readers and a single writer with WAL, so a many read transactions can exist whilst the writers move the db state forward.
- kentonv 2y ago
  We (Cloudflare) have considered adding an API to create multiple "database connections", especially to be able to stream a response from a long-running cursor while representing a consistent snapshot of the data. It's a bit tricky since if you hold open that old connection, the WAL could grow without bound and cannot be checkpointed back into the main database. What do we do when the WAL gets unreasonably large (e.g. bigger than the database)? Cancel old cursors so we can finally checkpoint? Will that be annoying for app developers to deal with, e.g. causing errors when traffic is high? SQLite itself calls an open database a "connection" even though there's no actual network involved.
  - kentonv 2y ago
    Ugh didn't notice until too late to edit, but apparently HN interpreted my asterisk as an instruction to italicize everything between it and the footnote it referred to.
  - emadda 2y ago
    I did guess it might be harder to do than vanilla SQLite, as vanilla SQLite just has the WAL and main db on the same hard drive, so it has more space to grow the WAL and it is not an issue when the machine/instance reboots (as it just starts where it left off, even if the WAL is large and has not been check-pointed back to the main db). To be honest this is an edge case. But I often start a read transaction on a SQLite connection just so I know multiple queries are reading from the same state (and to ensure state has not been changed between queries).

emadda 2y ago

E.g. if you have many websocket connections and they each have a snapshot at a point in time (that spans over many different await function calls/ws messages). SQLite can have many readers and a single writer with WAL, so a many read transactions can exist whilst the writers move the db state forward.

kentonv 2y ago
We (Cloudflare) have considered adding an API to create multiple "database connections", especially to be able to stream a response from a long-running cursor while representing a consistent snapshot of the data. It's a bit tricky since if you hold open that old connection, the WAL could grow without bound and cannot be checkpointed back into the main database. What do we do when the WAL gets unreasonably large (e.g. bigger than the database)? Cancel old cursors so we can finally checkpoint? Will that be annoying for app developers to deal with, e.g. causing errors when traffic is high? SQLite itself calls an open database a "connection" even though there's no actual network involved.
- kentonv 2y ago
  Ugh didn't notice until too late to edit, but apparently HN interpreted my asterisk as an instruction to italicize everything between it and the footnote it referred to.
- emadda 2y ago
  I did guess it might be harder to do than vanilla SQLite, as vanilla SQLite just has the WAL and main db on the same hard drive, so it has more space to grow the WAL and it is not an issue when the machine/instance reboots (as it just starts where it left off, even if the WAL is large and has not been check-pointed back to the main db). To be honest this is an edge case. But I often start a read transaction on a SQLite connection just so I know multiple queries are reading from the same state (and to ensure state has not been changed between queries).