lock timeouts

My script does the following: forks several children, each child
has some data to update/insert into the db, each child sleep()'s
until all the child have done their work then they all commit. I
do this by having each child touch a file when they are done their
work and ready to commit. Each child then waits in a loop, counts
the number of touch files to see if all of them have finished. If
so they all commit. The problem is that Im getting alot of 'Lock
wait timeout exceeded' errors from MySQL. I have a feeling its
related to the sleeping children. Can anyone offer some background
into this? Or some suggestions to solve it. I had to fork and have
children do the work because having 1 process do it took too long.
Any suggestions would be much appreciated.

thank you

--
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: lock timeouts

On Sun, 2 May 2004, mayuran wrote:

> My script does the following: forks several children, each child
> has some data to update/insert into the db, each child sleep()'s
> until all the child have done their work then they all commit. I
> do this by having each child touch a file when they are done their
> work and ready to commit. Each child then waits in a loop, counts
> the number of touch files to see if all of them have finished. If
> so they all commit. The problem is that Im getting alot of 'Lock
> wait timeout exceeded' errors from MySQL. I have a feeling its
> related to the sleeping children. Can anyone offer some background
> into this? Or some suggestions to solve it. I had to fork and have
> children do the work because having 1 process do it took too long.
> Any suggestions would be much appreciated.

It is not the sleeping children that are causing your lock wait timeouts. It is
fact that your children, like most children, do not like to share. The easiest
fix to this would be to set autocommit on. And from your description it does not
sound like are you doing that much with the transactions anway... Are you
rolling back all of the transactions in all of the chilren if one of them fails?
If you need the txs then try limiting the time you have a transaction open (ie
commit as soon as possible), but still be prepared to replay the transaction it
in the event that you get a collision.



Here is an example script that will cauase a lock wait timeout (note the
useless update and the tx isolation level is as low as you can get):


#!/usr/local/bin/perl -w

use strict;
use warnings;
use Data::Dumper;
use DBI;


my $dsn = "dbi:mysql:rlippan";
our $dbha = DBI->connect($dsn,"","",{ AutoCommit=>0, RaiseError=>1 });
our $dbhb = DBI->connect($dsn,"","",{ AutoCommit=>0, RaiseError=>1 });


$dbha->do(q{
CREATE TABLE IF NOT EXISTS update_test (
data INTEGER,
last_update TIMESTAMP
) TYPE=InnoDB
});
$dbha->commit();

my ($stha, $sthb) = map {
$_->do(q{SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED});
$_->prepare(q{INSERT INTO update_test(data) VALUES (?)});
} $dbha, $dbhb;

eval {
$dbha->do(q{UPDATE update_test SET data=2 WHERE 1=2});
$stha->execute(1);

$sthb->execute(2);
$dbha->commit;
$dbhb->commit;
}; if ($@) {
print $@;
};

#clean up:
eval {$_->rollback for $dbha,$dbhb};
$dbha->do(q{DROP TABLE update_test});
$dbha->commit;

$_->disconnect for ($dbha, $dbhb);

__END__



Rudy


--
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: lock timeouts

On Sun, 2 May 2004, mayuran wrote:

> My script does the following: forks several children, each child
> has some data to update/insert into the db, each child sleep()'s
> until all the child have done their work then they all commit. I
> do this by having each child touch a file when they are done their
> work and ready to commit. Each child then waits in a loop, counts
> the number of touch files to see if all of them have finished. If
> so they all commit. The problem is that Im getting alot of 'Lock
> wait timeout exceeded' errors from MySQL. I have a feeling its
> related to the sleeping children. Can anyone offer some background
> into this? Or some suggestions to solve it. I had to fork and have
> children do the work because having 1 process do it took too long.
> Any suggestions would be much appreciated.

It is not the sleeping children that are causing your lock wait timeouts. It is
fact that your children, like most children, do not like to share. The easiest
fix to this would be to set autocommit on. And from your description it does not
sound like are you doing that much with the transactions anway... Are you
rolling back all of the transactions in all of the chilren if one of them fails?
If you need the txs then try limiting the time you have a transaction open (ie
commit as soon as possible), but still be prepared to replay the transaction it
in the event that you get a collision.



Here is an example script that will cauase a lock wait timeout (note the
useless update and the tx isolation level is as low as you can get):


#!/usr/local/bin/perl -w

use strict;
use warnings;
use Data::Dumper;
use DBI;


my $dsn = "dbi:mysql:rlippan";
our $dbha = DBI->connect($dsn,"","",{ AutoCommit=>0, RaiseError=>1 });
our $dbhb = DBI->connect($dsn,"","",{ AutoCommit=>0, RaiseError=>1 });


$dbha->do(q{
CREATE TABLE IF NOT EXISTS update_test (
data INTEGER,
last_update TIMESTAMP
) TYPE=InnoDB
});
$dbha->commit();

my ($stha, $sthb) = map {
$_->do(q{SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED});
$_->prepare(q{INSERT INTO update_test(data) VALUES (?)});
} $dbha, $dbhb;

eval {
$dbha->do(q{UPDATE update_test SET data=2 WHERE 1=2});
$stha->execute(1);

$sthb->execute(2);
$dbha->commit;
$dbhb->commit;
}; if ($@) {
print $@;
};

#clean up:
eval {$_->rollback for $dbha,$dbhb};
$dbha->do(q{DROP TABLE update_test});
$dbha->commit;

$_->disconnect for ($dbha, $dbhb);

__END__



Rudy


--
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