| Bug #4118 | multi-table UPDATE takes WRITE lock on read table | ||
|---|---|---|---|
| Submitted: | 12 Jun 2004 23:56 | Modified: | 2 Oct 2004 23:49 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0 | OS: | Any (all) |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[23 Jun 2004 10:48]
Michael Widenius
[15 Jul 2004 9:21]
Arnaud Lesauvage
This bug also affects multi-table deletes, where the table(s) we delete from is(are) writable, but at least one of the table in the table-reference is read only (compressed MyISAM in my case).
[12 Sep 2004 11:40]
Antony Curtis
The following patch has not been fully tested
===== ./mysql_priv.h 1.226 vs edited =====
--- 1.226/sql/mysql_priv.h 2004-08-26 12:43:26 +01:00
+++ edited/./mysql_priv.h 2004-09-12 10:19:44 +01:00
@@ -561,6 +561,7 @@
List<String> *use_index=0,
List<String> *ignore_index=0);
void set_lock_for_tables(thr_lock_type lock_type);
+int set_lock_for_update(thr_lock_type lock_type);
void add_join_on(TABLE_LIST *b,Item *expr);
void add_join_natural(TABLE_LIST *a,TABLE_LIST *b);
bool add_proc_to_list(THD *thd, Item *item);
===== ./sql_parse.cc 1.382 vs edited =====
--- 1.382/sql/sql_parse.cc 2004-08-27 12:42:58 +01:00
+++ edited/./sql_parse.cc 2004-09-12 10:21:42 +01:00
@@ -3553,6 +3553,64 @@
DBUG_VOID_RETURN;
}
+/*
+ This is used for UPDATE to ensure that only tables being written to
+ hold a write lock.
+
+ SYNOPSIS:
+ set_lock_for_update()
+ lock_type Lock to set for tables
+
+ NOTE:
+ If lock is a write lock, then tables->updating is set 1
+ This is to get tables_ok to know that the table is updated by the
+ query
+*/
+
+int set_lock_for_update(thr_lock_type lock_type)
+{
+ int res;
+ THD *thd=current_thd;
+ LEX *lex= &thd->lex;
+ TABLE_LIST *tables=(TABLE_LIST*) lex->select_lex.table_list.first;
+ SELECT_LEX *select_lex = lex->select;
+ List<Item> *fields = &select_lex->item_list;
+ List_iterator_fast<Item> field_it(*fields);
+ Item_field *item;
+ bool for_update= lock_type >= TL_READ_NO_INSERT;
+ table_map update_map = 0;
+ DBUG_ENTER("set_lock_for_update");
+ DBUG_PRINT("enter", ("lock_type: %d for_update: %d", lock_type,
+ for_update));
+
+
+ if ((res=open_tables(thd,tables)))
+ DBUG_RETURN(res);
+
+ if (setup_fields(thd, tables, *fields, 1, 0, 0))
+ DBUG_RETURN(-1);
+
+ while ((item= (Item_field *) field_it++))
+ {
+ update_map |= item->used_tables();
+ }
+
+ close_thread_tables(thd, 0);
+
+ for (TABLE_LIST *tables= (TABLE_LIST*) thd->lex.select->table_list.first ;
+ tables && update_map;
+ tables=tables->next)
+ {
+ if (update_map & 1) {
+ tables->lock_type= lock_type;
+ tables->updating= for_update;
+ }
+ update_map >>= 1;
+ }
+
+ DBUG_RETURN(0);
+}
+
/*
** This is used for UNION to create a new table list of all used tables
===== ./sql_yacc.yy 1.231 vs edited =====
--- 1.231/sql/sql_yacc.yy 2004-09-03 16:56:11 +01:00
+++ edited/./sql_yacc.yy 2004-09-12 10:18:53 +01:00
@@ -2751,10 +2751,16 @@
lex->select->order_list.next= (byte**) &lex->select->order_list.first;
}
opt_low_priority opt_ignore join_table_list
- SET update_list where_clause opt_order_clause delete_limit_clause
+ SET update_list
{
- set_lock_for_tables($3);
+ LEX *lex=Lex;
+ if (lex->select->table_list.elements == 1)
+ set_lock_for_tables($3);
+ else
+ if (set_lock_for_update($3))
+ YYABORT;
}
+ where_clause opt_order_clause delete_limit_clause {}
;
update_list:
[14 Sep 2004 3:19]
Antony Curtis
New patch committed to local repo. - does not alter call count for open_tables/lock_tables/setup_fields
[14 Sep 2004 22:01]
Antony Curtis
Latest patch: 1.2018 04/09/14 22:51:40 antony@ltantony.rdg.cyberkinetica.homeunix.net +11 -0
[25 Sep 2004 15:34]
Antony Curtis
New patch created (after review by Monty) bk commit - 4.0 tree (antony:1.2027) BUG#4118 Date: Thu, 23 Sep 2004 17:12:52 +0100 (BST) ChangeSet 1.2027 04/09/23 17:12:35 antony@ltantony.rdg.cyberkinetica.homeunix.net +7 -0 Bug#4118: multi-table UPDATE takes WRITE lock on read table Ensures that WRITE lock is not obtained on all tables referenced.
[26 Sep 2004 15:08]
Antony Curtis
bk commit - 4.0 tree (antony:1.2027) BUG#4118 Date: Sun, 26 Sep 2004 16:02:14 +0100 (BST) ChangeSet 1.2027 04/09/26 16:02:01 antony@ltantony.rdg.cyberkinetica.homeunix.net +7 -0 Bug#4118: multi-table UPDATE takes WRITE lock on read table Ensures that WRITE lock is not obtained on all tables referenced.
[2 Oct 2004 23:49]
Antony Curtis
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: Multi UPDATE takes a write lock on a table even if it is going to only read that table. How to repeat: connection 1: lock tables b read; connection 2: UPDATE a,b SET a.m=5 WHERE a.l=b.l AND a.n=5; hangs, because of the READ lock of connection 1. Whereas table 'b' is going to be read by multi update, not written. Suggested fix: This function (sql_parse.cc): void set_lock_for_tables(thr_lock_type lock_type) { bool for_update= lock_type >= TL_READ_NO_INSERT; for (TABLE_LIST *tables= (TABLE_LIST*) thd->lex.select->table_list.first ; tables ; tables=tables->next) { tables->lock_type= lock_type; tables->updating= for_update; } is called when the SET clause is parsed. But 'tables' is what was after the UPDATE keyword, so it contains the read and the written tables. So the same lock_type is applied to both tables, regardless of what is going to be done to each table. Note that this lack of distinction also affects the setting of tables->updating (which results in BUG#3989 "multi-table update will broke replication if replicate-do-table is in use"). So this function is responsible for two bugs in fact.