> Le 30 juil. 2019 à 22:39, test user <example.com.use...@gmail.com> a écrit : > > What I would like is something like BEGIN READ, which will not block > writers for its duration. > > This "read transaction" can see all committed transactions that happened > before it, but none after it. > > At the moment it seems to get this guarantee I will need to do a "BEGIN; > SELECT * from sqlite_master LIMIT 1"
I would drop the "SELECT * from sqlite_master LIMIT 1" or whatever other dummy read. As long as your explicit transaction, started with "BEGIN" which is a shortcut for "BEGIN DEFERRED", does not actually start reading anything, what importance does it have for you that it already holds an "end-mark"? (See link below for reference to the "end mark" concept). As soon as it reads anything, it will see the database from that point on, until ROLLBACK or COMMIT, as it was at point of that single first read. https://www.sqlite.org/wal.html#concurrency > Le 30 juil. 2019 à 22:42, Simon Slavin <slav...@bigfraud.org> a écrit : > >> BEGIN IMMEDIATE will start a write transaction, which will block other >> writers with SQLITE_BUSY until its complete. > > This does not apply to WAL mode. You wrote that you were using WAL mode. Yes it applies to WAL mode. BEGIN IMMEDIATE will anyway get a RESERVED lock immediately and will block other writers from that point in time. Though in WAL mode, it won't block a new reader to start reading and see the database content as it existed before the BEGIN IMMEDIATE transaction was started. That view will still be stable after the writer COMMIT. The catch here is that a BEGIN (DEFERRED) does not restrict to read operations. Such a transaction might at some point attempt to write to the database. If it does, it will fail if: - another writer is still busy - another writer has already COMMITed changes The easiest way to program using WAL mode is probably to BEGIN when you want a read transaction and drill your code not to attempt any write while inside such a virtual read transaction. And always do a BEGIN IMMEDIATE upfront when you want to start a write transaction. If your write transactions are short-lived, you will get excellent concurrency (readers always free to start or continue running) with minimal wait time in the busy handler when executing BEGIN IMMEDIATE. Even PASSIVE checkpointing will run free (albeit not as completely) with readers ongoing. — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
