MySQL Bugs: #60198: load data/Insert blocks delete from another session

Bug #60198	load data/Insert blocks delete from another session
Submitted:	22 Feb 2011 8:28	Modified:	10 Sep 2011 19:19
Reporter:	Vinod Sugur	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.55	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb locks

Description:
Load data/Insert statement blocks delete from another session

How to repeat:

1. DDL statement:

CREATE TABLE detail (
  `mid` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL auto_increment,
  `data1` varchar(100) DEFAULT NULL,
  `data2` varchar(100) DEFAULT NULL,
  `fkid` int,
  PRIMARY KEY (`id`),
  KEY `f_key` (`mid`),
  KEY `f_key1` (`fkid`),
  CONSTRAINT `f_key` FOREIGN KEY (`mid`) REFERENCES `master` (`id`),
  CONSTRAINT `f_key1` FOREIGN KEY (`fkid`) REFERENCES `master_1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `master_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

2. Populate data in all tables:

INSERT INTO master values(1),(2),(3);

INSERT INTO master_1 VALUES(1,'bbb'),(2,'bbb'),(3,'bcc'),(4,'ddd');

LOAD DATA local INFILE  'D:/Projects/test/detail1.csv' INTO TABLE detail
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\r\n' 
(mid,data1,data2);

Populate detail table using above load statement and update fkid for values 1,2,3,4;

Steps to reproduce:

1. 

start transaction;
Query OK, 0 rows affected (0.00 sec)

 LOAD DATA local INFILE  'D:/Projects/test/detail.csv' INTO TABLE detail
           FIELDS TERMINATED BY ','
		   LINES TERMINATED BY '\r\n'
			(mid,data1,data2);
Query OK, 800000 rows affected (30.94 sec)
Records: 800000  Deleted: 0  Skipped: 0  Warnings: 0

2.

start transaction;
Query OK, 0 rows affected (0.00 sec)

delete  FROM detail where fkid = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Detail Error:
============================================
LOCK WAIT 17886 lock struct(s), heap size 1764672, 5617884 row lock(s), undo log entries 1600000
MySQL thread id 2, query id 80 localhost 127.0.0.1 root updating
delete  FROM detail where fkid = 1
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 33510 n bits 480 index `PRIMARY` of table `test`.`detail` trx id 0 38929 lock_mode X waiting
Record lock, heap no 167 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 809ad397; asc     ;; 1: len 6; hex 000000009810; asc       ;; 2: len 7; hex 80000040050110; asc    @   ;; 3: len 4; hex 80000002; asc     ;; 4: l
en 3; hex 616161; asc aaa;; 5: len 3; hex 626262; asc bbb;; 6: SQL NULL;

------------------
---TRANSACTION 0 38928, ACTIVE 115 sec, OS thread id 5224
4 lock struct(s), heap size 320, 2 row lock(s), undo log entries 800000
============================================

This lock also occurs if we insert only one row in detail table using following command:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into detail (mid,id,data1,data2,fkid) values(2,null,'aaa','bbb',4);
Query OK, 1 row affected (0.09 sec)

Suggested fix:
Delete should execute successfully as it has different criteria for deletion.

detail table data

Attachment: detail1.csv (application/vnd.ms-excel, text), 495.85 KiB.

Please use attached file detail1.csv to populate detail table using LOAD DATA command

Hi,

Is this bug related to Bug #19762?

Thanks,
Vinod Sugur

Thank you for the report.

Which transaction isolation level do you use?

Hi,

I have tested this with "REPEATABLE READ" and "READ COMMITED" isolation level.

Thanks,
Vinod Sugur

Thank you for the feedback.

I can not repeat described behavior. Please try with current version 5.1.58 and if problem still exists send us your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".