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
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0
• OS: Any (ALL)
• 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] MySQL Verification Team

===== 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;