第二个sessionmysql> 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: 0mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 1 | a |
| 1 | a |
| 2 | a |
+------+------+
6 rows in set (0.00 sec)第一个session: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)mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
为什么在第二个session中执行alter语句add column之后,第一个session再select就什么也select不出来了,这个很方便试验,mysql版本5.1
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: 0mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 1 | a |
| 1 | a |
| 2 | a |
+------+------+
6 rows in set (0.00 sec)第一个session: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)mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
为什么在第二个session中执行alter语句add column之后,第一个session再select就什么也select不出来了,这个很方便试验,mysql版本5.1
sqlserver的可重复读是不能更新表结构的,至于你说mysql的这种情况,可能是mysql更新表结构需要重建表吧
1:CREATE TABLE NEWTABLE(含最新的表结构);
2:INSERT INTO NEWTABLE SELECT * FROM OLDTABLE;
3: DROP TABLE OLDTABLE;
4: RENAME TABLE NEWTABLE TO OLDTABLE;在这个过程中,SESSION1里面读取的表已经不存在了,那么也就啥都读不出来了。