Hi,
I'm trying to run two separate programs which connect to a single MySQL
database and attempt to perform transactions on its BDB tables. I'm
running MySQL 3.23.49a Max on Windows (NT4). The two programs in
question are both Windows "Services" written in Perl (5.6.1), and they
are using the Perl DBI (DBD::mysql, Version 2.1013) to perform the
transactions.
Both programs have the "AutoCommit" flag switched off so that nothing is
committed until the whole transaction is complete. If anything goes
wrong part-way through then the changes thus-far are rolled back and the
program reports the error to the user.
One program is running in a fairly tight loop in which it performs a
transaction (which may involve INSERTs, UPDATEs and/or DELETEs) about
once a second. The other program has smaller bursts of activity in
which it may perform half a dozen similar transactions in rapid
succession and then be inactive for a few minutes or more.
Each program runs fine on its own, but when I try and run them both
together I intermittently get the following error:
[1213]: "Deadlock found when trying to get lock; Try restarting
transaction"
Firstly, I'm not certain what happens when I get this error. Whichever
of the two programs gets the error does appear to carry on afterwards,
so I'm assuming that MySQL/BerkeleyDB has detected the deadlock and
rolled back one of the (partially-completed) transactions so that the
other program can continue normally, hence the message that I need to
restart the transaction in the program that got the error. Is this correct?
Secondly, since I assume there is no way to completely stop this
happening (?) what should I do when it does occur? The obvious thing to
do seems to be to wrap the transaction in a loop like the following:
do { transaction } while deadlock-error
but this could potentially get stuck in the loop forevermore. If I add
a "threshold" to limit the number of retries to some maximum limit then
the transaction could still not get performed. It is also very tedious
to have to wrap all my transactions in such a loop.
I've tried setting "berkeley_trans_retry" to 10 in the file
"sql\ha_berkeley.cpp" as described in previous postings on this subject,
but I still get the same problem, proving that simply retrying is not
really a solution.
I've also tried all four settings of the "--transaction-isolation"
command line option, but to no avail. (I don't know if that should have
helped or not.)
To make things worse I also intermittently get other strange errors like:
[19]: fetch() without execute()
[1062]: Duplicate entry '116' for key 1.
There is nothing so fundamentally wrong with the programs that they do
this every time, just once in a while when they're getting hammered hard.
What on earth is going on?
Help!
Steve Hay
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
