MySQL Bugs: #29891: innodb and lock on delete

Bug #29891	innodb and lock on delete
Submitted:	19 Jul 2007 8:04	Modified:	19 Jul 2007 16:03
Reporter:	Salama Marc	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.45, 4.1, 5.0, 5.1 BK	OS:	Any (Linux, Windows, Mac)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
on innodb table with autocommit=0 and a autoincrement column.

If you try to delete a row who do not exist a lock is on all row and other session  can not insert any row before the commit

I don't speek good english. I hope you understand me

Thanks

marc@salama.fr

How to repeat:
CREATE TABLE `toto` (
  `id` tinyint(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Champ1` int(11) DEFAULT NULL,
  `Champ2` char(50) DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `zero` (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `Deux` (`Champ2`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1

you open 2 sessions : s1 and s2

on s1 :
set autocommit=0;
delete from toto where id =4444 (any value wich not exist);
insert into toto (id,champ1,champ2) values(null,5,'xxx');

on s2
insert into toto (id,champ1,champ2) values(null,8,'yyyyy');
and you get error 1205 (lock wait timeout)

If there is a row with id=4444 no problem

test case

Attachment: bug29891.test (application/octet-stream, text), 567 bytes.

Thank you for the report.

Verified as described.

This is a documented shortcoming of 'next-key locking'.