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 <kmedc...@dessus.com> wrote: > Saturday, 25 March, 2017 23:44. petern <peter.nichvolo...@gmail.com> > 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 <slav...@bigfraud.org> > > wrote: > > > > > > > > On 26 Mar 2017, at 2:35am, petern <peter.nichvolo...@gmail.com> wrote: > > > > > > > Is there something that can be done by the writer, like holding a > > BEGIN > > > > EXCLUSIVE TRANSACTION open with PRAGMA read_uncommitted=0? Would > that > > > > block all readers or would they continue to get empty results from > the > > > > command table? I suppose I could start experimenting with this but > it > > > > would be faster to get some hints from experts on his forum. > > > > > > The problem is that you can’t monitor the connection to the database > > which > > > does the writing. The connection you’re using to do the reading > doesn’t > > > know anything special about what another connection is doing. > > > > > > I can think of ways to make SQLite be slightly more helpful than > vanilla > > > SQL would be. For instance, you could use a separate file for your > > "cmd" > > > table and use > > > > > > <https://sqlite.org/pragma.html#pragma_data_version> > > > > > > PRAGMA data_version; > > > > > > to see whether any modifications at all had been done. But when you > > > figure out what you’ll actually be doing in your program, you’re still > > > executing a command and analyzing the result. And it has the > > disadvantage > > > that it’s terribly non-standard and someone reading your code won’t > > > understand it. > > > > > > I suspect that you’re better off just sticking to standard calls. > > > > > > > If a writer TRANSACTION might work, I'd be interested to know the > > minimum > > > > transaction and isolation settings that would cause readers to block > > in > > > an > > > > example of reader using sqlite3_busy_handler with the sqlite3_exec > > call > > > in > > > > a single threaded program to achieve PRAGMA busy_timeout=N. > > > > > > I recommend that you don’t try to mess with the mutex at this level. > > > Detailed behaviour of the mutex can vary a lot depending on precise > > > versions of OS, FS, storage, etc.. You could develop something which > > > worked very well on your programming computer, then find it completely > > > misses updates on another computer, or even when you just update your > OS > > or > > > replace your hard disk. Your method of storing commands in a table and > > > using conventional SQL to read them should work fine. > > > > > > > Comments on the benefits of WAL mode for this pattern would also be > > > helpful > > > > considering there is one writer that needs speedy access to > > potentially > > > > large number of of changes before the writer transaction finally > > inserts > > > > some new commands and closes the transaction for the readers to see > > the > > > > updated command table. > > > > > > Don’t try to subvert transactions to act as a mutex. You might end up > > > depending on something which stops working in a later version of > SQLite. > > > > > > Try both WAL and original modes. See which one gives you acceptable > > > behaviour. Pick that one. > > > > > > Another way to do it would be to add a "done" column to your "cmd" > table > > > containing either 0 or 1. When the command is added to the table this > > > defaults to 0. Your loop reads all rows which have this set to zero. > > You > > > then execute those commands and use an UPDATE to set the "done" column > > to 1 > > > for those rows. This means you don’t care about rowid, and are > > > better-protected against crashes. But I think the method you described > > > originally may be better than this. > > > > > > Simon. > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > 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
