Bug #274 Too many tables locked in multi-table updates
Submitted: 11 Apr 2003 9:54 Modified: 11 Apr 2003 9:54
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.0 OS:Any (ALL)
Assigned to: CPU Architecture:Any

[11 Apr 2003 9:54] Sinisa Milivojevic
Description:
All tables in multi-table updates get locked, although not all of them are necessarily updated.

How to repeat:
In one connection run this:

update t1,t2 set t2.a=t1.a where t2.id=t1.id ...

In other window run:

update t1 set t1.b="..." where ....

Suggested fix:
release some locks
[11 Apr 2003 9:54] Sinisa Milivojevic
===== sql/sql_update.cc 1.80 vs edited =====
*** /tmp/sql_update.cc-1.80-3596        Wed Apr  2 17:05:30 2003
--- edited/sql/sql_update.cc    Wed Apr  9 15:17:33 2003
***************
*** 478,484 ****
--- 478,491 ----
        table->used_keys=0;
        table->pos_in_table_list= tl;
      }
+     else
+     {
+       table_ref->lock_type=TL_READ;
+       mysql_unlock_some_tables(thd,&table,1);
+     }
    }
+ 
+ 
    table_count=  update.elements;
    update_tables= (TABLE_LIST*) update.first;