Bug #106 Multi table UPDATE Deadlock with Foreign Keys
Submitted: 27 Feb 2003 6:03 Modified: 12 Mar 2003 7:23
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 2003 6:03] Alexander Keremidarski 
Description:
With exapmle Foreign Key schema, Multi-table UPDATE causes thread deadlock, thread become unkillable, mysqld can be killed only with SIGKILL and upon subsequent restart mysqld can't proceed after ...

InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: Trx id counter is 0 7424
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 6927

... mysqld Hangs here!

How to repeat:
DROP TABLE IF EXISTS parent;
DROP TABLE IF EXISTS child;

CREATE TABLE parent(id INT NOT NULL,
      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id  INT PRIMARY KEY, parent_id INT,
      INDEX par_ind (parent_id),
      FOREIGN KEY (parent_id) REFERENCES parent(id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
      ) TYPE=INNODB;

INSERT INTO parent VALUES(1);
INSERT INTO child VALUES(1, 1);

UPDATE parent,child SET parent.id=parent.id+1, child.parent_id=parent.id+1;
[11 Mar 2003 2:38] Lenz Grimmer 
Heikki, can you please take a look at this? Or should this be handled by 
Sinisa instead?
[12 Mar 2003 7:23] MySQL Verification Team 
fixed in 4.0.12