Comment 1 for bug 1081432

Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

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
#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

------------------------

[Full trace here: https://gist.github.com/dbd134e39cd52c4e9982 ]

It looks to be so because:

from: mysql_alter_table

  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.