[MySQL FAQ]系列 -- MySQL执行ALTER TABLE导致读不一致性问题

转发一下某网友(Howard)的来信，先讨论一下，稍后再给结论

你好：

我现在有个关于mysql的问题一点思路都没有，想问下你，麻烦你有时间了看下。

另外你一般有没有一个活跃的论坛或者小组之类的？

mysql的版本是Server
version: 5.1.51-community-log MySQL Community Server (GPL)

将mysql的tx_isolation设置为repeatable-read

mysql> show global variables
like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show create table test;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table
|

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test`
(

`id` int(11) DEFAULT
NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_bin |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

然后同时开启了两个事务，其中事务1先执行select * from test;然后事务2执行alter table test
add column name char(5) default 'a';，事务再执行select * from test;的时候就返回为空了。

第一个transaction：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 1 |
| 1 |
| 2 |
+------+
6 rows in set (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

第二个transaction

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 1 |
| 1 |
| 2 |
+------+
6 rows in set (0.00 sec)

mysql> alter table test add column name char(5) default 'a';
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 1 | a |
| 1 | a |
| 2 | a |
+------+------+
6 rows in set (0.00 sec)