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

• Submitted: 20 Mar 2009 10:19
• Modified: 20 Mar 2009 17:47
• Reporter: Nishant Deshpande
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.1.25, 5.1.31, 4.1, 5.0, 5.1, 6.0 bzr
• OS: Linux
• CPU Architecture: Any
• Tags: innodb_locks_unsafe_for_binlog, lock, READ-COMMITTED, unnecessary, UPDATE

[20 Mar 2009 10:19] Nishant Deshpande

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;

[20 Mar 2009 17:47] Sveta Smirnova

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;