It looks like it is because, the ALTER TABLE acquires an exclusive metadata lock (MDL_EXCLUSIVE), so it waits for SELECT in session 1 to finish, and in turn all selects after that wait on the ALTER (because of the wait_while_table_is_used used by ALTER).
This is how the trace looks:
-----------------
#0 0x00000037e3c0b7bb in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1 0x0000000000631f26 in inline_mysql_cond_timedwait (this=0x3a16560, thd=0x3a16450, abs_timeout=0x7fd8343b2100, set_status_on_timeout=false, wait_state_name=0x0) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/P
ercona-Server-5.5.27-rel28.1/include/mysql/psi/mysql_thread.h:1009
#2 MDL_wait::timed_wait (this=0x3a16560, thd=0x3a16450, abs_timeout=0x7fd8343b2100, set_status_on_timeout=false, wait_state_name=0x0) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27-rel28.1/
sql/mdl.cc:1159
#3 0x00000000006332c6 in MDL_context::acquire_lock (this=0x3a16560, mdl_request=0x7fd8343b2170, lock_wait_timeout=<value optimized out>) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27-rel28
.1/sql/mdl.cc:1996
#4 0x0000000000633684 in MDL_context::upgrade_shared_lock_to_exclusive (this=0x3a16560, mdl_ticket=0x7fd7f802d9b0, lock_wait_timeout=31536000) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27
-rel28.1/sql/mdl.cc:2175
#5 0x0000000000554317 in wait_while_table_is_used (thd=0x3a16450, table=0x7fd7e8003b70, function=HA_EXTRA_PREPARE_FOR_RENAME) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27-rel28.1/sql/sql_
base.cc:2307
#6 0x00000000005ed85d in mysql_alter_table (thd=<value optimized out>, new_db=<value optimized out>, new_name=0x7fd7f8029e98 "t1", create_info=0x7fd8343b4610, table_list=0x7fd7f802ae00, alter_info=0x7fd8343b46f0, order_num=0, order=0x0, ignore=false) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27-rel28.1/sql/sql_table.cc:6895
#7 0x000000000078d52f in Alter_table_statement::execute (this=<value optimized out>, thd=0x3a16450) at /usr/src/debug/Percona-Server-5.5.27-rel28.1/Percona-Server-5.5.27-rel28.1/sql/sql_alter.cc:106
thd_proc_info(thd, "setup");
if (!(alter_info->flags & ~(ALTER_RENAME | ALTER_KEYS_ONOFF)) &&
!table->s->tmp_table) // no need to touch frm
{
switch (alter_info->keys_onoff) {
case LEAVE_AS_IS:
break;
case ENABLE:
if (wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN))
However,
HA_EXTRA_FORCE_REOPEN=21, /* Datafile have changed on disk */
indicates that datafile has changed on the disk but SELECT doesn't do that.
May be this is an undocumented corner case or a bug.
Confirmed.
It looks like it is because, the ALTER TABLE acquires an exclusive metadata lock (MDL_EXCLUSIVE), so it waits for SELECT in session 1 to finish, and in turn all selects after that wait on the ALTER (because of the wait_while_ table_is_ used used by ALTER).
This is how the trace looks:
-----------------
#0 0x00000037e3c0b7bb in pthread_ cond_timedwait@ @GLIBC_ 2.3.2 () from /lib64/ libpthread. so.0 mysql_cond_ timedwait (this=0x3a16560, thd=0x3a16450, abs_timeout= 0x7fd8343b2100, set_status_ on_timeout= false, wait_state_ name=0x0) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ P Server- 5.5.27- rel28.1/ include/ mysql/psi/ mysql_thread. h:1009 :timed_ wait (this=0x3a16560, thd=0x3a16450, abs_timeout= 0x7fd8343b2100, set_status_ on_timeout= false, wait_state_ name=0x0) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27- rel28.1/ :acquire_ lock (this=0x3a16560, mdl_request= 0x7fd8343b2170, lock_wait_ timeout= <value optimized out>) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27- rel28 :upgrade_ shared_ lock_to_ exclusive (this=0x3a16560, mdl_ticket= 0x7fd7f802d9b0, lock_wait_ timeout= 31536000) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27 1/sql/mdl. cc:2175 table_is_ used (thd=0x3a16450, table=0x7fd7e80 03b70, function= HA_EXTRA_ PREPARE_ FOR_RENAME) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27- rel28.1/ sql/sql_ 0x7fd7f8029e98 "t1", create_ info=0x7fd8343b 4610, table_list= 0x7fd7f802ae00, alter_info= 0x7fd8343b46f0, order_num=0, order=0x0, ignore=false) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27- rel28.1/ sql/sql_ table.cc: 6895 statement: :execute (this=<value optimized out>, thd=0x3a16450) at /usr/src/ debug/Percona- Server- 5.5.27- rel28.1/ Percona- Server- 5.5.27- rel28.1/ sql/sql_ alter.cc: 106
#1 0x0000000000631f26 in inline_
ercona-
#2 MDL_wait:
sql/mdl.cc:1159
#3 0x00000000006332c6 in MDL_context:
.1/sql/mdl.cc:1996
#4 0x0000000000633684 in MDL_context:
-rel28.
#5 0x0000000000554317 in wait_while_
base.cc:2307
#6 0x00000000005ed85d in mysql_alter_table (thd=<value optimized out>, new_db=<value optimized out>, new_name=
#7 0x000000000078d52f in Alter_table_
------- ------- ------- ---
[Full trace here: https:/ /gist.github. com/dbd134e39cd 52c4e9982 ]
It looks to be so because:
from: mysql_alter_table
thd_proc_ info(thd, "setup"); info->flags & ~(ALTER_RENAME | ALTER_KEYS_ONOFF)) && table-> s->tmp_ table) // no need to touch frm info->keys_ onoff) { table_is_ used(thd, table, HA_EXTRA_ FORCE_REOPEN) )
if (!(alter_
!
{
switch (alter_
case LEAVE_AS_IS:
break;
case ENABLE:
if (wait_while_
However, FORCE_REOPEN= 21, /* Datafile have changed on disk */
HA_EXTRA_
indicates that datafile has changed on the disk but SELECT doesn't do that.
May be this is an undocumented corner case or a bug.