2927 Konstantin Osipov 2009-11-20
Backport of:
------------------------------------------------------------
revno: 2476.1116.1
committer: davi@stripped/endora.local
timestamp: Fri 2007-12-14 10:10:19 -0200
message:
DROP TABLE under LOCK TABLES simultaneous to a FLUSH TABLES
WITH READ LOCK (global read lock) can lead to a deadlock.
The solution is to not wait for the global read lock if the
thread is holding any locked tables.
Related to bugs 23713 and 32395. This issues is being fixed
only on 6.0 because it depends on the fix for bug 25858 --
which was fixed only on 6.0.
modified:
mysql-test/r/lock_multi.result
mysql-test/t/lock_multi.test
sql/sql_table.cc
2926 Konstantin Osipov 2009-11-20
Backport of:
------------------------------------------------------------
revno: 2476.784.3
committer: davi@stripped
timestamp: Tue 2007-10-02 21:27:31 -0300
message:
Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
When a client (connection) holds a lock on a table and attempts to
drop (obtain a exclusive lock) on a second table that is already
held by a second client and the second client then attempts to
drop the table that is held by the first client, leads to a
circular wait deadlock. This scenario is very similar to trying to
drop (or rename) a table while holding read locks and are
correctly forbidden.
The solution is to allow a drop table operation to continue only
if the table being dropped is write (exclusively) locked, or if
the table is temporary, or if the client is not holding any
locks. Using this scheme prevents the creation of a circular
chain in which each client is waiting for one table that the
next client in the chain is holding.
This is incompatible change, as can be seen by number of tests
cases that needed to be fixed, but is consistent with respect to
behavior of the different scenarios in which the circular wait
might happen.
@ mysql-test/r/drop.result
Test case result for Bug#25858
@ mysql-test/r/group_by.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table.
@ mysql-test/r/insert_notembedded.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table
@ mysql-test/r/lock.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table.
@ mysql-test/r/lock_multi.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table.
@ mysql-test/r/myisam.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table.
@ mysql-test/r/view.result
Fix test case result wrt drop table under lock tables -- unlock tables
before dropping table.
@ mysql-test/t/drop.test
Add test case for Bug#25858
@ mysql-test/t/group_by.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/insert_notembedded.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/lock.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/lock_multi.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/myisam.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/query_cache_notembedded.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ mysql-test/t/view.test
Fix test case: unlock tables in preparation for a drop table. In some
circumstances, dropping tables while holding locks leads to a deadlock.
@ sql/lock.cc
When trying to obtain a name lock under lock tables, ensure that the table is properly exclusively locked and fail otherwise.
modified:
mysql-test/r/drop.result
mysql-test/r/group_by.result
mysql-test/r/insert_notembedded.result
mysql-test/r/lock.result
mysql-test/r/lock_multi.result
mysql-test/r/myisam.result
mysql-test/r/query_cache_notembedded.result
mysql-test/r/view.result
mysql-test/t/drop.test
mysql-test/t/group_by.test
mysql-test/t/insert_notembedded.test
mysql-test/t/lock.test
mysql-test/t/lock_multi.test
mysql-test/t/myisam.test
mysql-test/t/query_cache_notembedded.test
mysql-test/t/view.test
sql/lock.cc
2925 Konstantin Osipov 2009-11-20
Backport of:
revno: 2476.784.2
committer: davi@stripped
timestamp: Thu 2007-09-27 16:56:27 -0300
message:
Bug#28870 check that table locks are released/reset
The problem is that some mysql_lock_tables error paths are not
resetting the tables lock type back to TL_UNLOCK. If the lock
types are not reset properly, a table might be returned to the
table cache with wrong lock_type.
The proposed fix is to ensure that the tables lock type is always
properly reset when mysql_lock_tables fails. This is a
incompatible change with respect to the process state information.
@ sql/lock.cc
Merge mysql_lock_tables cleanup sequence and the reset_lock_data
function into a single function and take steps to ensure it is
always called for each error exit path. Also remove references
to the redundant THD::locked variable which was almost exclusively
used by this function and the same information is already on proc_info.
@ sql/sql_class.cc
Remove references to the THD::locked variable.
@ sql/sql_class.h
Remove the THD::locked variable.
@ sql/sql_show.cc
Remove references to THD:locked, state_info will now default to proc_info.
modified:
sql/lock.cc
sql/sql_class.cc
sql/sql_class.h
sql/sql_show.cc
=== modified file 'mysql-test/r/drop.result'
--- a/mysql-test/r/drop.result 2009-10-22 18:22:53 +0000
+++ b/mysql-test/r/drop.result 2009-11-20 19:51:12 +0000
@@ -86,6 +86,26 @@ select 1;
1
1
unlock tables;
+drop table if exists t1,t2;
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 read;
+drop table t2;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+drop table t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1,t2;
+create table t1 (i int);
+create table t2 (i int);
+lock tables t1 read;
+lock tables t2 read;
+drop table t1;
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+drop table t1,t2;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1,t2;
End of 5.0 tests
create database mysql_test;
create table mysql_test.t1(f1 int);
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2009-02-26 17:17:06 +0000
+++ b/mysql-test/r/group_by.result 2009-11-20 19:51:12 +0000
@@ -107,8 +107,9 @@ SELECT cid, CONCAT(firstname, ' ', surna
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
-drop table t1,t2;
+drop table t2;
unlock tables;
+drop table t1;
CREATE TABLE t1 (
bug_id mediumint(9) NOT NULL auto_increment,
groupset bigint(20) DEFAULT '0' NOT NULL,
=== modified file 'mysql-test/r/insert_notembedded.result'
--- a/mysql-test/r/insert_notembedded.result 2007-09-06 16:22:34 +0000
+++ b/mysql-test/r/insert_notembedded.result 2009-11-20 19:51:12 +0000
@@ -122,5 +122,6 @@ a b
connection: default
select * from t1;
a b
+unlock tables;
drop table t1;
set low_priority_updates=default;
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result 2009-03-05 14:22:33 +0000
+++ b/mysql-test/r/lock.result 2009-11-20 19:51:12 +0000
@@ -48,6 +48,9 @@ unlock tables;
lock tables t1 write, t1 as t1_alias read;
insert into t1 select index1,nr from t1 as t1_alias;
drop table t1,t2;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
+drop table t1,t2;
create table t1 (c1 int);
create table t2 (c1 int);
create table t3 (c1 int);
@@ -69,6 +72,9 @@ ERROR HY000: Table 't2' was locked with
delete t2 from t1,t2 where t1.a=t2.a;
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
drop table t1,t2;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t2,t1;
End of 4.1 tests.
drop table if exists t1;
create table t1 (a int);
=== modified file 'mysql-test/r/lock_multi.result'
--- a/mysql-test/r/lock_multi.result 2009-07-10 23:12:13 +0000
+++ b/mysql-test/r/lock_multi.result 2009-11-20 20:12:57 +0000
@@ -27,6 +27,7 @@ update t1,t2 set c=a where b=d;
select c from t2;
c
2
+unlock tables;
drop table t1;
drop table t2;
create table t1 (a int);
@@ -209,3 +210,12 @@ select @tlwa < @tlwb;
@tlwa < @tlwb
1
End of 5.1 tests
+drop table if exists t1;
+create table t1 (i int);
+connection: default
+lock tables t1 write;
+connection: flush
+flush tables with read lock;;
+connection: default
+flush tables;
+drop table t1;
=== modified file 'mysql-test/r/myisam.result'
--- a/mysql-test/r/myisam.result 2009-10-27 14:27:27 +0000
+++ b/mysql-test/r/myisam.result 2009-11-20 19:51:12 +0000
@@ -529,6 +529,7 @@ select straight_join * from t1,t2 force
a a b
1 1 1
2 2 1
+unlock tables;
drop table t1,t2;
CREATE TABLE t1 (c1 varchar(250) NOT NULL);
CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1));
@@ -542,6 +543,7 @@ INSERT INTO t2 VALUES ('test000001'), ('
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
t1c1 t2c1
+UNLOCK TABLES;
DROP TABLE t1,t2;
CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM;
Got one of the listed errors
=== modified file 'mysql-test/r/query_cache_notembedded.result'
--- a/mysql-test/r/query_cache_notembedded.result 2009-02-13 19:32:24 +0000
+++ b/mysql-test/r/query_cache_notembedded.result 2009-11-20 19:51:12 +0000
@@ -93,6 +93,7 @@ a
3
SELECT * FROM t1;
a
+UNLOCK TABLES;
drop table t1;
flush query cache;
reset query cache;
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2009-10-19 13:14:43 +0000
+++ b/mysql-test/r/view.result 2009-11-20 19:51:12 +0000
@@ -1104,6 +1104,9 @@ select * from t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
drop view v1;
drop table t1, t2;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1, t2;
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
insert into v1 values(1);
=== modified file 'mysql-test/t/drop.test'
--- a/mysql-test/t/drop.test 2009-10-22 18:22:53 +0000
+++ b/mysql-test/t/drop.test 2009-11-20 19:51:12 +0000
@@ -124,6 +124,39 @@ disconnect addconroot1;
--source include/wait_until_disconnected.inc
connection default;
+#
+# Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
+#
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 read;
+--error ER_TABLE_NOT_LOCKED
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1;
+unlock tables;
+drop table t1,t2;
+connect (addconroot, localhost, root,,);
+connection default;
+create table t1 (i int);
+create table t2 (i int);
+lock tables t1 read;
+connection addconroot;
+lock tables t2 read;
+--error ER_TABLE_NOT_LOCKED
+drop table t1;
+connection default;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1,t2;
+disconnect addconroot;
+connection default;
+unlock tables;
+drop table t1,t2;
+
--echo End of 5.0 tests
#
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2009-02-26 17:17:06 +0000
+++ b/mysql-test/t/group_by.test 2009-11-20 19:51:12 +0000
@@ -120,8 +120,9 @@ SELECT cid, CONCAT(firstname, ' ', surna
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
-drop table t1,t2;
+drop table t2;
unlock tables;
+drop table t1;
#
# Test of group by bug in bugzilla
=== modified file 'mysql-test/t/insert_notembedded.test'
--- a/mysql-test/t/insert_notembedded.test 2007-09-06 16:22:34 +0000
+++ b/mysql-test/t/insert_notembedded.test 2009-11-20 19:51:12 +0000
@@ -185,5 +185,6 @@ select * from t1;
connection default;
disconnect update;
disconnect select;
+unlock tables;
drop table t1;
set low_priority_updates=default;
=== modified file 'mysql-test/t/lock.test'
--- a/mysql-test/t/lock.test 2009-03-05 14:22:33 +0000
+++ b/mysql-test/t/lock.test 2009-11-20 19:51:12 +0000
@@ -58,6 +58,9 @@ insert into t1 select index1,nr from t1;
unlock tables;
lock tables t1 write, t1 as t1_alias read;
insert into t1 select index1,nr from t1 as t1_alias;
+--error ER_TABLE_NOT_LOCKED
+drop table t1,t2;
+unlock tables;
drop table t1,t2;
#
@@ -90,7 +93,10 @@ delete t1 from t1,t2 where t1.a=t2.a;
delete from t2 using t1,t2 where t1.a=t2.a;
--error 1099
delete t2 from t1,t2 where t1.a=t2.a;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
drop table t1,t2;
+unlock tables;
+drop table t2,t1;
--echo End of 4.1 tests.
=== modified file 'mysql-test/t/lock_multi.test'
--- a/mysql-test/t/lock_multi.test 2009-11-10 14:23:55 +0000
+++ b/mysql-test/t/lock_multi.test 2009-11-20 20:12:57 +0000
@@ -76,6 +76,7 @@ update t1,t2 set c=a where b=d;
connection reader;
select c from t2;
connection locker;
+unlock tables;
drop table t1;
drop table t2;
@@ -635,6 +636,41 @@ select @tlwa < @tlwb;
--echo End of 5.1 tests
+#
+# Test that DROP TABLES does not wait for a impending FLUSH TABLES
+# WITH READ LOCK
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (i int);
+connect (flush,localhost,root,,test,,);
+connection default;
+--echo connection: default
+lock tables t1 write;
+connection flush;
+--echo connection: flush
+--send flush tables with read lock;
+connection default;
+--echo connection: default
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Flushing tables";
+--source include/wait_condition.inc
+flush tables;
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Flushing tables";
+--source include/wait_condition.inc
+drop table t1;
+let $wait_condition=
+ select count(*) = 0 from information_schema.processlist
+ where state = "Flushing tables";
+--source include/wait_condition.inc
+connection flush;
+--reap
+connection default;
+disconnect flush;
# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc
-
=== modified file 'mysql-test/t/myisam.test'
--- a/mysql-test/t/myisam.test 2009-10-27 14:27:27 +0000
+++ b/mysql-test/t/myisam.test 2009-11-20 19:51:12 +0000
@@ -503,6 +503,7 @@ insert into t2 values(2,0);
disconnect root;
connection default;
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
+unlock tables;
drop table t1,t2;
#
# Full key.
@@ -520,6 +521,7 @@ disconnect con1;
connection default;
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
+UNLOCK TABLES;
DROP TABLE t1,t2;
# End of 4.0 tests
=== modified file 'mysql-test/t/query_cache_notembedded.test'
--- a/mysql-test/t/query_cache_notembedded.test 2009-02-13 19:32:24 +0000
+++ b/mysql-test/t/query_cache_notembedded.test 2009-11-20 19:51:12 +0000
@@ -99,6 +99,7 @@ connection root2;
SELECT * FROM t1;
connection root;
SELECT * FROM t1;
+UNLOCK TABLES;
drop table t1;
connection default;
disconnect root;
=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test 2009-09-16 06:55:17 +0000
+++ b/mysql-test/t/view.test 2009-11-20 19:51:12 +0000
@@ -1017,6 +1017,9 @@ select * from v1;
-- error ER_TABLE_NOT_LOCKED
select * from t2;
drop view v1;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1, t2;
+unlock tables;
drop table t1, t2;
#
=== modified file 'sql/lock.cc'
--- a/sql/lock.cc 2009-11-20 19:15:50 +0000
+++ b/sql/lock.cc 2009-11-20 19:51:12 +0000
@@ -1028,6 +1028,7 @@ int lock_table_name(THD *thd, TABLE_LIST
char key[MAX_DBKEY_LENGTH];
char *db= table_list->db;
uint key_length;
+ bool found_locked_table= FALSE;
HASH_SEARCH_STATE state;
DBUG_ENTER("lock_table_name");
DBUG_PRINT("enter",("db: %s name: %s", db, table_list->table_name));
@@ -1043,6 +1044,13 @@ int lock_table_name(THD *thd, TABLE_LIST
table = (TABLE*) my_hash_next(&open_cache,(uchar*) key,
key_length, &state))
{
+ if (table->reginfo.lock_type < TL_WRITE)
+ {
+ if (table->in_use == thd)
+ found_locked_table= TRUE;
+ continue;
+ }
+
if (table->in_use == thd)
{
DBUG_PRINT("info", ("Table is in use"));
@@ -1053,6 +1061,17 @@ int lock_table_name(THD *thd, TABLE_LIST
}
}
+ if (thd->locked_tables && thd->locked_tables->table_count &&
+ ! find_temporary_table(thd, table_list->db, table_list->table_name))
+ {
+ if (found_locked_table)
+ my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_list->alias);
+ else
+ my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_list->alias);
+
+ DBUG_RETURN(-1);
+ }
+
if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
DBUG_RETURN(-1);
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-11-10 07:31:33 +0000
+++ b/sql/sql_table.cc 2009-11-20 20:12:57 +0000
@@ -1766,7 +1766,8 @@ void write_bin_log(THD *thd, bool clear_
If a table is in use, we will wait for all users to free the table
before dropping it
- Wait if global_read_lock (FLUSH TABLES WITH READ LOCK) is set.
+ Wait if global_read_lock (FLUSH TABLES WITH READ LOCK) is set, but
+ not if under LOCK TABLES.
RETURN
FALSE OK. In this case ok packet is sent to user
@@ -1777,7 +1778,7 @@ void write_bin_log(THD *thd, bool clear_
bool mysql_rm_table(THD *thd,TABLE_LIST *tables, my_bool if_exists,
my_bool drop_temporary)
{
- bool error= FALSE, need_start_waiters= FALSE;
+ bool error= FALSE, need_start_waiting= FALSE;
Drop_table_error_handler err_handler(thd->get_internal_handler());
DBUG_ENTER("mysql_rm_table");
@@ -1785,13 +1786,9 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
if (!drop_temporary)
{
- if ((error= wait_if_global_read_lock(thd, 0, 1)))
- {
- my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), tables->table_name);
+ if (!thd->locked_tables &&
+ !(need_start_waiting= !wait_if_global_read_lock(thd, 0, 1)))
DBUG_RETURN(TRUE);
- }
- else
- need_start_waiters= TRUE;
}
/*
@@ -1804,7 +1801,7 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
thd->pop_internal_handler();
- if (need_start_waiters)
+ if (need_start_waiting)
start_waiting_global_read_lock(thd);
if (error)
Attachment: [text/bzr-bundle] bzr/kostja@sun.com-20091120201257-yy7t8dgotsotg0so.bundle
| Thread |
|---|
| • bzr push into mysql-5.6-next-mr branch (kostja:2925 to 2927) | Konstantin Osipov | 20 Nov |
