Re: [sqlite] How does one block a reader connection?

[Jens Alfke]

> On Mar 26, 2017, at 11:37 PM, Hick Gunter  wrote:
> 
> I think this kind of problem (transfer of information between cooperating 
> processes) is best solved using the tools designed for inter-process 
> communication and not attempting to abuse a DB system designed to isolate 
> processes from unfinished changes.

Generally I agree … but to play devil’s advocate: SQLite already has a degree 
of interprocess communication using shared memory (the “-shm” file). It might 
be simple to extend this to provide a very lightweight change counter, by 
atomically incrementing an integer field in this shared memory. A process could 
then poll this with almost no overhead.

(I’m sure the answer is “implement it and send a patch”, which is as it should 
be. I don’t have any experience with the SQLite source base, though, so it 
would probably take me longer to get up to speed on that, than it would for me 
to reach for an IPC tool I already know how to use, like CFNotificationCenter. 
Oh well.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[Hick Gunter]

Hey, neat idea! To expand on my previous post:

CREATE TRIGGER wakeup AFTER INSERT ON cmd BEGIN SELECT 
cond_broadcast('cmd_ready'); END;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Damon
Gesendet: Sonntag, 26. März 2017 03:43
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] How does one block a reader connection?

On 3/25/17 6:52 PM, petern wrote:
> I would like to construct a SQLite database for one writer and one or
> more reader connections.  The writer will be updating various data
> tables which occasionally trigger one or more row inserts in a command
> table named 'cmd'.  This command table is being polled by the readers for new 
> commands.
>
> To make the example more concrete, suppose the following DDL defines
> the command table:
>
> 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;
>
> where $lastCmdRowid is a variable managed by the reader to keep track
> of the last command that was seen from its point of view.  [FYI yes,
> of course there are other time stamp bookkeeping columns and variables
> that I've omitted because they don't pertain this question.]
>
> So finally, here is the question.  Is there a SQLite API way for
> reader connections to block and wait for a meaningful change, like a
> new row, in the 'cmd' table instead of madly polling and using up
> database concurrency resources?  [Block with timeout would be even
> more desirable of course.]
I don't know SQLite well enough to say for certain, but to my knowledge 
'blocking till something happens' isn't the sort of thing the SQL vocabulary 
has. (There is the concept of a trigger, so you might be able to get something 
to run when the insert happens but that is different than blocking to something 
happens).

My best guess is that you want to use a real synchronization primitive outside 
of SQLite sent by the writer, (or maybe you can put it into a
trigger) to hold off the reader, and then have it read the work that was queued 
up.


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[Hick Gunter]

I think this kind of problem (transfer of information between cooperating 
processes) is best solved using the tools designed for inter-process 
communication and not attempting to abuse a DB system designed to isolate 
processes from unfinished changes.

Have the processes share a condition variable. The readers block on the 
condition variable (with a timeout if desired, to guard against dropped 
signals); the writer broadcasts the variable, waking up the readers which then 
proceed to retrieve/process any new entries.

If you are really desperate, you could write use defined functions/virtual 
table to do this over the SQL interface, e.g.

For creating a virtual table (allows keeping/querying housekeeping info about 
condition variables)

CREATE VIRTUAL TABLE conditions USING posix_cond; -- allocate/connect to the 
shared memory of your choice

INSERT INTO conditions (name) value ('cmd_ready'); -- create a condition 
variable

Or just for creating with minimal housekeeping:

SELECT cond_init('cmd_readY');

And in both cases:

SELECT cond_wait('cmd_ready' [,]); -- wait for condition [or timeout], 
returns 1 for signal received, 0 for error/timeout

SELECT cond_broadcast('cmd_ready'); -- wake up readers

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von petern
Gesendet: Samstag, 25. März 2017 23:52
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] How does one block a reader connection?

I would like to construct a SQLite database for one writer and one or more 
reader connections.  The writer will be updating various data tables which 
occasionally trigger one or more row inserts in a command table named 'cmd'.  
This command table is being polled by the readers for new commands.

To make the example more concrete, suppose the following DDL defines the 
command table:

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;

where $lastCmdRowid is a variable managed by the reader to keep track of the 
last command that was seen from its point of view.  [FYI yes, of course there 
are other time stamp bookkeeping columns and variables that I've omitted 
because they don't pertain this question.]

So finally, here is the question.  Is there a SQLite API way for reader 
connections to block and wait for a meaningful change, like a new row, in the 
'cmd' table instead of madly polling and using up database concurrency 
resources?  [Block with timeout would be even more desirable of course.] 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[Rowan Worth]

On 26 March 2017 at 14:17, Keith Medcalf  wrote:

> If you do not specify your own custom busy handler (to display flying ball
> bearings, etc, or do your own exponential sleeping, etc) then the default
> busy_handler is used.  The default busy handler does its own exponential
> backoff algorithm but does not display whirling ball bearings or anything
> else, it just sleeps silently.


Nitpick: the "default" busy_handler is in fact not used by default. That
is, if you open a connection and don't specify a busy handler or timeout
then you will get SQLITE_BUSY returned immediately if a lock cannot be
acquired. There are essentially three busy handler states:

(1) Normal settings: no busy handler is active
(2) sqlite3_busy_timeout()/PRAGMA timeout invoked: a sleep() with
exponential backoff busy handler is used
(3) sqlite3_busy_handler() invoked: the user defined busy handler is used

Sqlite implements the timeout feature by installing a busy_handler behind
the scenes (which is called the "default" busy handler in the code).
There's only one busy_handler per connection though, so whichever is called
last out of sqlite3_busy_handler() or sqlite3_busy_timeout() will take
precedence.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[petern]

Keith, I understand your point.  The timescale of polling is between 1 and
10 seconds by sleep loop depending on operational objectives.  This range
of sleep loop will have a corresponding latency of between 0.5 and 5
seconds for single commands with a uniform arrival time distribution.

The idea was to get near the 0.5 second latency of the 1 second sleep loop
while actually polling in a very gentle 10 second sleep loop.

Thanks for the colorful explanation of custom busy handlers.



On Sat, Mar 25, 2017 at 11:17 PM, Keith Medcalf  wrote:

> Saturday, 25 March, 2017 23:44. petern 
> wrote:
>
> > 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.
>
> The busy handler is used if you have set a timeout to obtain a lock.  If
> you try to do
>
> BEGIN IMMEDIATE
>
> on a database that is not in WAL mode, then in order to obtain a lock
> there must be no active readers or writers.  If there are, the lock cannot
> be obtained an SQLITE_BUSY is returned and you try again later.  If you set
> a timeout with PRAMGA timeout, that specifies the amount of time to wait
> for a lock before returning SQLITE_BUSY.  The busy handler is used to
> "handle" the "busy" state (that is, rather than return SQLITE_BUSY to the
> caller, your busy handler is called.  You do something (like wait a while
> using sleep, then return, perhaps displaying flying ball bearings or other
> annoyances to indicate to the user that the program is waiting).  Lather
> rinse repeat until the specified timeout expires at which time SQLITE_BUSY
> is returned to the caller.
>
> If you do not specify your own custom busy handler (to display flying ball
> bearings, etc, or do your own exponential sleeping, etc) then the default
> busy_handler is used.  The default busy handler does its own exponential
> backoff algorithm but does not display whirling ball bearings or anything
> else, it just sleeps silently.
>
> > 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.
>
> Again, you have specified "RAPID POLLING".  Can you please define what you
> mean by "RAPID POLLING"?  To me, "RAPID POLLING" means using the immensely
> stupid spinlock type implementation such as:
>
> open connection
> while (nothing to do)
>query database for something to do
> do what needs doing
>
> If you need things to go "RAPIDLY" then you need to use some IPC
> notification mechanism.
>
> Please state in explicit terms your definition of "RAPIDLY".   Using terms
> without defining them makes it difficult for anyone to understand what you
> are talking about.  One persons "RAPIDLY" may mean "after the MoC is filled
> out and endorsed, operations windows are arranged, and the board operator
> gives the final go ahead -- in other words, two weeks to six months".
> Another person's definition of "RAPIDLY" may be "within 250 milliseconds".
> Please specify what you definition of "RAPIDLY" is, giving minimum and
> maximum bounds in common time units.
>
> If RAPIDLY means "in less than one second, 33% of the time, within two
> seconds 75% of the time, and no more than three seconds ever" then you are
> talking about rather SLOW POLLING.
>
> open connection
> while (nothing to do)
>sleep 1 second
>query database for something to do
> do what needs doing
>
> This type of SLOW POLLING will use about 1% of the available CPU on a 4.77
> Mhz 8086 CPU while in the polling loop.  This means that you can have about
> 100 such processes polling at the same time on a 4.77 Mhz 8086 equipped
> computer.  On a modern CPU with a couple of cores and about 2Ghz per code,
> you should be able to run many tens of thousands of such processes
> simultaneously and STILL have resources left over.  (though the OS
> scheduler will probably consume more CPU than the polling loops do, and the
> process table and OS resources are likely to become exhausted long before
> you hit any reasonable limit on the resource limits for the simultaneously
> polling processes themselves.)
>
> > [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
> > 

Re: [sqlite] How does one block a reader connection?

[Jens Alfke]

> On Mar 25, 2017, at 3:52 PM, petern  wrote:
> 
> So finally, here is the question.  Is there a SQLite API way for reader
> connections to block and wait for a meaningful change, like a new row, in
> the 'cmd' table instead of madly polling and using up database concurrency
> resources?  [Block with timeout would be even more desirable of course.]

No; I’ve asked about this myself.

Alternatives to polling are either (a) using a platform-specific filesystem 
notification API to inform you when the database file(s) have been changed; or 
(b) using a platform-specific cross-process notification API to let the writer 
process signal that there are new changes. If you only want to know about 
specific types of changes, then option (b) sounds best.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[Keith Medcalf]

Saturday, 25 March, 2017 23:44. petern  wrote:

> 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.

The busy handler is used if you have set a timeout to obtain a lock.  If you 
try to do

BEGIN IMMEDIATE

on a database that is not in WAL mode, then in order to obtain a lock there 
must be no active readers or writers.  If there are, the lock cannot be 
obtained an SQLITE_BUSY is returned and you try again later.  If you set a 
timeout with PRAMGA timeout, that specifies the amount of time to wait for a 
lock before returning SQLITE_BUSY.  The busy handler is used to "handle" the 
"busy" state (that is, rather than return SQLITE_BUSY to the caller, your busy 
handler is called.  You do something (like wait a while using sleep, then 
return, perhaps displaying flying ball bearings or other annoyances to indicate 
to the user that the program is waiting).  Lather rinse repeat until the 
specified timeout expires at which time SQLITE_BUSY is returned to the caller.

If you do not specify your own custom busy handler (to display flying ball 
bearings, etc, or do your own exponential sleeping, etc) then the default 
busy_handler is used.  The default busy handler does its own exponential 
backoff algorithm but does not display whirling ball bearings or anything else, 
it just sleeps silently.
 
> 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.

Again, you have specified "RAPID POLLING".  Can you please define what you mean 
by "RAPID POLLING"?  To me, "RAPID POLLING" means using the immensely stupid 
spinlock type implementation such as:

open connection
while (nothing to do)
   query database for something to do
do what needs doing

If you need things to go "RAPIDLY" then you need to use some IPC notification 
mechanism.

Please state in explicit terms your definition of "RAPIDLY".   Using terms 
without defining them makes it difficult for anyone to understand what you are 
talking about.  One persons "RAPIDLY" may mean "after the MoC is filled out and 
endorsed, operations windows are arranged, and the board operator gives the 
final go ahead -- in other words, two weeks to six months".  Another person's 
definition of "RAPIDLY" may be "within 250 milliseconds".  Please specify what 
you definition of "RAPIDLY" is, giving minimum and maximum bounds in common 
time units.

If RAPIDLY means "in less than one second, 33% of the time, within two seconds 
75% of the time, and no more than three seconds ever" then you are talking 
about rather SLOW POLLING. 

open connection
while (nothing to do)
   sleep 1 second
   query database for something to do
do what needs doing

This type of SLOW POLLING will use about 1% of the available CPU on a 4.77 Mhz 
8086 CPU while in the polling loop.  This means that you can have about 100 
such processes polling at the same time on a 4.77 Mhz 8086 equipped computer.  
On a modern CPU with a couple of cores and about 2Ghz per code, you should be 
able to run many tens of thousands of such processes simultaneously and STILL 
have resources left over.  (though the OS scheduler will probably consume more 
CPU than the polling loops do, and the process table and OS resources are 
likely to become exhausted long before you hit any reasonable limit on the 
resource limits for the simultaneously polling processes themselves.)

> [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
> 

Re: [sqlite] How does one block a reader connection?

[Jay Kreibich]

On Mar 25, 2017, at 5:52 PM, petern  wrote:

> So finally, here is the question.  Is there a SQLite API way for reader
> connections to block and wait for a meaningful change, like a new row, in
> the 'cmd' table instead of madly polling and using up database concurrency
> resources?  [Block with timeout would be even more desirable of course.]


No, there is not.  As others have pointed out, this is not a very “databasey” 
thing.  The whole point of relational, ACID transaction databases is that they 
provide an atomic “snapshot in time” view of a data set.  They do everything 
they can hide changes within a transaction, and there is no way to issue a 
SELECT outside of a transaction.

It sounds like you really need a message queue of some type, more of a pub/sub 
infrastructure.  There are dozens of products out there that do this.  That 
said, if all the processes are on a single system (as they would have to be to 
be using SQLite to communicate) it might make a lot more sense to use a simple 
text file.  One writer (or more) can append lines, and multiple processes can 
read from the end, using blocking I/O on the file, not unlike a “tail -f” in 
the UNIX world.  If you keep track of which line number you’re on, you can 
retry or re-pickup if a reader process needs to restart, plus the file provides 
a log of all messages.  Simple, easy, and straight forward.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[petern]

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  (two different sqlite3
 objects returned by separate calls
to sqlite3_open() ) and the two database
connections do not have a shared cache ,
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
 as long as the read_uncommitted pragma
 remains turned off.
The read_uncommitted pragma
 is off by default"



On Sat, Mar 25, 2017 at 7:56 PM, Simon Slavin  wrote:

>
> On 26 Mar 2017, at 2:35am, petern  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
>
> 
>
> 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 

Re: [sqlite] How does one block a reader connection?

[Keith Medcalf]

On Saturday, 25 March, 2017 19:35, petern  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 
> wrote:
> 
> >
> > On 25 Mar 2017, at 10:52pm, petern  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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[Simon Slavin]

On 26 Mar 2017, at 2:35am, petern  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



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

Re: [sqlite] How does one block a reader connection?

[Richard Damon]

On 3/25/17 6:52 PM, petern wrote:

I would like to construct a SQLite database for one writer and one or more
reader connections.  The writer will be updating various data tables which
occasionally trigger one or more row inserts in a command table named
'cmd'.  This command table is being polled by the readers for new commands.

To make the example more concrete, suppose the following DDL defines the
command table:

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;

where $lastCmdRowid is a variable managed by the reader to keep track of
the last command that was seen from its point of view.  [FYI yes, of course
there are other time stamp bookkeeping columns and variables that I've
omitted because they don't pertain this question.]

So finally, here is the question.  Is there a SQLite API way for reader
connections to block and wait for a meaningful change, like a new row, in
the 'cmd' table instead of madly polling and using up database concurrency
resources?  [Block with timeout would be even more desirable of course.]
I don't know SQLite well enough to say for certain, but to my knowledge 
'blocking till something happens' isn't the sort of thing the SQL 
vocabulary has. (There is the concept of a trigger, so you might be able 
to get something to run when the insert happens but that is different 
than blocking to something happens).


My best guess is that you want to use a real synchronization primitive 
outside of SQLite sent by the writer, (or maybe you can put it into a 
trigger) to hold off the reader, and then have it read the work that was 
queued up.



--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does one block a reader connection?

[petern]

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  wrote:

>
> On 25 Mar 2017, at 10:52pm, petern  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

Re: [sqlite] How does one block a reader connection?

[Simon Slavin]

On 25 Mar 2017, at 10:52pm, petern  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] How does one block a reader connection?

[petern]

I would like to construct a SQLite database for one writer and one or more
reader connections.  The writer will be updating various data tables which
occasionally trigger one or more row inserts in a command table named
'cmd'.  This command table is being polled by the readers for new commands.

To make the example more concrete, suppose the following DDL defines the
command table:

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;

where $lastCmdRowid is a variable managed by the reader to keep track of
the last command that was seen from its point of view.  [FYI yes, of course
there are other time stamp bookkeeping columns and variables that I've
omitted because they don't pertain this question.]

So finally, here is the question.  Is there a SQLite API way for reader
connections to block and wait for a meaningful change, like a new row, in
the 'cmd' table instead of madly polling and using up database concurrency
resources?  [Block with timeout would be even more desirable of course.]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users