How does one block a reader connection?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Simon Slavin-3

On 25 Mar 2017, at 10:52pm, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> wrote:

>
> On 25 Mar 2017, at 10:52pm, petern <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Richard Damon
In reply to this post by petern
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Simon Slavin-3
In reply to this post by petern

On 26 Mar 2017, at 2:35am, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Keith Medcalf
In reply to this post by petern
On Saturday, 25 March, 2017 19:35, petern <[hidden email]> 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 <[hidden email]>
> wrote:
>
> >
> > On 25 Mar 2017, at 10:52pm, petern <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

petern
In reply to this post by Simon Slavin-3
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 <[hidden email]> wrote:

>
> On 26 Mar 2017, at 2:35am, petern <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Jay Kreibich
In reply to this post by petern

On Mar 25, 2017, at 5:52 PM, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Keith Medcalf
In reply to this post by petern
Saturday, 25 March, 2017 23:44. petern <[hidden email]> 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
> 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 <[hidden email]>
> wrote:
>
> >
> > On 26 Mar 2017, at 2:35am, petern <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Jens Alfke-2
In reply to this post by petern

> On Mar 25, 2017, at 3:52 PM, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

petern
In reply to this post by Keith Medcalf
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 <[hidden email]> wrote:

> Saturday, 25 March, 2017 23:44. petern <[hidden email]>
> 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
> > 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 <[hidden email]>
> > wrote:
> >
> > >
> > > On 26 Mar 2017, at 2:35am, petern <[hidden email]> 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
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Rowan Worth-2
In reply to this post by Keith Medcalf
On 26 March 2017 at 14:17, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Hick Gunter
In reply to this post by petern
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' [,<timeout>]); -- 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:[hidden email]] Im Auftrag von petern
Gesendet: Samstag, 25. März 2017 23:52
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Hick Gunter
In reply to this post by Richard Damon
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:[hidden email]] Im Auftrag von Richard Damon
Gesendet: Sonntag, 26. März 2017 03:43
An: [hidden email]
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How does one block a reader connection?

Jens Alfke-2
In reply to this post by Hick Gunter

> On Mar 26, 2017, at 11:37 PM, Hick Gunter <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...