Bug #43766 unnecessary locking on update even with tx_isolation=READ-COMMITTED or

Description:
there is unnecessary locking of non-updated tables in update statements.

bug #43160 is probably related.

How to repeat:
conn1> create table tmp_n1 (x int);
conn1> create table tmp_n2 (x int, y int);
conn1> insert into tmp_n1 values (1),(2);
conn1> insert into tmp_n2 values (1,null),(2,null);
conn1> begin; update tmp_n1 set x=3;
-- this presumably write locks tmp_n1

conn2> update tmp_n2, tmp_n1 set tmp_n2.y=1 where tmp_n2.x = tmp_n1.x;
-- this blocks presumably because it wants a read lock on tmp_n1, although given tx_isolation='READ-COMMITTED', it should not require any lock.

-- i also tried to set innodb_locks_unsafe_for_binlog=1 with the same result as above

-- note that an insert.. on duplicate key.. into tmp_n2 using tmp_n1 works
-- i.e.

conn2> create unique index tmp_n2_x_idx on tmp_n2 (x);
conn2> insert into tmp_n2 (x) select x from tmp_n1 on duplicate key update tmp_n2.y = 1;

Suggested fix:
non-updated table should not require any lock.

also perhaps not strictly relevant, not sure if this kind of syntax for a single table update with multiple join tables makes sense:

update t
from foo t
join bar t2 using (k)
set t.x=t2.x;

Thank you for the report.

Verified as described.

Falcon storage engine allows such UPDATE.

Test case for MySQL test suite:

--source include/have_innodb.inc

set global transaction isolation level read uncommitted;

create table tmp_n1 (x int) engine=innodb;
create table tmp_n2 (x int, y int) engine=innodb;

insert into tmp_n1 values (1),(2);
insert into tmp_n2 values (1,null),(2,null);
begin;
update tmp_n1 set x=3;

connect (addconroot, localhost, root,,);
connection addconroot;

begin;
update tmp_n2, tmp_n1 set tmp_n2.y=1 where tmp_n2.x = tmp_n1.x;