Skip to content

Commit 42d85e6

Browse files
Dyre Tjeldvolldahlerlend
Dyre Tjeldvoll
authored and
dahlerlend
committed
BUG#27472087: ALTER TABLE HANGS IN "WAITING FOR TABLESPACE METADATA LOCK" STATE
Problem: MDLs are now acquired by SE on implicit tablespaces. But SE does not consider locked tables mode, which is accessible only to server. Solution: Add hook function which allows SE to notfy server about the MDL_tickets used for the old and new implicit tablespace names. Add code in the server which releases the lock on the old name and changes the duration for the lock on the new name, when the transaction commits. Change-Id: I594f3d29db32c497d07e1b2fdd732e8df87ba310 (cherry picked from commit a92ed3ea0f57e8f5be5d6371a71da339fbd7cb30)
1 parent bd8eb01 commit 42d85e6

14 files changed

+872
-502
lines changed

‎mysql-test/r/mdl_tablespace.result

Lines changed: 129 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1039,3 +1039,132 @@ UNLOCK TABLE;
10391039
DROP TABLE t1;
10401040
DROP TABLESPACE s1;
10411041
DROP TABLESPACE s2;
1042+
#
1043+
# Bug#27472087: ALTER TABLE HANGS IN "WAITING FOR TABLESPACE METADATA
1044+
# LOCK" STATE
1045+
#
1046+
# Verify that MDLs taken for implicit table and partition tablespaces
1047+
# handled correctly in lock tables mode.
1048+
#
1049+
CREATE TABLE test1(a INT NOT NULL, b CHAR(2) NOT NULL, PRIMARY KEY(a, b))
1050+
ENGINE=INNODB;
1051+
CREATE TABLE test2(a INT NOT NULL, b CHAR(2) NOT NULL, PRIMARY KEY(a, b))
1052+
ENGINE=INNODB;
1053+
LOCK TABLES test1 WRITE;
1054+
### Locks held ###
1055+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1056+
FROM performance_schema.metadata_locks
1057+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1058+
OBJECT_TYPE = 'tablespace'
1059+
ORDER BY OBJECT_NAME;
1060+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1061+
TABLESPACE test/test1 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1062+
TABLE test1 SHARED_NO_READ_WRITE TRANSACTION GRANTED
1063+
###
1064+
ALTER TABLE test1 RENAME test1_tmp;
1065+
### Locks held ###
1066+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1067+
FROM performance_schema.metadata_locks
1068+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1069+
OBJECT_TYPE = 'tablespace'
1070+
ORDER BY OBJECT_NAME;
1071+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1072+
TABLESPACE test/test1_tmp EXCLUSIVE TRANSACTION GRANTED
1073+
TABLE test1_tmp EXCLUSIVE TRANSACTION GRANTED
1074+
###
1075+
ALTER TABLE test2 RENAME test1;
1076+
### Locks held ###
1077+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1078+
FROM performance_schema.metadata_locks
1079+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1080+
OBJECT_TYPE = 'tablespace'
1081+
ORDER BY OBJECT_NAME;
1082+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1083+
TABLESPACE test/test1_tmp EXCLUSIVE TRANSACTION GRANTED
1084+
TABLE test1_tmp EXCLUSIVE TRANSACTION GRANTED
1085+
###
1086+
UNLOCK TABLES;
1087+
### Locks held ###
1088+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1089+
FROM performance_schema.metadata_locks
1090+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1091+
OBJECT_TYPE = 'tablespace'
1092+
ORDER BY OBJECT_NAME;
1093+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1094+
###
1095+
DROP TABLE test1_tmp;
1096+
DROP TABLE test1;
1097+
# Testing partitioned tables
1098+
CREATE TABLE part1(a INT) PARTITION BY HASH (a) PARTITIONS 10;
1099+
CREATE TABLE part2(a INT) PARTITION BY HASH (a) PARTITIONS 10;
1100+
LOCK TABLES part1 WRITE;
1101+
### Locks held ###
1102+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1103+
FROM performance_schema.metadata_locks
1104+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1105+
OBJECT_TYPE = 'tablespace'
1106+
ORDER BY OBJECT_NAME;
1107+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1108+
TABLE part1 SHARED_NO_READ_WRITE TRANSACTION GRANTED
1109+
TABLESPACE test/part1#P#p0 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1110+
TABLESPACE test/part1#P#p1 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1111+
TABLESPACE test/part1#P#p2 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1112+
TABLESPACE test/part1#P#p3 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1113+
TABLESPACE test/part1#P#p4 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1114+
TABLESPACE test/part1#P#p5 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1115+
TABLESPACE test/part1#P#p6 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1116+
TABLESPACE test/part1#P#p7 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1117+
TABLESPACE test/part1#P#p8 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1118+
TABLESPACE test/part1#P#p9 INTENTION_EXCLUSIVE TRANSACTION GRANTED
1119+
###
1120+
ALTER TABLE part1 RENAME TO part1_tmp;
1121+
### Locks held ###
1122+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1123+
FROM performance_schema.metadata_locks
1124+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1125+
OBJECT_TYPE = 'tablespace'
1126+
ORDER BY OBJECT_NAME;
1127+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1128+
TABLE part1_tmp EXCLUSIVE TRANSACTION GRANTED
1129+
TABLESPACE test/part1_tmp#P#p0 EXCLUSIVE TRANSACTION GRANTED
1130+
TABLESPACE test/part1_tmp#P#p1 EXCLUSIVE TRANSACTION GRANTED
1131+
TABLESPACE test/part1_tmp#P#p2 EXCLUSIVE TRANSACTION GRANTED
1132+
TABLESPACE test/part1_tmp#P#p3 EXCLUSIVE TRANSACTION GRANTED
1133+
TABLESPACE test/part1_tmp#P#p4 EXCLUSIVE TRANSACTION GRANTED
1134+
TABLESPACE test/part1_tmp#P#p5 EXCLUSIVE TRANSACTION GRANTED
1135+
TABLESPACE test/part1_tmp#P#p6 EXCLUSIVE TRANSACTION GRANTED
1136+
TABLESPACE test/part1_tmp#P#p7 EXCLUSIVE TRANSACTION GRANTED
1137+
TABLESPACE test/part1_tmp#P#p8 EXCLUSIVE TRANSACTION GRANTED
1138+
TABLESPACE test/part1_tmp#P#p9 EXCLUSIVE TRANSACTION GRANTED
1139+
###
1140+
ALTER TABLE part2 RENAME TO part1;
1141+
### Locks held ###
1142+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1143+
FROM performance_schema.metadata_locks
1144+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1145+
OBJECT_TYPE = 'tablespace'
1146+
ORDER BY OBJECT_NAME;
1147+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1148+
TABLE part1_tmp EXCLUSIVE TRANSACTION GRANTED
1149+
TABLESPACE test/part1_tmp#P#p0 EXCLUSIVE TRANSACTION GRANTED
1150+
TABLESPACE test/part1_tmp#P#p1 EXCLUSIVE TRANSACTION GRANTED
1151+
TABLESPACE test/part1_tmp#P#p2 EXCLUSIVE TRANSACTION GRANTED
1152+
TABLESPACE test/part1_tmp#P#p3 EXCLUSIVE TRANSACTION GRANTED
1153+
TABLESPACE test/part1_tmp#P#p4 EXCLUSIVE TRANSACTION GRANTED
1154+
TABLESPACE test/part1_tmp#P#p5 EXCLUSIVE TRANSACTION GRANTED
1155+
TABLESPACE test/part1_tmp#P#p6 EXCLUSIVE TRANSACTION GRANTED
1156+
TABLESPACE test/part1_tmp#P#p7 EXCLUSIVE TRANSACTION GRANTED
1157+
TABLESPACE test/part1_tmp#P#p8 EXCLUSIVE TRANSACTION GRANTED
1158+
TABLESPACE test/part1_tmp#P#p9 EXCLUSIVE TRANSACTION GRANTED
1159+
###
1160+
UNLOCK TABLES;
1161+
### Locks held ###
1162+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1163+
FROM performance_schema.metadata_locks
1164+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1165+
OBJECT_TYPE = 'tablespace'
1166+
ORDER BY OBJECT_NAME;
1167+
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1168+
###
1169+
DROP TABLE part1_tmp;
1170+
DROP TABLE part1;

‎mysql-test/t/mdl_tablespace.test

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1497,3 +1497,109 @@ UNLOCK TABLE;
14971497
DROP TABLE t1;
14981498
DROP TABLESPACE s1;
14991499
DROP TABLESPACE s2;
1500+
1501+
1502+
--echo #
1503+
--echo # Bug#27472087: ALTER TABLE HANGS IN "WAITING FOR TABLESPACE METADATA
1504+
--echo # LOCK" STATE
1505+
--echo #
1506+
--echo # Verify that MDLs taken for implicit table and partition tablespaces
1507+
--echo # handled correctly in lock tables mode.
1508+
--echo #
1509+
1510+
CREATE TABLE test1(a INT NOT NULL, b CHAR(2) NOT NULL, PRIMARY KEY(a, b))
1511+
ENGINE=INNODB;
1512+
1513+
CREATE TABLE test2(a INT NOT NULL, b CHAR(2) NOT NULL, PRIMARY KEY(a, b))
1514+
ENGINE=INNODB;
1515+
1516+
LOCK TABLES test1 WRITE;
1517+
--echo ### Locks held ###
1518+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1519+
FROM performance_schema.metadata_locks
1520+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1521+
OBJECT_TYPE = 'tablespace'
1522+
ORDER BY OBJECT_NAME;
1523+
--echo ###
1524+
1525+
ALTER TABLE test1 RENAME test1_tmp;
1526+
1527+
--echo ### Locks held ###
1528+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1529+
FROM performance_schema.metadata_locks
1530+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1531+
OBJECT_TYPE = 'tablespace'
1532+
ORDER BY OBJECT_NAME;
1533+
--echo ###
1534+
1535+
--connect(con1,localhost,root,,)
1536+
ALTER TABLE test2 RENAME test1;
1537+
1538+
--echo ### Locks held ###
1539+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1540+
FROM performance_schema.metadata_locks
1541+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1542+
OBJECT_TYPE = 'tablespace'
1543+
ORDER BY OBJECT_NAME;
1544+
--echo ###
1545+
1546+
--connection default
1547+
UNLOCK TABLES;
1548+
--echo ### Locks held ###
1549+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1550+
FROM performance_schema.metadata_locks
1551+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1552+
OBJECT_TYPE = 'tablespace'
1553+
ORDER BY OBJECT_NAME;
1554+
--echo ###
1555+
1556+
DROP TABLE test1_tmp;
1557+
DROP TABLE test1;
1558+
1559+
--echo # Testing partitioned tables
1560+
CREATE TABLE part1(a INT) PARTITION BY HASH (a) PARTITIONS 10;
1561+
CREATE TABLE part2(a INT) PARTITION BY HASH (a) PARTITIONS 10;
1562+
LOCK TABLES part1 WRITE;
1563+
1564+
--echo ### Locks held ###
1565+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1566+
FROM performance_schema.metadata_locks
1567+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1568+
OBJECT_TYPE = 'tablespace'
1569+
ORDER BY OBJECT_NAME;
1570+
--echo ###
1571+
1572+
ALTER TABLE part1 RENAME TO part1_tmp;
1573+
1574+
--echo ### Locks held ###
1575+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1576+
FROM performance_schema.metadata_locks
1577+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1578+
OBJECT_TYPE = 'tablespace'
1579+
ORDER BY OBJECT_NAME;
1580+
--echo ###
1581+
1582+
--connection con1
1583+
ALTER TABLE part2 RENAME TO part1;
1584+
1585+
--echo ### Locks held ###
1586+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1587+
FROM performance_schema.metadata_locks
1588+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1589+
OBJECT_TYPE = 'tablespace'
1590+
ORDER BY OBJECT_NAME;
1591+
--echo ###
1592+
1593+
--connection default
1594+
UNLOCK TABLES;
1595+
1596+
--echo ### Locks held ###
1597+
SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1598+
FROM performance_schema.metadata_locks
1599+
WHERE OBJECT_TYPE = 'table' AND OBJECT_SCHEMA = 'test' OR
1600+
OBJECT_TYPE = 'tablespace'
1601+
ORDER BY OBJECT_NAME;
1602+
--echo ###
1603+
1604+
DROP TABLE part1_tmp;
1605+
DROP TABLE part1;

‎sql/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -338,6 +338,7 @@ SET(SQL_SHARED_SOURCES
338338
key_spec.cc
339339
keycaches.cc
340340
lock.cc
341+
locked_tables_list.cc
341342
locking_service.cc
342343
log.cc
343344
mdl.cc

‎sql/dd/dd_tablespace.cc

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2015, 2017, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2015, 2018, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License, version 2.0,
@@ -132,7 +132,6 @@ bool fill_table_and_parts_tablespace_names(
132132
for (const dd::Index *idx_obj : table_obj->indexes())
133133
if (get_and_store_tablespace_name(thd, idx_obj, tablespace_set))
134134
return true;
135-
136135
// TODO WL#7156: Add tablespaces used by individual columnns.
137136

138137
return false;

‎sql/dd/dictionary.h

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -229,13 +229,15 @@ bool has_exclusive_table_mdl(THD *thd, const char *schema_name,
229229
@param no_wait Use try_acquire_lock() if no_wait is true,
230230
else use acquire_lock() with
231231
thd->variables.lock_wait_timeout timeout value.
232+
@param ticket ticket for request (optional out parameter)
232233
233234
@retval true Failure, e.g. a lock wait timeout.
234235
@retval false Successful lock acquisition.
235236
*/
236237

237238
bool acquire_exclusive_tablespace_mdl(THD *thd, const char *tablespace_name,
238-
bool no_wait)
239+
bool no_wait,
240+
MDL_ticket **ticket = nullptr)
239241
MY_ATTRIBUTE((warn_unused_result));
240242

241243
/**
@@ -398,6 +400,18 @@ bool reset_tables_and_tablespaces();
398400
*/
399401
template <typename Entity_object_type>
400402
const Object_table &get_dd_table();
403+
404+
/**
405+
Implicit tablespaces are renamed inside SE. But it is necessary to inform the
406+
server layer about the rename, specifically which MDLs have been taken, so
407+
that it can perform the necessary adjustment of MDLs when running in LOCK
408+
TABLES mode.
409+
410+
@param thd thread context
411+
@param src ticket for old name
412+
@param dst ticket for new name
413+
*/
414+
void rename_tablespace_mdl_hook(THD *thd, MDL_ticket *src, MDL_ticket *dst);
401415
} // namespace dd
402416

403417
#endif // DD__DICTIONARY_INCLUDED

‎sql/dd/impl/dictionary_impl.cc

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -425,11 +425,10 @@ bool has_exclusive_table_mdl(THD *thd, const char *schema_name,
425425
}
426426

427427
bool acquire_exclusive_tablespace_mdl(THD *thd, const char *tablespace_name,
428-
bool no_wait) {
429-
// When requesting a tablespace name lock, we leave the schema name empty.
428+
bool no_wait, MDL_ticket **ticket) {
430429
return acquire_mdl(thd, MDL_key::TABLESPACE, "", tablespace_name, no_wait,
431430
thd->variables.lock_wait_timeout, MDL_EXCLUSIVE,
432-
MDL_TRANSACTION, NULL);
431+
MDL_TRANSACTION, ticket);
433432
}
434433

435434
bool acquire_shared_tablespace_mdl(THD *thd, const char *tablespace_name,
@@ -612,4 +611,10 @@ template const Object_table &get_dd_table<dd::Partition>();
612611
template const Object_table &get_dd_table<dd::Table>();
613612
template const Object_table &get_dd_table<dd::Tablespace>();
614613

614+
void rename_tablespace_mdl_hook(THD *thd, MDL_ticket *src, MDL_ticket *dst) {
615+
if (!thd->locked_tables_mode) {
616+
return;
617+
}
618+
thd->locked_tables_list.add_rename_tablespace_mdls(src, dst);
619+
}
615620
} // namespace dd

0 commit comments

Comments
 (0)