MySQL Bugs: #48106: Concurrent DELETE / INSERT throw lock wait timeout

Bug #48106	Concurrent DELETE / INSERT throw lock wait timeout
Submitted:	16 Oct 2009 7:50	Modified:	16 Nov 2009 8:15
Reporter:	Holger Hees	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DELETE in TRANSACTION A on a TABLE 1 with foreign key relationships to TABLE 2 throw a lock timeout in TRANSACTION B when i try to insert a row to TABLE 2 after a DELETE in TRANSACTON A on TABLE 1

How to repeat:
CREATE TABLE IF NOT EXISTS `test1` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `test2`
  ADD CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

#TRANSACTION A:
BEGIN;
INSERT INTO test1 SET `id`= 1, `name`='test';
INSERT INTO test2 SET `id`= 1, `name`='test';
DELETE FROM test1 WHERE `id` = 1;
# !!! NO COMMIT;
# sleep or do other

#TRANSCATION B:
BEGIN;
INSERT INTO test1 SET `id`= 2, `name`='test';
INSERT INTO test2 SET `id`= 2, `name`='test';
# now mysql throw lock timeout

TRANSACTION ISOLATION LEVEL is READ COMMITTED

I think manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html, properly explain this situation (if you take into account ON DELETE CASCADE, that is, the fact that corresponding row in test2 is also deleted):

"DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters."

Plus (from http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html):

"Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog  system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking."

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".