All good points. Yes, query by 'rowid > $lastCmdRowid' was the intent. 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.
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. 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. On Sat, Mar 25, 2017 at 4:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 25 Mar 2017, at 10:52pm, petern <peter.nichvolo...@gmail.com> wrote: > > > CREATE TABLE cmd(opcode TEXT, params TEXT); > > > > Also assume each reader is in a different process which maintains its own > > open db connection over which it periodically executes the following > > command retrieval query, > > > > SELECT * FROM cmd WHERE rowid>=$lastCmdRowid; > > I presume you mean '>' not '>='. > > Not answering your question, but making some recommendations. If the > order of commands matters, then you might use this instead: > > SELECT * FROM cmd WHERE rowid > $lastCmdRowid ORDER BY rowid; > > Since you have a polling loop, you might want to make that as efficient as > possible. Possibly the quickest way to do it would be > > SELECT max(rowid) FROM cmd; > > then do the comparison in your code. If and only if the new value is > bigger you do the other SELECT. > > Since you intend to make use of the rowid column, it’s best to make that > explicit in your code. I know SQLite understands the reference anyway, but > you’re also explaining things to anyone reading your code. > > CREATE TABLE cmd(id INTEGER PRIMARY KEY, opcode TEXT, params TEXT); > > Given your requirements I recommend that you put this database into WAL > mode. > > 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
