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

[19 Jul 2007 8:04] Salama Marc
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
[19 Jul 2007 8:47] Sveta Smirnova
test case

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

[19 Jul 2007 8:48] Sveta Smirnova
Thank you for the report.

Verified as described.
[19 Jul 2007 16:03] Heikki Tuuri
This is a documented shortcoming of 'next-key locking'.