From: Steve Hay
Date: April 26 2002 8:57am
Subject: Problem with DBD::mysql
List-Archive: http://lists.mysql.com/perl/1747
Message-Id: <3CC9168F.6090403@uk.radan.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------060801090609030306080406"

--------------060801090609030306080406
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm experiencing deadlock errors on BDB tables when using DBD::mysql. 
 I've whittled it down to an unbelievably simple program ("deadlock.pl", 
attached).  This program runs fine on its own, but when I run two of 
them together, one of them deadlocks in a matter of seconds.  (I use the 
attached "db.bat" batch file to (re-)create the database which this 
program uses.)

The error is:
DBD::mysql::st execute failed: Deadlock found when trying to get lock; 
Try restarting transaction at D:\Temp\deadlock.pl line 14.

I can't understand why this program should have deadlock errors in the 
first place, but what is even stranger (and is why I'm mailing to *this* 
list) is that it runs fine if I use DBD::ADO instead of DBD::mysql.  (I 
also tried DBD::ODBC; that had the same deadlock errors as DBD::mysql.) 
 However, I'm not keen to use the DBD::ADO driver because it doesn't 
(currently) support bind parameters, which I'm making much use of.

Also, the same program with only the DBI connect string changed to 
access a Sybase database works fine with the main Sybase DBD driver 
(DBD::ASAny) and with DBD::ADO and DBD::ODBC.

Any help would be greatly appreciated.

Steve Hay

---

Version info:

OS: Windows NT4, SP6
Perl: 5.6.1, patched to the same level as ActivePerl Build 631
DBI: 1.21
DBD::mysql: 2.1013
DBD::ADO: 2.4
DBD::ODBC: 0.40
MySQL: 3.23.49a-max-nt
MyODBC: 2.50.39


--------------060801090609030306080406
Content-Type: application/x-perl;
 name="deadlock.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="deadlock.pl"


use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:mysql:test', 'root', '',
			{AutoCommit => 0, PrintError => 0, RaiseError => 1});

for (my $i = 0; ; $i++) {
	print "i = $i\n";

	my $sth = $dbh->prepare("INSERT INTO x (id) VALUES ($i)");
	$sth->execute();
	$sth->finish();

	$dbh->commit();
}

$dbh->disconnect();

--------------060801090609030306080406
Content-Type: text/plain;
 name="db.bat"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="db.bat"

@echo off
\mysql\bin\mysqladmin -f drop test >NUL 2>&1
\mysql\bin\mysqladmin create test
\mysql\bin\mysql -u root -e "CREATE TABLE x (id INT) TYPE=BDB" test

--------------060801090609030306080406--