-- source include/have_innodb.inc
connect (c1, localhost, root,,);
connect (c2, localhost, root,,);

CREATE DATABASE systest1;
USE systest1;

CREATE TABLE tb1_eng1 (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   f1 INT
) ENGINE = InnoDB;

INSERT INTO systest1.tb1_eng1 VALUES (1,1),(2,2),(3,3);
COMMIT;

connection c1;

SET AUTOCOMMIT = 0;

CREATE TEMPORARY TABLE systest1.t1_tmp ( f1 INT );

INSERT INTO systest1.t1_tmp (f1)
   SELECT f1 FROM systest1.tb1_eng1 WHERE i1 = 3;
INSERT INTO systest1.t1_tmp (f1)
   SELECT f1 FROM systest1.tb1_eng1 WHERE i1 = 2;


connection c2;

SET AUTOCOMMIT = 0;
USE systest1;

CREATE TEMPORARY TABLE systest1.t2_tmp ( i1 int, new_i1 int );
INSERT INTO systest1.t2_tmp VALUES
(1,51),(2,52),(3,53);

UPDATE systest1.tb1_eng1 target
SET i1 = (SELECT new_i1 FROM systest1.t2_tmp source WHERE source.i1 = target.i1)
   WHERE i1 = 1;

--send
UPDATE systest1.tb1_eng1 target
SET i1 = (SELECT new_i1 FROM systest1.t2_tmp source WHERE source.i1 = target.i1)
   WHERE i1 = 2;

--sleep 2

connection c1;

--error ER_LOCK_DEADLOCK
INSERT INTO systest1.t1_tmp (f1)
   SELECT f1 FROM systest1.tb1_eng1 WHERE i1 = 1;

connection c2;

--reap
UPDATE systest1.tb1_eng1 target
SET i1 = (SELECT new_i1 FROM systest1.t2_tmp source WHERE source.i1 = target.i1)
   WHERE i1 = 3;

connection default;
disconnect c1;
disconnect c2;
drop database systest1;