Thanks Simon. Yes, the actual command table has other columns like a time stamp to allow readers to join and restart asynchronously without losing state. The concept of 'done' in this system is also not so clear to the readers because command execution depends on the state of independent systems fed by the readers which all report back to the writer. So ultimately, it's the writer that takes care of knowing what's 'done' and retrying if an impaired command's goal is still worthwhile.
Can anybody explain the purpose of http://sqlite.org/c3ref/busy_handler.html ? It seems the only practical use would be to allow the caller to give the engine a suggested lock deadline before SQLITE_BUSY is returned by sqlite3_exec or sqlite3_step Then, if the calling thread would prefer to wait longer, it can just retry with progressively smaller lock deadline until it's time to do something else. In my application it is more ideal if commands in the command table are known to the readers immediately but without the overhead of rapid polling by the readers. Setting a suggested wait time and then waiting again if SQLITE_BUSY comes back early and there's nothing else to do would be just fine. [Regarding the suggestion of synchronizing the readers with another concurrency object, I could do that. But if the database connection itself can coordinate the same thing, the reader program is a lot simpler and more portable.] I'm not sure I understand Simon's comments how using EXCLUSIVE TRANSACTION writer isolation is subverting something subject to change if that isolation level is desired. Reader would still be querying the command table but in committed read mode with EXCLUSIVE isolation. Why would this work differently on different systems? From http://www.sqlite.org/lockingv3.html "EXCLUSIVE An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held." "If the same database is being read and written using two different database connections <http://sqlite.org/c3ref/sqlite3.html> (two different sqlite3 <http://sqlite.org/c3ref/sqlite3.html> objects returned by separate calls to sqlite3_open() <http://sqlite.org/c3ref/open.html>) and the two database connections do not have a shared cache <http://sqlite.org/sharedcache.html>, then the reader is only able to see complete committed transactions from the writer." "The previous paragraph is also true (separate database connections are isolated from one another) in shared cache mode <http://sqlite.org/sharedcache.html> as long as the read_uncommitted pragma <http://sqlite.org/pragma.html#pragma_read_uncommitted> remains turned off. The read_uncommitted pragma <http://sqlite.org/pragma.html#pragma_read_uncommitted> is off by default" On Sat, Mar 25, 2017 at 7:56 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
