Using Alarms for MySQL DB

satishkumaryarru has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have set an alarm to interrupt execution MySQL Delete operation after few seconds. But though timeout happens, it is not interrupting the DB operation. That query hangs on. Please check my below code and correct me if any thing wrong in it. Kindly help!!


eval { 
 local $SIG{ALRM} = sub { die "alarm\n" };
 alarm $timeout_in_seconds;
 $mtd_prod_sqlQuery_delete->execute($mtd_replica_row[0]);
 alarm 0;
};
if ($@) 
{
    print "Delete from MTD Prod Timeout\n";
}
[download]

Comment on Using Alarms for MySQL DB Download Code

Replies are listed 'Best First'.
Re: Using Alarms for MySQL DB by Mr. Muskrat (Canon) on May 26, 2016 at 22:01 UTC
Please wrap code in <code> or <c> tags. Writeup Formatting Tips You did not mention what OS this is running but it won't work as written on Windows: http://perldoc.perl.org/perlport.html#alarm. On other operating systems, it's also possible that safe signals is preventing it from working: http://perldoc.perl.org/perlipc.html#Deferred-Signals-(Safe-Signals). Update: You might want to read https://metacpan.org/pod/DBI#Signal-Handling-and-Canceling-Operations.	[reply]
Re^2: Using Alarms for MySQL DB (unsafe) by tye (Sage) on May 26, 2016 at 22:28 UTC
Don't miss this part of the referenced documentation: Firstly, remember that what we've done here is essentially revert to old style unsafe handling of these signals. So do as little as possible in the handler. Ideally just die(). Secondly, the handles in use at the time the signal is handled may not be safe to use afterwards. Even if you do nothing but 'die' inside of your signal handler, the "unsafe" timing of when 'die' gets called can mean that some internal data structures of Perl or of the DBI driver get corrupted. It has been a while since I've had to deal with such, but on many prior occasions it was pretty normal for an unsafe signal to cause that instance of Perl to be corrupted about 5% of the time. So you also need to do as little as possible after you get back from the 'eval' and you probably can't rely on those things always happening. And after you've tried to do those few things, you should 'exit' or 'die'. - tye	[reply]