Simplified case
Issue also can be reproduced in a simpler case when there are only two tables with couple of records, but the child table doesn't has an index on the parent table ref column.
Create parent table
CREATE TABLE `parent` (
`id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Create child table
CREATE TABLE `child` (
`id` int(10) unsigned NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Fill tables
INSERT INTO `parent` (id) VALUES (1), (2);
INSERT INTO `child` (id, parent_id) VALUES (1, NULL), (2, NULL);
Test in two parallel transactions:
Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c
INNER JOIN parent p ON p.id = c.parent_id
WHERE p.id = 1;
Transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c
INNER JOIN parent p ON p.id = c.parent_id
WHERE p.id = 2;
The common part in both cases is that MySQL doesn't use indices. I believe that's the reason of lock of entire table.
Subject
Views
Written By
Posted
2095
December 12, 2012 10:43AM
Simplified case
980
December 13, 2012 06:29AM
909
December 20, 2012 10:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.