table locking with forking

I'm having issues with locking and forking code. I've consolidated the
problem into the below example code.

I expect the parent to start, lock the table, and spawn the child. I
then expect the child to die without inserting data, because the parent
had the table locked longer than the child was allowed to wait. The
parent should then say "count: 0" and exit.

But instead, the child spawns, waits for the parent to unlock (ignoring
the alarm?), and inserts the data.

What do I have to do to change the behavior so that the child times out
and immediately stops trying to insert the data after a specified time
period ?

my $dbh = DBI->connect($dsn, $dbun, $dbpw, {RaiseError => 1});

$dbh->do('DELETE FROM table1 WHERE field1 > 1');
$dbh->do('LOCK TABLE table1 READ');
my $pid = fork();
if($pid){
print "PARENT: starting.\n";
foreach(1..5){
print "PARENT: sleeping [$_/5]\n";
sleep 1;
}
$dbh->do('UNLOCK TABLES');
print "PARENT: exiting.\n";

my $sql = 'SELECT COUNT(*) FROM table1 WHERE field1 = 5';
my $sth = $dbh->prepare($sql);
$sth->execute;
my $row = $sth->fetchrow_arrayref;
print "PARENT: count $row->[0]\n";

}else{
print "CHILD: starting.\n";
$dbh->{InactiveDestroy} = 1; # dont kill the parent $dbh

eval {
local $SIG{ALRM} = sub { die "timeout."; };
alarm(3);
my $dbh = DBI->connect($dsn, $dbun, $dbpw, {RaiseError => 1});
print "CHILD: db connection succeeded\n";

my $sql = 'INSERT INTO table1 (field1) VALUES (5)';
my $sth = $dbh->prepare($sql);
$sth->execute;

print "CHILD: inserted data.\n";
alarm(0);
};
alarm(0);
if($@){
print "CHILD: $@\n";
}
print "CHILD: exiting.\n";
}

--

Jeremy Kister
http://jeremy.kister.net./

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: table locking with forking

Hi Jeremy,

Jeremy Kister wrote:
> I'm having issues with locking and forking code. I've consolidated the
> problem into the below example code.
>
> I expect the parent to start, lock the table, and spawn the child. I
> then expect the child to die without inserting data, because the parent
> had the table locked longer than the child was allowed to wait. The
> parent should then say "count: 0" and exit.
>
> But instead, the child spawns, waits for the parent to unlock (ignoring
> the alarm?), and inserts the data.
>
> What do I have to do to change the behavior so that the child times out
> and immediately stops trying to insert the data after a specified time
> period ?
>
> my $dbh = DBI->connect($dsn, $dbun, $dbpw, {RaiseError => 1});

I think you need to set InactiveDestroy here. Setting it after the fork
is probably too late.

>
> $dbh->do('DELETE FROM table1 WHERE field1 > 1');
> $dbh->do('LOCK TABLE table1 READ');
> my $pid = fork();
> if($pid){
> print "PARENT: starting.\n";
> foreach(1..5){
> print "PARENT: sleeping [$_/5]\n";
> sleep 1;
> }
> $dbh->do('UNLOCK TABLES');
> print "PARENT: exiting.\n";
>
> my $sql = 'SELECT COUNT(*) FROM table1 WHERE field1 = 5';
> my $sth = $dbh->prepare($sql);
> $sth->execute;
> my $row = $sth->fetchrow_arrayref;
> print "PARENT: count $row->[0]\n";
>
> }else{
> print "CHILD: starting.\n";
> $dbh->{InactiveDestroy} = 1; # dont kill the parent $dbh
>
> eval {
> local $SIG{ALRM} = sub { die "timeout."; };
> alarm(3);
> my $dbh = DBI->connect($dsn, $dbun, $dbpw, {RaiseError => 1});
> print "CHILD: db connection succeeded\n";
>
> my $sql = 'INSERT INTO table1 (field1) VALUES (5)';
> my $sth = $dbh->prepare($sql);
> $sth->execute;
>
> print "CHILD: inserted data.\n";
> alarm(0);
> };
> alarm(0);
> if($@){
> print "CHILD: $@\n";
> }
> print "CHILD: exiting.\n";
> }
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: table locking with forking

--_002_1EEA8FAEE559BE49B397E42A3D42A5A70BA334CBA9floyduniver se_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

> -----Original Message-----
> From: Jeremy Kister [mailto:perl-mysql@jeremykister.com]
> Sent: Tuesday, September 18, 2007 11:44 PM
> To: perl@lists.mysql.com
> Subject: table locking with forking
>
> I'm having issues with locking and forking code. I've consolidated the
> problem into the below example code.

I played around with this, and came up with the attached code. The key was=
using InnoDB with commit() in the right place.

I don't understand the behavior of the original script; it looks like the I=
NSERT from the child process gets queued up by MySQL, and the killing of th=
e child doesn't affect it.



--_002_1EEA8FAEE559BE49B397E42A3D42A5A70BA334CBA9floyduniver se_
Content-Type: text/plain; charset=us-ascii


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
--_002_1EEA8FAEE559BE49B397E42A3D42A5A70BA334CBA9floyduniver se_--

RE: table locking with forking

On Sep 19, 2007; 12:19pm, John Trammel wrote:
> I don't understand the behavior of the original script; it looks like
> the INSERT from the child process gets queued up by MySQL, and the
> killing of the child doesn't affect it.

For the archives, I've figured it out:

the answer is to use Sys::SigAction instead of $SIG{ALRM}

see http://search.cpan.org/~lbaxter/Sys-SigAction/lib/Sys/SigAct ion.pm

everything now works the way i expected.

--

Jeremy Kister
http://jeremy.kister.net./


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org