ALTER TABLE can block SELECT statements in certain conditions

[Ovais Tariq]

The documentation says that ALTER TABLE still allows other sessions to be able to read from the table whose ALTER is in progress, and that the table is only locked for writes. However, in certain conditions the ALTER TABLE blocks other select statements from executing. This has to do with how meta-data locking is implemented.

This can be repeated as follows:

- Create a table and insert some data
create table t1(i int(11) not null auto_increment primary key, c char(32) not null default 'dummy_text') engine=innodb;
insert into t1(i) select null from t1; <-- execute this multiple times

- Now execute the following statements in different sessions as specified
session1 > start transaction;
session 1 > select * from t1 limit 10;
+----+------------+
| i | c |
+----+------------+
| 1 | dummy_text |
| 2 | dummy_text |
| 3 | dummy_text |
| 4 | dummy_text |
| 6 | dummy_text |
| 7 | dummy_text |
| 8 | dummy_text |
| 9 | dummy_text |
| 13 | dummy_text |
| 14 | dummy_text |
+----+------------+

session 2 > alter table t1 add key k1(c); <-- this blocks
Query OK, 0 rows affected (1 min 47.40 sec)
Records: 0 Duplicates: 0 Warnings: 0

session 3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
| 4 | msandbox | localhost | test | Sleep | 49 | | NULL |
| 5 | msandbox | localhost | test | Query | 45 | Waiting for table metadata lock | alter table t1 add key k1(c) |
| 6 | msandbox | localhost | test | Sleep | 9 | | NULL |
| 7 | msandbox | localhost | test | Query | 0 | NULL | show processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
4 rows in set (0.00 sec)
session 3 > start transaction;
session 3 > select * from t1 limit 5; <-- this blocks

session 4 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
| 4 | msandbox | localhost | test | Sleep | 85 | | NULL |
| 5 | msandbox | localhost | test | Query | 81 | Waiting for table metadata lock | alter table t1 add key k1(c) |
| 7 | msandbox | localhost | test | Query | 30 | Waiting for table metadata lock | select * from t1 limit 5 |
| 8 | msandbox | localhost | test | Query | 0 | NULL | show processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------+
4 rows in set (0.00 sec)
session 4 > start transaction;
session 4 > select * from t1 limit 5; <-- this blocks

This behaviour of selects waiting on meta-data locks, specifically happens when a new session opens the table for reading and the table is not already in the table cache

[Raghavendra D Prabhu (raghavendra-prabhu)]

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.

[Stewart Smith (stewart)]

likely 5.5 specific solution due to how metadata locking is handled. We'll probably wait for the MySQL fix for this one though.

[Laurynas Biveinis (laurynas-biveinis)]

This has been handled as a documentation bug by the upstream, and it's unlikely we'll do any code changes. Please re-open if you see the need for a code patch.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1950