MySQL Bugs: #8752: Multi table updates in stored procedure hangs the server

Bug #8752	Multi table updates in stored procedure hangs the server
Submitted:	23 Feb 2005 22:45	Modified:	14 Apr 2005 5:11
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.2-alpha-max-log	OS:	Windows (WinXp Professional)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using multitable in a stored procdure hangs the server indefinately.

Cannot go to the next line of the procedure (after multi-table update sql st.)

How to repeat:

try this 
create table t1(id int,name varchar(10));
insert into t1 values(1,'abc');
insert into t1 values(2,'xyz');
insert into t1 values(3,'pqr');

create table t2(id int,name varchar(10));
insert into t2 values(10,'sometext');
insert into t2 values(2,'updating text');
insert into t2 values(30,'something');

-- create a procedure
delimiter $$
drop procedure if exist test_multi_table_updates$$
create procedure test_multi_table_updates()

begin
UPDATE
    t1,t2
SET
   t1.name=t2.name
WHERE
    t1.id=t2.id;

SELECT 'process complete';
end$$
delimiter ;

you won't see the message 'process complete' although update has been done

Suggested fix:
I do not know

** Multi-table updates in a stored procedure hangs the server

This is related to the bug #6950.

Sorry I tested with newer version and the client hangs not the
server. I will try it with the latest BK source.

Thank you for the bug report.

The new table locking scheme has change the behaviour, so now you get
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

Hi!

Thank you for reporting this!

Actually there are two separate issues here:
1) Problem with locks which was introduced by new SP-locking implementation.
2) Successfull execution of multi-update in SP causes connection stall.

Both these issues were also reported as bug #9486 for which I just have 
proposed fix. So I will mark this bug report as duplicate.