Benjamin Pflugmann wrote:
>>>[...] Which table
>>>type (MyISAM/InnoDB/BDB) do you use? Do you intend to use transactions
>>>or not?
>>>
>>I am using BDB tables, and I am making use of transactions.
>>
>Okay, than that is the reason for the deadlocks and it is to be
>expected, as far as I understand (as writers are involved):
>http://www.mysql.com/doc/B/D/BDB_characteristics.html
>
>BDB tables use optimistic locking, i.e. it is assumed that you are
>lucky, and no one else will need the same locks as you do. If you
>conflict with other locks, one of your will gets an deadlock error.
>(From http://www.sleepycat.com/docs/ref/transapp/put.html - did not
>find an explanation in the MySQL manual :-( )
>
This reference is very useful, actually. If I modify my test program to
detect the deadlock as shown in the example in your reference (see code
below) then two instances of the program now seem to run quite happily
together, with one or the other occasionally going through phases like
[...]
i = 396
i = 397
Retrying 397
i = 397
Retrying 397
i = 397
i = 398
[...]
and then continuing successfully. (Obviously I need an upper limit on
the number of retries.)
------------------------------
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";
# "Catch" any errors in the do() or commit()...
eval {
$dbh->do("INSERT INTO x (id) VALUES ($i)");
$dbh->commit();
};
# ... then check if there was an error. (Deadlock error is 1213.)
if ($@) {
if ($DBI::err == 1213) {
print "Deadlock! Retrying ...\n";
redo;
}
else {
print "Error! ($DBI::errstr) Exiting ...\n";
last;
}
}
}
$dbh->disconnect();
------------------------------
This is slightly tedious to do every time I wish to perform a
transaction. It would be nice if the database automatically retried a
certain number of times itself before giving the error, and coming to
think of it I've tried fiddling with a flag called
"berkeley_trans_retry" in the MySQL source code (sql/ha_berkeley.cpp)
which looked like it might do just that, but it didn't seem to make much
difference. Maybe I'll put some debug in to see if it really is
retrying 10 times when MySQL is built with the flag set to 10.
Also, as the BerkeleyDB docs say, there is no guarantee that the
transaction will ever succeed, which could be a bit of a problem (!) Is
there any way to "favour" the transactions being performed by one client
over those being performed by another to make sure some chosen client's
transactions *do* always succeed? The docs simply say that one thread
is selected to have its locks discarded (and receieve a deadlock error),
but can I control *which* thread?
In the real application code that I'm writing, I have two processes
accessing the database (both of them both reading and writing), one of
which is fairly busy all the time, the other of which only springs to
life for a relatively short period of time about once an hour. I would
like to "favour" the latter process during its short(ish) bursts of
activity if possible. The other process could simply wait and retry,
knowing that sooner or later the hourly process will finish it's burst
of activity, enabling it to continue.
>This happens rather seldom, as BDB uses page locks, which only block a
>small part of the table.
>
What is a "page lock"? Is it a lock on one row, a certain number of
rows, or the whole table?
>>>A possible reason that you observe the rollback one time and not the
>>>other could be that the mysql client sets auto-commit differently?
>>>
>>Both clients are instances of the same program running on the same
>>machine, so I don't believe they are setting auto-commit differently.
>>
>Sorry, I meant the Perl vs. the command line client. I.e. Did you
>assure, that the command line client also does not auto-commit, when
>you run the INSERTs?
>
>I am not sure if you mean exactly this by your next comment, because
>you interpreted my former statement differently.
>
>>Repeating the exercise explicitly setting "auto-commit=0;" in each of
>>them makes do difference either.
>>
OK, I'm now definitely running the two command line clients in NON
auto-commit mode, and it DOES make difference. I must have cocked it up
before when I thought it made no difference. If I put the following
Perl code into a file called "insert.pl" and then run "perl insert.pl |
\mysql\bin\mysql -u root test" then I do get one or other client falling
over quickly with the error "ERROR 1213 at line 3716: Deadlock found
when trying to get lock; Try restarting transaction".
------------------------------
print "SET AUTOCOMMIT=0;\n";
for my $i (1 .. 10000) {
print "INSERT INTO x (id) VALUES ($i);\n";
print "COMMIT;\n";
}
------------------------------
>Okay, now we know the fact that the processes may deadlock. The
>question is why it does not happen in every environment.
>
>That it does not happen with Sybase could simply mean, that Sybase
>uses a different locking approach or is able to lock inserts in a way
>that would never deadlock. I don't know Sybase good enough to comment
>on this.
>
Nor me.
>Well, that DBD::ADO and the mysql client work could mean, that
>auto-commit is enabled - could you check that it is in fact disabled
>on the server side by quering the value... hm. I don't find a way to
>query the status of a variable set via SET. Well, then how about
>trying to create a deadlock by purpose (using an explicit LOCK TABLE
>for the first client and an update for the second or so...).
>
As described above, I think the mysql client must have been
autocommitting before. Now that autocommit is definitely disabled it
gets deadlock too.
So presumably DBD::ADO is also autocommitting. (Perhaps it doesn't
honour the AutoCommit flag in my Perl program?)
>Hm. Maybe I am completely wrong and MySQL/BDB works in a way that
>INSERTs of two clients are distributed to pages so that they don't
>conflict (i.e. starting a new page for the second client), but I
>couldn't find any hint on that in one of the two manual.
>
If anybody else out there knows anything relevant about page locks in
MySQL/BDB, I'd be glad to hear it.
Thanks,
Steve
---------------------------------------------------------------------
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
