如何做到Alter多个表的原子性

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿，请发送稿件至：[e-mail]。

有奖征稿说明：知识无价，劳动有偿，ACMUG特约撰稿人有奖回报计划（修订版）

作者简介：王竹峰

毕业于华中科技大学数据库研究所，资深MySQL数据库专家，擅长数据库的开发、管理和维护，先后就职于上海达梦数据库、人人网，现在是去哪儿网数据库专家，负责MySQL数据库运维及相关开发工作。

曾经我问过一个同学这样的问题：“如何原子性的去改多个表的表名？”，那位同学很快就回答道：可以用Alter Table Rename来改啊。

针对这样的回答，我只是希望这位同学多想想看是不是这么一回事了。

问题是如何实现“原子性”的修改多个表名，首先Alter Table语句只能修改一个表，这个语句方便的是，可以修改某一个表所有的属性。但如何修改多个表？显然没办法做到。

可能这个时候，这位同学就想通了，马上说可以通过Rename 语句来改啊，这个语句可以同时修改多个表的表名，并且还是原子性的，没错，这个是可以的，这一条语句下去，MySQL会把所有的表加上MDL锁，依次把所有的表名都改成想要的，然后就搞定了。

问题解决了?

其实不然，如果我再加一个需求，如何做到：

1. 有两个表T1和T2，T1一直有数据写入。

2. T2表一直同步T1的数据，并且增量补获T1的Binlog，并且将Binlog转换为对应的DML语句写入到T2表中。

3. 等到T2追T1的Binlog剩下很少一部分时，让T1和T2表名互换，从而实现T1表加列的需求。

上面的需求是一个实际的应用需求，是通过Binlog增量方式来实现MySQL在线改表的需求，目前我已经实现了这个工具，叫Inception Schema Change。不过有一个问题不知道大家注意到没有，那就是在解决第3步时，如何保证在表名互换时，T1没有新的Binlog产生，如果有，那这部分写入就丢失了。

这一总结，就又回到了我们的主题，就是说如何保证改表的同时，又能与另一个DML语句保持原子性的问题。

可能有同学很快想到了，可以先给表上个锁，然后再去RENAME T1 to T_old, T2 to T1。这样不就解决了。行，那我们就来做个实验：

mysql> lock table T1, T2 write;

Query OK, 0 rows affected (0.00 sec)

# then we rename them:

mysql> RENAME T1 to T_old, T2 to T1;

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

这个时候，可能就有点困惑了，竟然不行？那我们再做一个其它Alter Table Rename ...的实验：

mysql> lock table T1, T2 write;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table T1 add a int;

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

# but we can not operate the tables which not been locked:

mysql> create table up(sno int);

ERROR 1100 (HY000): Table 'up' was not locked with LOCK TABLES this is ok.

上面这个实验说明，我们上锁之后，就可以对这个表操作了，只是没有上锁的就不行，这是我们所熟知的情况。

那这就说明，Rename to语句是被限制了的，确实行不通，那咱看看代码，下面是精简之后的代码，并且是Rename Table语句处理函数的开始位置：

bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list, bool silent)

{

  /*

    Avoid problems with a rename on a table that we have locked or

    if the user is trying to to do this in a transcation context

  */

  if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction())

  {

    my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,

               ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));

    DBUG_RETURN(1);

  } 

  /* other code lines ... */

}/*mysql_rename_tables*/

这下了然了，看代码解决一切问题，从上面代码注释中可以看到，为了防止出现问题，在一个事务中，不能去做Rename Table的操作。当然不管改几个表，只要前面有上锁操作，都是不行的。而Alter Table Rename...语句就不存在这样的限制。

那这个问题如何解决呢？其实有两种办法：

1. 这种办法也是最好的办法，那就是联系Oracle的人，问问这是怎么回事，能不能优化，改代码来处理，这个工作我们已经去做了，等待消息中。链接可查看：http://forums.mysql.com/read.php?20,652015,652015#msg-652015

2. 这种办法，是一种妥协的办法，就是之前在ACMUG公众号中军伟写的文章《gh-ost实践》中提到的ghost所使用的方式，就是先让处理增量的会话trx1对表T1和T2上锁，然后开始另一个会话trx2去做`RENAME T1 to T_old, T2 to T1`操作，此时这个操作会被阻塞，然后所有的业务对T1表的请求都会被阻塞，最后处理完增量后，trx1放锁，此时相应的trx2就会Rename成功了。具体的实现细节，可以关注公众号来获取。

我们只期待第一种方法能被解决，官方也已经回应，期待美好的明天。

注：ACMUG收录技术文章版权属于原作者本人所有。如有疑问，请联系作者。

看完转发，手留余香。关注我们，一起进步。

关注ACMUG公众号，参与社区活动，交流开源技术，分享学习心得，一起共同进步。