楼主我可以重现你的场景,确实是跟事务隔离级别和是否启用事务[自动提交(autocommit)的参数设置]有关: 顺序如下: connection1: set autocommit=0; mysql> desc tablea; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)connection 2: set autocommit=0;connection 1: mysql> insert into tablea values(1,'a'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tablea; +------+------+ | id | name | +------+------+ | 1 | a | +------+------+ 1 row in set (0.00 sec) connection 2: mysql> select * from tablea; Empty set (0.00 sec) 然后关闭connection 2,再开一个窗口就可以查到。其实不用关闭连接,直接在connection 2中,commit一下就可以查到了: mysql> commit; Query OK, 0 rows affected (0.00 sec)mysql> select * from tablea; +------+------+ | id | name | +------+------+ | 1 | a | +------+------+ 1 row in set (0.00 sec)对了我的是默认隔离级别。表是innodb类型刚学mysql,版主见笑了。
检查你的级别,然后对照一下这个隔离级别的说明。mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)mysql>
READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLEMySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
顺序如下:
connection1:
set autocommit=0;
mysql> desc tablea;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)connection 2:
set autocommit=0;connection 1:
mysql> insert into tablea values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tablea;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
connection 2:
mysql> select * from tablea;
Empty set (0.00 sec)
然后关闭connection 2,再开一个窗口就可以查到。其实不用关闭连接,直接在connection 2中,commit一下就可以查到了:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)mysql> select * from tablea;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)对了我的是默认隔离级别。表是innodb类型刚学mysql,版主见笑了。
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)mysql>
在持续读中,有一个与之前隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的,请参阅15.2.10.4节,“持续非锁定读”。 所以commit之后才能看到多谢版主。