Looks like this page:
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
does explain somewhat how deadlocks could happen in our scenario - lock
on insert.
"InnoDB uses automatic row-level locking. You can get deadlocks even in
the case of transactions that just insert or delete a single row. That
is because these operations are not really “atomic”; they automatically
set locks on the (possibly several) index records of the row inserted or
deleted."
It would be good to follow the instructions on the page, run SHOW ENGINE
INNODB STATUS, and share the outcome. Thanks.
-Akara
Mingfan Lu wrote:
Yes I am using Olio 0.1
On Tue, Sep 29, 2009 at 2:21 AM, Akara Sucharitakul
<[email protected] <mailto:[email protected]>> wrote:
Even duplicate addresses generate a new PK. I do not quite
understand how the deadlock could happen. It looks like something is
trying to lock the table rather than the record. This is the Olio
0.1 publicly available, right?
-Akara
Mingfan Lu wrote:
In my benchmark, I found that there is a dead lock exception.
exception 'PDOException' with message 'SQLSTATE[40001]:
Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction' in
/opt/faban/faban/apache-olio-php/oliophp/classes/PDOConnection.php:77\nStack
trace:\n#0
/opt/faban/faban/apache-olio-php/oliophp/classes/PDOConnection.php(77):
PDO->exec('insert into ADD...')\n#1
/opt/faban/faban/apache-olio-php/oliophp/public_html/addPersonResult.php(79):
PDOConnection->exec('insert into ADD...')\n#2 {main}
According to the source code, it throws by
$insertaddr = "insert into ADDRESS (street1, street2, city,
state, zip, country, latitude, longitude) ".
"values ('$strt1', '$street2', '$cty',
'$state', '$zip', '$country', ".
"'$geocode->latitude', '$geocode->longitude')";
$connection->beginTransaction();
$connection->exec($insertaddr);
There is not other index but btree index.
Will we modify the insert to "insert ignore" ?
