Re: Safely timing out DBI queries

Use the great Sys::SigAction by Lincoln Baxter.
It has never failed me.
Take a look at DBIx::HA for sample usage.

On Sep 16, 2006, at 2:27 AM, CAMPBELL, BRIAN D (BRIAN) wrote:

I've done something similar to what you did...

It went something like this...

- Run query in a separate thread.
- Display dialog box to allow user to abort (instead of having fixed timeout).
- If query finishes first, close dialog box, and query thread is done.
- If user presses abort, cancel the query which then brings control back to the thread and the thread is done.

Yes, I ran into race conditions occasionally, but that was fixed by adding locks around critical sections of code. Now, by all appearances, it works like a charm.

Now the problem is I did not use Perl and did not use DBI**. Plus I am not familiar with using Threads in Perl and with DBI so I can't tell how well a design like this translates to Perl. I'll leave that for you to evaluate if you wish.

** I used the latest language and development platform from a large monopoly company that shall remain nameless.

-----Original Message-----
From: Sam Tregar [mailto:sam@xxxxxxxxxx]
Sent: Friday, September 15, 2006 3:15 PM
To: dbi-users@xxxxxxxx
Subject: Safely timing out DBI queries


Greetings all. I'm working on an app which allows users to construct
queries using a web UI in a moderately free-form fashion. There's
plenty of data and hence plenty of rope. I need to save my users from
themselves by timing-out long-running queries and killing the MySQL
thread.

Our first attempt used alarm() and $SIG{ALRM}. For reasons described
in the DBI docs, this didn't work - the alarm fires, but only after
the database is done with the query. I did try the POSIX sigaction()
recipe from the docs, and it worked. However, I'm very nervous about
the possibility of random instability inherent in unsafe signals. I'm
hoping to find a better way.

At present I'm imagining something like:

- Parent forks a child before starting long-running query.

- Child sleeps for $timeout seconds.

- If parent finishes first, kills child and proceeds.

- If child wakes up, kills parent's MySQL thread ID and exits.
Parent gets "lost connection during query" and assumes that's a
timeout.

I am concerned about a race condition here. What happens when the
parent finishes just before the child wakes up? Seems to me the child
could kill the parent's connection and the parent might not notice.
In my case I think I can get away with this - when the parent finishes
it's about the exit() itself, and doesn't need its DB handle anymore.
I'd like to have a recipe that didn't have this potential problem
though, since I forsee needing this elsewhere in the future.

Ideas? Am I better off giving in to darkside of POSIX?

-sam

.

Relevant Pages

  • Re: Query vs form with child & parent: Deleting rows
    ... subordinate to that record, that is, all individuals (and sometimes child ... companies) whose Owner field contains the record key of the parent company. ... That second form is fed by this query: ... But I have a child table called "Channel"; ...
    (microsoft.public.access.forms)
  • Re: Queries involving subforms
    ... If you create your query, with the parent & child tables joined, and add the ... you will get as many as there are child rows ...
    (microsoft.public.access.queries)
  • RE: Safely timing out DBI queries
    ... If query finishes first, close dialog box, and query thread is done. ... Plus I am not familiar with using Threads in Perl and with DBI so I can't tell how well a design like this translates to Perl. ... Parent forks a child before starting long-running query. ...
    (perl.dbi.users)
  • Query vs form with child & parent: Deleting rows
    ... When I delete a row from a query involving both parent and child rows, ... when I display this query in a form and delete a row from there, ...
    (microsoft.public.access.forms)
  • RE: Generating Schemas from one parent table and 2 child table
    ... your query here or elaborate more on what you are trying to accomplish? ... Thiago Almeida ... table with 2 child table. ... at-identity to copy the parent id to the child/children. ...
    (microsoft.public.biztalk.general)