MySQL Forums
Forum List  »  InnoDB

Simplified case
Posted by: Vitaly Litvak
Date: December 13, 2012 06:29AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
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.