mysql 5.1中DDL语句对Transaction的影响

这个问题是从mysqlperformance上发现的，挺有意思，分享一下。DDL语句对transaction的影响。下面贴出自己的实验过程：
mysql-5.5
session1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

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

session2:
mysql> alter table test add column pas varchar(20) ;
(下面的结果是session1 commit之后的，session1没有commit，session2一直被阻塞)
Query OK, 4 rows affected (18.55 sec)
Records: 4  Duplicates: 0  Warnings: 0
---------------------------------------------------------------------------------
下面来测试mysql5.1
mysql5.1

session1：
root@localhost test 10:17:18 >start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost test 10:17:38 >select * from test;
+----+------+
| id | name |
+----+------+
|  1 | xx   |
|  2 | xx   |
|  3 | xx   |
|  4 | xx   |
+----+------+
4 rows in set (0.00 sec)

session2：
root@localhost test 10:17:29 >alter table test add column pas varchar(20);
(session2并没有被阻塞，结果瞬间出来)
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

此时在session1里面再次执行命令：
session1：
root@localhost test 10:17:52 >select * from test;
Empty set (0.00 sec)
可以看出，此时得到的结果集为空

注意上面的测试是在隔离级别为 REPEATABLE-READ ,将隔离级别设置为READ-COMMITTED，再次进行测试
mysql5.1
session1：
root@localhost test 10:49:46 >set tx_isolation = 'read-committed';
Query OK, 0 rows affected (0.00 sec)
root@localhost test 10:50:13 >start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost test 10:50:19 >select * from test;
+----+------+------+
| id | name | pas  |
+----+------+------+
|  1 | xx   | NULL |
|  2 | xx   | NULL |
|  3 | xx   | NULL |
|  4 | xx   | NULL |
+----+------+------+
4 rows in set (0.00 sec)

session2：
root@localhost test 10:44:07 >alter table test drop column pas;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

session2不被阻塞是预期的，那么如果此时在session1再次查询呢？

session1:
root@localhost test 10:50:27 >select * from test;
+----+------+
| id | name |
+----+------+
|  1 | xx   |
|  2 | xx   |
|  3 | xx   |
|  4 | xx   |
+----+------+
4 rows in set (0.00 sec)

可以看出，session1此时能得到最新的结果，因为此时的隔离级别为READ-COMMITTED，读提交，自然能读到最新的数据。
现在来解释这些现象，在mysql 5.1里面，开启一个事务，如果对表的操作仅有读操作，那么不会对表加上meta lock（也就是锁住表的结构），所以在前面的测试中可以看到session1中开启一个事务读表，session2中可以修改表结构。如果事务里面对表有写操作，那么结果肯定是session2的DDL操作肯定会被阻塞，我已经测试过了。

那么在REPEATABLE-READ隔离级别下为什么同一个事务里面读到结果不一样？一个事务里面（这里是session1）只有读操作，那么这个表就可以在其他事务中被执行DDL操作，而当session1里面再次去读表时，发现这个表结构已经被修改了，于是只好去读副本。而mysql ALTER TABLE的操作原理是：创建一个临时表、将数据插入临时表、删掉原表、重命名临时表。那么由于REPEATABLE-READ的特性，新表里面的数据肯定是读不到的（因为事务开启时间早于新表的创建时间），所以说读到的数据也就是empty set。而你可能会问为什么不去读那个原表的数据？这是因为DDL语句是不可rollback，所以之前删除原表的操作也就不会创建回滚数据。不过幸好是这个问题在mysql 5.5里面已经修正了，因此我在用mysql 5.5做测试时也就看不到mysql 5.1里面出现的现象。

对于这个问题最需要注意的地方就是在利用mysqldump备份的时候，虽然你可能使用了参数--single-transaction，结果很有可能不是你所预期的（导出来的数据是一张空表，empty set），而另一篇文章则是因为flush tables with read lock可能会导致备份时间增长，所以要小心了。