Bug #5785 lock timeout during concurrent update

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();
}

All in one file...

Attachment: bug-lock-timeout (application/octet-stream, text), 2.31 KiB.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html