On 26 Mar 2017, at 2:35am, petern <peter.nichvolo...@gmail.com> wrote:
> Is there something that can be done by the writer, like holding a BEGIN > EXCLUSIVE TRANSACTION open with PRAGMA read_uncommitted=0? Would that > block all readers or would they continue to get empty results from the > command table? I suppose I could start experimenting with this but it > would be faster to get some hints from experts on his forum. The problem is that you can’t monitor the connection to the database which does the writing. The connection you’re using to do the reading doesn’t know anything special about what another connection is doing. I can think of ways to make SQLite be slightly more helpful than vanilla SQL would be. For instance, you could use a separate file for your "cmd" table and use <https://sqlite.org/pragma.html#pragma_data_version> PRAGMA data_version; to see whether any modifications at all had been done. But when you figure out what you’ll actually be doing in your program, you’re still executing a command and analyzing the result. And it has the disadvantage that it’s terribly non-standard and someone reading your code won’t understand it. I suspect that you’re better off just sticking to standard calls. > If a writer TRANSACTION might work, I'd be interested to know the minimum > transaction and isolation settings that would cause readers to block in an > example of reader using sqlite3_busy_handler with the sqlite3_exec call in > a single threaded program to achieve PRAGMA busy_timeout=N. I recommend that you don’t try to mess with the mutex at this level. Detailed behaviour of the mutex can vary a lot depending on precise versions of OS, FS, storage, etc.. You could develop something which worked very well on your programming computer, then find it completely misses updates on another computer, or even when you just update your OS or replace your hard disk. Your method of storing commands in a table and using conventional SQL to read them should work fine. > Comments on the benefits of WAL mode for this pattern would also be helpful > considering there is one writer that needs speedy access to potentially > large number of of changes before the writer transaction finally inserts > some new commands and closes the transaction for the readers to see the > updated command table. Don’t try to subvert transactions to act as a mutex. You might end up depending on something which stops working in a later version of SQLite. Try both WAL and original modes. See which one gives you acceptable behaviour. Pick that one. Another way to do it would be to add a "done" column to your "cmd" table containing either 0 or 1. When the command is added to the table this defaults to 0. Your loop reads all rows which have this set to zero. You then execute those commands and use an UPDATE to set the "done" column to 1 for those rows. This means you don’t care about rowid, and are better-protected against crashes. But I think the method you described originally may be better than this. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
