Description:
Concurrent updates on a table fails. One table is created and two scripts running concurrently, connected to one mysqld, and updating the same data leads to some problems.
Without any PK defined on the table Foo (see below), it works but is slow (about 50 reqs per second with two scripts, compared to 350 reqs per second using one scripts).
With a PK defined on the column A running two scripts triggers the following error:
DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at foo.pl line 66.
DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at foo.pl line 66.
Running one script with one PK is faster than the prev. example (500 request per second)
Scripts to reproduce this error is included.
How to repeat:
CREATE DATABASE Bar;
USE Bar;
CREATE TABLE Foo (A INTEGER, B INTEGER, C INTEGER) Type=NDB;
INSERT INTO TABLE Foo VALUES (0,0,0);
INSERT INTO TABLE Foo VALUES (1,0,0);
INSERT INTO TABLE Foo VALUES (2,0,0);
INSERT INTO TABLE Foo VALUES (3,0,0);
INSERT INTO TABLE Foo VALUES (4,0,0);
INSERT INTO TABLE Foo VALUES (5,0,0);
INSERT INTO TABLE Foo VALUES (6,0,0);
INSERT INTO TABLE Foo VALUES (7,0,0);
INSERT INTO TABLE Foo VALUES (8,0,0);
INSERT INTO TABLE Foo VALUES (9,0,0);
Run two instances of the script (below) at the same time against one mysql server.
Add a PK to generate error:
ALTER TABLE Foo ADD PRIMARY KEY(A);
Run two instances of script again.
#!/usr/bin/perl
#
# @(#)$Id$
#
use DBI;
use Time::HiRes qw (gettimeofday);
$ClientId = $ARGV[0];
$perfmod = $ARGV[1];
if (!defined $ClientId) {
print "ClientId is a required parameter.\n";
exit 1;
}
if ($perfmod <= 0) {
$perfmod = 100;
}
$dbdsn = "dbi:mysql:database=cluster;host=10.154.99.201;port=3307";
$dbuser = "root";
$dbpassword = "";
my $dbh = DBI->connect ($dbdsn,
$dbuser,
$dbpassword,{RaiseError => 0,
PrintError => 1,
AutoCommit => 0});
$checkpoint = 0;
$updates = 0;
while (1) {
#
# Get timestamp for performance computations
#
if ($checkpoint == 0) {
($sec, $usec) = gettimeofday();
$checkpoint = $sec * 1000 + int($usec / 1000);
}
$ts = time % 10;
$sth = $dbh->prepare ("UPDATE Foo SET B=?,C=C+1 WHERE A=?");
$status = $sth->execute ($ClientId, $ts);
$updates++;
if ($updates % $perfmod == 0) {
($sec, $usec) = gettimeofday();
$checkpoint = ($sec * 1000 + int($usec / 1000)) - $checkpoint;
print "================================================================\n";
print "$updates UPDATEs in $checkpoint ms " . (1000 * $updates) / $checkpoint . " req/s\n";
print "================================================================\n";
$checkpoint = 0;
$updates = 0;
}
$dbh->commit();
}
Description: Concurrent updates on a table fails. One table is created and two scripts running concurrently, connected to one mysqld, and updating the same data leads to some problems. Without any PK defined on the table Foo (see below), it works but is slow (about 50 reqs per second with two scripts, compared to 350 reqs per second using one scripts). With a PK defined on the column A running two scripts triggers the following error: DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at foo.pl line 66. DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at foo.pl line 66. Running one script with one PK is faster than the prev. example (500 request per second) Scripts to reproduce this error is included. How to repeat: CREATE DATABASE Bar; USE Bar; CREATE TABLE Foo (A INTEGER, B INTEGER, C INTEGER) Type=NDB; INSERT INTO TABLE Foo VALUES (0,0,0); INSERT INTO TABLE Foo VALUES (1,0,0); INSERT INTO TABLE Foo VALUES (2,0,0); INSERT INTO TABLE Foo VALUES (3,0,0); INSERT INTO TABLE Foo VALUES (4,0,0); INSERT INTO TABLE Foo VALUES (5,0,0); INSERT INTO TABLE Foo VALUES (6,0,0); INSERT INTO TABLE Foo VALUES (7,0,0); INSERT INTO TABLE Foo VALUES (8,0,0); INSERT INTO TABLE Foo VALUES (9,0,0); Run two instances of the script (below) at the same time against one mysql server. Add a PK to generate error: ALTER TABLE Foo ADD PRIMARY KEY(A); Run two instances of script again. #!/usr/bin/perl # # @(#)$Id$ # use DBI; use Time::HiRes qw (gettimeofday); $ClientId = $ARGV[0]; $perfmod = $ARGV[1]; if (!defined $ClientId) { print "ClientId is a required parameter.\n"; exit 1; } if ($perfmod <= 0) { $perfmod = 100; } $dbdsn = "dbi:mysql:database=cluster;host=10.154.99.201;port=3307"; $dbuser = "root"; $dbpassword = ""; my $dbh = DBI->connect ($dbdsn, $dbuser, $dbpassword,{RaiseError => 0, PrintError => 1, AutoCommit => 0}); $checkpoint = 0; $updates = 0; while (1) { # # Get timestamp for performance computations # if ($checkpoint == 0) { ($sec, $usec) = gettimeofday(); $checkpoint = $sec * 1000 + int($usec / 1000); } $ts = time % 10; $sth = $dbh->prepare ("UPDATE Foo SET B=?,C=C+1 WHERE A=?"); $status = $sth->execute ($ClientId, $ts); $updates++; if ($updates % $perfmod == 0) { ($sec, $usec) = gettimeofday(); $checkpoint = ($sec * 1000 + int($usec / 1000)) - $checkpoint; print "================================================================\n"; print "$updates UPDATEs in $checkpoint ms " . (1000 * $updates) / $checkpoint . " req/s\n"; print "================================================================\n"; $checkpoint = 0; $updates = 0; } $dbh->commit(); }