Thread notification for new record in a table.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Thread notification for new record in a table.

John Found
What is the best way (less CPU consuming) to put a thread in sleep and wake it up when new record has been written to a given table of SQLite database?

Now I am implementing this by polling and time based sleep, but such solution is very dirty compromise, trading response time for CPU load. I want both. :)

--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thread notification for new record in a table.

Rowan Worth-2
Is the database being updated by a separate process, or by another thread
in the same process?

If the former, I don't think you've any choice but to poll the database.
But by taking advantage of PRAGMA data_version and a clever schema you can
make that polling pretty lightweight. If you really don't want to sleep you
could use inotify or similar to only wake up after the database has been
written to, though the result will be significantly more complicated and
less portable; I'm not sure there's much value in it and it's certainly
getting off-topic for an sqlite mailing list.

If the latter then there's plenty of ways the thread updating the database
can notify the waiting thread, depending on your platform(s) and
language(s) of choice. But none of them really have anything to do with
sqlite either :)

-Rowan

On 2 May 2017 at 15:00, John Found <[hidden email]> wrote:

> What is the best way (less CPU consuming) to put a thread in sleep and
> wake it up when new record has been written to a given table of SQLite
> database?
>
> Now I am implementing this by polling and time based sleep, but such
> solution is very dirty compromise, trading response time for CPU load. I
> want both. :)
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[hidden email]>
> _______________________________________________
> 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
|

Re: Thread notification for new record in a table.

Clemens Ladisch
Rowan Worth wrote:
> Is the database being updated by a separate process, or by another thread
> in the same process?
> [...]
> If the latter then there's plenty of ways the thread updating the database
> can notify the waiting thread, depending on your platform(s) and
> language(s) of choice.

If the former, there's plenty of ways to do interprocess communication,
depending on the platform(s) and language(s) of choice.

Except when monitoring for file changes (inotify etc.), all this
requires the cooperation of the application that does the update.


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

Re: Thread notification for new record in a table.

Olivier Mascia
In reply to this post by John Found
> Le 2 mai 2017 à 09:00, John Found <[hidden email]> a écrit :
>
> What is the best way (less CPU consuming) to put a thread in sleep and wake it up when new record has been written to a given table of SQLite database?
>
> Now I am implementing this by polling and time based sleep, but such solution is very dirty compromise, trading response time for CPU load. I want both. :)
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[hidden email]>

Reading your question I assume a single, multi-threaded, application.  You could write a SQL function (see sqlite3_create_function_v2 and associates) which signal an event.  And add a SQL trigger calling this function when appropriate.


--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



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

Re: Thread notification for new record in a table.

John Found
On Tue, 2 May 2017 09:38:30 +0200
Olivier Mascia <[hidden email]> wrote:

> > Le 2 mai 2017 à 09:00, John Found <[hidden email]> a écrit :
> >
> > What is the best way (less CPU consuming) to put a thread in sleep and wake it up when new record has been written to a given table of SQLite database?
> >
> > Now I am implementing this by polling and time based sleep, but such solution is very dirty compromise, trading response time for CPU load. I want both. :)
> >
> > --
> > http://fresh.flatassembler.net
> > http://asm32.info
> > John Found <[hidden email]>
>
> Reading your question I assume a single, multi-threaded, application.  You could write a SQL function (see sqlite3_create_function_v2 and associates) which signal an event.  And add a SQL trigger calling this function when appropriate.

In my case I have several running applications that write in the database.
But isn't this solution applicable nevertheless? Maybe with some kind of IPC.

>
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia, http://integral.software
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thread notification for new record in a table.

Olivier Mascia
> Le 2 mai 2017 à 09:48, John Found <[hidden email]> a écrit :
>
>> Reading your question I assume a single, multi-threaded, application.  You could write a SQL function (see sqlite3_create_function_v2 and associates) which signal an event.  And add a SQL trigger calling this function when appropriate.
>
> In my case I have several running applications that write in the database.
> But isn't this solution applicable nevertheless? Maybe with some kind of IPC.

Of course. My comfort-zone is in Windows systems. An event can be shared by multiple processes. So that is an IPC. Equivalent things are of course available in all other platforms.

Using a C-defined SQL function is not a requirement though. All you have to do is add C code right after each insert/update significant to you to 'signal' whatever IPC you have chosen to implement.

The C-defined SQL function is just sugar on the cake to not forget to call that IPC mechanism from each and every place you would need it.  Indeed the "database engine" is actually made up of both your C code calling SQLite API and SQLite itself.

We took this path (C-defined SQL function) while upgrading a large code-base system which used another database engine which had a SQL mean to signal an EVENT to the client application.  It was the simplest path to follow in that case, instead of patching the client code in hundreds locations and risk forgetting some.


--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: Thread notification for new record in a table.

petern
In reply to this post by John Found
FYI. I proposed a portable solution for a responsive interprocess work
queue within SQLite without using native IPC less than 2 weeks ago on this
very board.  [See Dori the forgetful fish.]

https://www.mail-archive.com/sqlite-users@mailinglists.
sqlite.org/msg102741.html

DB reader(s) block/poll up to a specified busy_timeout but are directly
synchronized to completion of the DB writer's transaction payload.

See: http://sqlite.org/c3ref/busy_timeout.html

Basically, Instead of sleep(), the reader(s) set busy_timeout().  Thus, the
polling interval can be very long without reducing responsiveness.




On Tue, May 2, 2017 at 12:48 AM, John Found <[hidden email]> wrote:

> On Tue, 2 May 2017 09:38:30 +0200
> Olivier Mascia <[hidden email]> wrote:
>
> > > Le 2 mai 2017 à 09:00, John Found <[hidden email]> a écrit :
> > >
> > > What is the best way (less CPU consuming) to put a thread in sleep and
> wake it up when new record has been written to a given table of SQLite
> database?
> > >
> > > Now I am implementing this by polling and time based sleep, but such
> solution is very dirty compromise, trading response time for CPU load. I
> want both. :)
> > >
> > > --
> > > http://fresh.flatassembler.net
> > > http://asm32.info
> > > John Found <[hidden email]>
> >
> > Reading your question I assume a single, multi-threaded, application.
> You could write a SQL function (see sqlite3_create_function_v2 and
> associates) which signal an event.  And add a SQL trigger calling this
> function when appropriate.
>
> In my case I have several running applications that write in the database.
> But isn't this solution applicable nevertheless? Maybe with some kind of
> IPC.
>
> >
> >
> > --
> > Best Regards, Meilleures salutations, Met vriendelijke groeten,
> > Olivier Mascia, http://integral.software
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[hidden email]>
> _______________________________________________
> 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