Ensure that SQLite connections are used correctly to preserve isolation #84

New issue

Open

opened 2020-08-06 16:54:38 +00:00 by gburd · 0 comments

gburd commented

2020-08-06 16:54:38 +00:00

(Migrated from github.com)

Per discussion with @rnewman, the current transactor -- even post #80 -- doesn't handle concurrent reads correctly in JavaScript.

In JavaScript: since there's only a single thread, all reads will race to read and write, potentially witnessing in-progress transactions. What we really want is (at least) one read DB connection(s) and exactly one write DB connection. The write connection serves to isolate the read connection(s) from in-progress transactions, while the read(s) connection service queries.

In Java: we're a bit concerned that JDBC and the SQLite JDBC driver may be multiplexing DB connections among threads in hard to predict ways. Again, we'd like to address this as above (with read DB connections and a distinguished write DB connection), but in addition we'd like to pin te write DB connection to a distinguished thread, to avoid any DB connection multiplexing.

Per discussion with @rnewman, the current transactor -- even post #80 -- doesn't handle concurrent reads correctly in JavaScript. In JavaScript: since there's only a single thread, all reads will race to read and write, potentially witnessing in-progress transactions. What we really want is (at least) one read DB connection(s) and exactly one write DB connection. The write connection serves to isolate the read connection(s) from in-progress transactions, while the read(s) connection service queries. In Java: we're a bit concerned that JDBC and the SQLite JDBC driver may be multiplexing DB connections among threads in hard to predict ways. Again, we'd like to address this as above (with read DB connections and a distinguished write DB connection), but in addition we'd like to pin te write DB connection to a distinguished thread, to avoid any DB connection multiplexing.