#At file:///home/dlenev/src/bzr/mysql-next-4284-nl-review-2/ based on revid:dlenev@stripped
3060 Dmitry Lenev 2010-01-26
Tentative patch implementing new type-of-operation-aware metadata locks.
Fixes bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock" and bug
#37346 "innodb does not detect deadlock between update and alter table".
After review fixes in progress.
Disallowing DDL under LOCK TABLES on implicitly locked tables allows
to simplify metadata locking.
modified:
mysql-test/r/lock.result
mysql-test/r/lock_multi.result
mysql-test/t/lock.test
mysql-test/t/lock_multi.test
sql/mysql_priv.h
sql/sql_base.cc
sql/sql_delete.cc
sql/sql_parse.cc
sql/sql_table.cc
sql/sql_trigger.cc
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result 2010-01-15 11:47:22 +0000
+++ b/mysql-test/r/lock.result 2010-01-26 17:46:07 +0000
@@ -151,6 +151,12 @@ select * from t2;
a
select * from t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
+Dropping of implicitly locked table is disallowed.
+drop table t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+Now let us also lock table explicitly and drop it.
+lock tables t1 write, v_bug5719 write;
drop table t1;
sic: left LOCK TABLES mode
@@ -282,6 +288,79 @@ insert into t1 values (1);
# Ensure that metadata locks held by the transaction are released.
drop table t1;
#
+# Coverage for situations when we try to execute DDL on tables
+# which are locked by LOCK TABLES only implicitly.
+#
+drop tables if exists t1, t2;
+drop view if exists v1;
+drop function if exists f1;
+create table t1 (i int);
+create table t2 (j int);
+#
+# Try to perform DDL on table which is locked through view.
+create view v1 as select * from t2;
+lock tables t1 write, v1 write;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table Op Msg_type Msg_text
+test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated
+test.t2 repair status Operation failed
+unlock tables;
+drop view v1;
+#
+# Now, try DDL on table which is locked through routine.
+create function f1 () returns int
+begin
+insert into t2 values (1);
+return 0;
+end|
+create view v1 as select f1() from t1;
+lock tables v1 read;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table Op Msg_type Msg_text
+test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated
+test.t2 repair status Operation failed
+unlock tables;
+drop view v1;
+drop function f1;
+#
+# Finally, try DDL on table which is locked thanks to trigger.
+create trigger t1_ai after insert on t1 for each row insert into t2 values (1);
+lock tables t1 write;
+flush table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+drop table t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+alter table t2 add column k int;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+# Repair produces error as part of its result set.
+repair table t2;
+Table Op Msg_type Msg_text
+test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated
+test.t2 repair status Operation failed
+unlock tables;
+drop trigger t1_ai;
+drop tables t1, t2;
+#
# Bug#45035 " Altering table under LOCK TABLES results in
# "Error 1213 Deadlock found..."
#
=== modified file 'mysql-test/r/lock_multi.result'
--- a/mysql-test/r/lock_multi.result 2010-01-22 05:53:57 +0000
+++ b/mysql-test/r/lock_multi.result 2010-01-26 17:46:07 +0000
@@ -278,14 +278,15 @@ DROP VIEW IF EXISTS v1;
#
# Test 1: LOCK TABLES v1 WRITE, t1 READ;
#
+# Thanks to the fact that we no longer allow DDL on tables
+# which are locked for write implicitly, the exact scenario
+# in which assert was failing is no longer repeatable.
CREATE TABLE t1 ( f1 integer );
CREATE VIEW v1 AS SELECT f1 FROM t1 ;
-# Connection 2
LOCK TABLES v1 WRITE, t1 READ;
FLUSH TABLE t1;
-# Connection 1
-LOCK TABLES t1 WRITE;
-FLUSH TABLE t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+UNLOCK TABLES;
DROP TABLE t1;
DROP VIEW v1;
#
=== modified file 'mysql-test/t/lock.test'
--- a/mysql-test/t/lock.test 2010-01-15 11:47:22 +0000
+++ b/mysql-test/t/lock.test 2010-01-26 17:46:07 +0000
@@ -202,6 +202,12 @@ select * from t1;
select * from t2;
--error ER_TABLE_NOT_LOCKED
select * from t3;
+--echo Dropping of implicitly locked table is disallowed.
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1;
+unlock tables;
+--echo Now let us also lock table explicitly and drop it.
+lock tables t1 write, v_bug5719 write;
drop table t1;
--echo
--echo sic: left LOCK TABLES mode
@@ -349,6 +355,76 @@ drop table t1;
--echo #
+--echo # Coverage for situations when we try to execute DDL on tables
+--echo # which are locked by LOCK TABLES only implicitly.
+--echo #
+--disable_warnings
+drop tables if exists t1, t2;
+drop view if exists v1;
+drop function if exists f1;
+--enable_warnings
+create table t1 (i int);
+create table t2 (j int);
+--echo #
+--echo # Try to perform DDL on table which is locked through view.
+create view v1 as select * from t2;
+lock tables t1 write, v1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+--echo #
+--echo # Now, try DDL on table which is locked through routine.
+delimiter |;
+create function f1 () returns int
+begin
+ insert into t2 values (1);
+ return 0;
+end|
+delimiter ;|
+create view v1 as select f1() from t1;
+lock tables v1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+drop function f1;
+--echo #
+--echo # Finally, try DDL on table which is locked thanks to trigger.
+create trigger t1_ai after insert on t1 for each row insert into t2 values (1);
+lock tables t1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop trigger t1_ai;
+drop tables t1, t2;
+
+
+--echo #
--echo # Bug#45035 " Altering table under LOCK TABLES results in
--echo # "Error 1213 Deadlock found..."
--echo #
=== modified file 'mysql-test/t/lock_multi.test'
--- a/mysql-test/t/lock_multi.test 2010-01-22 05:53:57 +0000
+++ b/mysql-test/t/lock_multi.test 2010-01-26 17:46:07 +0000
@@ -758,21 +758,17 @@ DROP VIEW IF EXISTS v1;
--echo #
--echo # Test 1: LOCK TABLES v1 WRITE, t1 READ;
--echo #
+--echo # Thanks to the fact that we no longer allow DDL on tables
+--echo # which are locked for write implicitly, the exact scenario
+--echo # in which assert was failing is no longer repeatable.
CREATE TABLE t1 ( f1 integer );
CREATE VIEW v1 AS SELECT f1 FROM t1 ;
---echo # Connection 2
-connect (con2,localhost,root);
LOCK TABLES v1 WRITE, t1 READ;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
FLUSH TABLE t1;
-disconnect con2;
---source include/wait_until_disconnected.inc
-
---echo # Connection 1
-connection default;
-LOCK TABLES t1 WRITE;
-FLUSH TABLE t1; # Assertion happened here
+UNLOCK TABLES;
# Cleanup
DROP TABLE t1;
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2010-01-25 16:47:37 +0000
+++ b/sql/mysql_priv.h 2010-01-26 17:46:07 +0000
@@ -1241,8 +1241,9 @@ bool tdc_open_view(THD *thd, TABLE_LIST
char *cache_key, uint cache_key_length,
MEM_ROOT *mem_root, uint flags);
TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name);
-TABLE *find_write_locked_table(TABLE *list, const char *db,
- const char *table_name);
+TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
+ const char *table_name,
+ bool no_error);
thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table);
void execute_init_command(THD *thd, sys_var_str *init_command_var,
rw_lock_t *var_mutex);
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-01-25 16:47:37 +0000
+++ b/sql/sql_base.cc 2010-01-26 17:46:07 +0000
@@ -131,8 +131,6 @@ static bool tdc_wait_for_old_versions(TH
static bool
has_write_table_with_auto_increment(TABLE_LIST *tables);
-TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
- const char *table_name);
uint cached_open_tables(void)
{
@@ -1005,7 +1003,7 @@ bool close_cached_tables(THD *thd, TABLE
{
/* A check that the table was locked for write is done by the caller. */
TABLE *table= find_table_for_mdl_upgrade(thd->open_tables, table_list->db,
- table_list->table_name);
+ table_list->table_name, TRUE);
/* May return NULL if this table has already been closed via an alias. */
if (! table)
@@ -2386,48 +2384,10 @@ open_table_get_mdl_lock(THD *thd, TABLE_
mdl_request->set_type(MDL_SHARED);
}
- else if (flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL)
- {
- DBUG_ASSERT(!(flags & MYSQL_LOCK_IGNORE_FLUSH));
-
- if (table_list->lock_type >= TL_WRITE_ALLOW_WRITE)
- {
- /*
- When executing LOCK TABLES ... WRITE statement in addition to
- upgradable locks which were pre-acquired in open_tables() we
- want to acquire upgradable locks on tables which are implicitly
- locked for write.
-
- QQ: Should we try to get rid of this thing by disallowing DDL
- on implicitly locked tables?
- */
- mdl_request->set_type(table_list->lock_type > TL_WRITE_ALLOW_READ ?
- MDL_UPGRADABLE_NO_READ_WRITE :
- MDL_UPGRADABLE_NO_WRITE);
- }
- }
else if (flags & MYSQL_LOCK_IGNORE_FLUSH)
- mdl_request->set_type(MDL_SHARED_HIGH_PRIO);
-
- if (mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE ||
- mdl_request->type == MDL_UPGRADABLE_NO_WRITE)
{
- MDL_request *global_request;
-
- if (!(global_request= ot_ctx->get_global_mdl_request(thd)))
- return 1;
- if (! global_request->ticket)
- {
- ot_ctx->add_request(global_request);
- if (thd->mdl_context.try_acquire_global_intention_exclusive_lock(
- global_request))
- return 1;
- if (! global_request->ticket)
- {
- ot_ctx->request_backoff_action(Open_table_context::OT_WAIT_GRL);
- return 1;
- }
- }
+ DBUG_ASSERT(!(flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL));
+ mdl_request->set_type(MDL_SHARED_HIGH_PRIO);
}
ot_ctx->add_request(mdl_request);
@@ -2593,6 +2553,17 @@ bool open_table(THD *thd, TABLE_LIST *ta
{
int distance= ((int) table->reginfo.lock_type -
(int) table_list->lock_type);
+
+ /*
+ If we are performing DDL operation we also should ensure
+ that we will find TABLE instance with upgradable metadata
+ lock,
+ */
+ if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+ table_list->lock_type >= TL_WRITE_ALLOW_WRITE &&
+ ! table->mdl_ticket->is_upgradable_or_exclusive())
+ distance= -1;
+
/*
Find a table that either has the exact lock type requested,
or has the best suitable lock. In case there is no locked
@@ -2626,6 +2597,13 @@ bool open_table(THD *thd, TABLE_LIST *ta
}
if (best_table)
{
+ if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+ table_list->lock_type >= TL_WRITE_ALLOW_WRITE &&
+ ! best_table->mdl_ticket->is_upgradable_or_exclusive())
+ {
+ my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), alias);
+ DBUG_RETURN(TRUE);
+ }
table= best_table;
table->query_id= thd->query_id;
DBUG_PRINT("info",("Using locked table"));
@@ -3014,33 +2992,42 @@ TABLE *find_locked_table(TABLE *list, co
/**
- Find write locked instance of table in the list of open tables,
- emit error if no such instance found.
+ Find instance of TABLE with upgradable or exclusive metadata
+ lock from the list of open tables, emit error if no such table
+ found.
- @param thd List of TABLE objects to be searched
+ @param list List of TABLE objects to be searched
@param db Database name.
@param table_name Name of table.
+ @param no_error Don't emit error if no suitable TABLE
+ instance were found.
- @return Pointer to write-locked TABLE instance, 0 - otherwise.
+ @return Pointer to MDL_SHARED_UPGRADABLE or MDL_EXCLUSIVE
+ TABLE instance, NULL otherwise.
*/
-TABLE *find_write_locked_table(TABLE *list, const char *db, const char *table_name)
+TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
+ const char *table_name,
+ bool no_error)
{
TABLE *tab= find_locked_table(list, db, table_name);
if (!tab)
{
- my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_name);
- return 0;
+ if (!no_error)
+ my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_name);
+ return NULL;
}
else
{
- while (tab->reginfo.lock_type < TL_WRITE_LOW_PRIORITY &&
+ while (tab->mdl_ticket != NULL &&
+ !tab->mdl_ticket->is_upgradable_or_exclusive() &&
(tab= find_locked_table(tab->next, db, table_name)))
continue;
if (!tab)
{
- my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name);
+ if (!no_error)
+ my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name);
return 0;
}
}
@@ -3048,34 +3035,6 @@ TABLE *find_write_locked_table(TABLE *li
}
-/**
- Find instance of TABLE with MDL_SHARED_UPGRADABLE or
- MDL_EXCLUSIVE lock from the list of open tables.
-
- @param list List of TABLE objects to be searched
- @param db Database name.
- @param table_name Name of table.
-
- @return Pointer to MDL_SHARED_UPGRADABLE or MDL_EXCLUSIVE
- TABLE instance, NULL otherwise.
-*/
-
-TABLE *find_table_for_mdl_upgrade(TABLE *list, const char *db,
- const char *table_name)
-{
- TABLE *tab= find_locked_table(list, db, table_name);
-
- while (tab != NULL)
- {
- if (tab->mdl_ticket != NULL &&
- tab->mdl_ticket->is_upgradable_or_exclusive())
- return tab;
- tab= find_locked_table(tab->next, db, table_name);
- }
- return NULL;
-}
-
-
/***********************************************************************
class Locked_tables_list implementation. Declared in sql_class.h
************************************************************************/
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2010-01-21 20:43:03 +0000
+++ b/sql/sql_delete.cc 2010-01-26 17:46:07 +0000
@@ -1186,8 +1186,8 @@ bool mysql_truncate(THD *thd, TABLE_LIST
if (thd->locked_tables_mode)
{
- if (!(table= find_write_locked_table(thd->open_tables, table_list->db,
- table_list->table_name)))
+ if (!(table= find_table_for_mdl_upgrade(thd->open_tables, table_list->db,
+ table_list->table_name, FALSE)))
DBUG_RETURN(TRUE);
mdl_ticket= table->mdl_ticket;
if (wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN))
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-01-25 16:47:37 +0000
+++ b/sql/sql_parse.cc 2010-01-26 17:46:07 +0000
@@ -6556,15 +6556,15 @@ bool reload_acl_and_cache(THD *thd, ulon
if (tables)
{
for (TABLE_LIST *t= tables; t; t= t->next_local)
- if (!find_write_locked_table(thd->open_tables, t->db,
- t->table_name))
+ if (!find_table_for_mdl_upgrade(thd->open_tables, t->db,
+ t->table_name, FALSE))
return 1;
}
else
{
for (TABLE *tab= thd->open_tables; tab; tab= tab->next)
{
- if (tab->reginfo.lock_type < TL_WRITE_ALLOW_WRITE)
+ if (! tab->mdl_ticket->is_upgradable_or_exclusive())
{
my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0),
tab->s->table_name.str);
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2010-01-25 16:47:37 +0000
+++ b/sql/sql_table.cc 2010-01-26 17:46:07 +0000
@@ -1932,8 +1932,8 @@ int mysql_rm_table_part2(THD *thd, TABLE
by parser) it is safe to cache pointer to the TABLE instances
in its elements.
*/
- table->table= find_write_locked_table(thd->open_tables, table->db,
- table->table_name);
+ table->table= find_table_for_mdl_upgrade(thd->open_tables, table->db,
+ table->table_name, FALSE);
if (!table->table)
DBUG_RETURN(1);
table->mdl_request.ticket= table->table->mdl_ticket;
=== modified file 'sql/sql_trigger.cc'
--- a/sql/sql_trigger.cc 2010-01-25 16:47:37 +0000
+++ b/sql/sql_trigger.cc 2010-01-26 17:46:07 +0000
@@ -454,8 +454,10 @@ bool mysql_create_or_drop_trigger(THD *t
if (thd->locked_tables_mode)
{
/* Under LOCK TABLES we must only accept write locked tables. */
- if (!(tables->table= find_write_locked_table(thd->open_tables, tables->db,
- tables->table_name)))
+ if (!(tables->table= find_table_for_mdl_upgrade(thd->open_tables,
+ tables->db,
+ tables->table_name,
+ FALSE)))
goto end;
}
else
Attachment: [text/bzr-bundle] bzr/dlenev@mysql.com-20100126174607-mi0d5y2md0ctnrc3.bundle
| Thread |
|---|
| • bzr commit into mysql-5.6-next-mr branch (dlenev:3060) Bug#46272 | Dmitry Lenev | 26 Jan |
