On Saturday, 25 March, 2017 19:35, petern <[email protected]> wrote:
> 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.
I do not understand why you would want to do this. Use WAL mode so that you
have concurrent access to the database. I presume all the processes are
running on one machine?
> 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 think you are doing what is called "Premature Optimization". How fast do you
need to respond to a command being added to the table? Why can you not sleep
for a second in your polling loops?
open connection
while True:
sleep(1.00)
query command table
if nothing to do
continue
... process the updates ...
If you need to have picosecond scale response, then SQLite is not your
solution. If you need that sort of thing then you would have to dispatch
notifications via an IPC mechanism (such as a named event). If you are going
to implement an IPC mechanism then you may as well just send the command
directly.
> 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.
This is what WAL was designed for. You perform all you updates in a single
transaction, and when you commit the changes, all the other readers will see
all the changes the next time they look.
> On Sat, Mar 25, 2017 at 4:16 PM, Simon Slavin <[email protected]>
> wrote:
>
> >
> > On 25 Mar 2017, at 10:52pm, petern <[email protected]> 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
> > [email protected]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
