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
