Mysql 一致性读与幻读理解求助

Army_00007

测试如下
1、基本信息

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.22, for Win32 (x86)

Connection id:          3
Current database:       army
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.22-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 2 hours 14 min 16 sec

Threads: 1  Questions: 35  Slow queries: 0  Opens: 72  Flush tables: 1  Open tab
les: 65  Queries per second avg: 0.004
--------------

建表及准备数据

mysql>

session1

CREATE TABLE `auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


mysql> desc auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into auto (name) values ('A');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into auto (name) values ('B');
Query OK, 1 row affected (0.00 sec)

mysql> insert into auto (name) values ('C');
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
+----+------+
3 rows in set (0.00 sec)


mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select  @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)


mysql> set autocommit=0
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

开始测试 先查询数据
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
+----+------+
3 rows in set (0.00 sec)


session 2  insert 数据

mysql> set autocommit=0
    ->
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> select  @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)


mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into auto (name) values ('D');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
|  5 | D    |
+----+------+
4 rows in set (0.00 sec)


session 1

查询
mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
+----+------+
3 rows in set (0.00 sec)

--REPEATABLE-READ  在事务内保证了重复读

mysql> select * from auto for update;
+----+------+
| id | name |
+----+------+
|  2 | A    |
|  3 | B    |
|  4 | C    |
|  5 | D    |
+----+------+
4 rows in set (0.00 sec)

--出现幻读

求解释 select * from auto 与 select * from auto for update 结果不一致的原因

末日战狂

支持一下！

lyhabc

请使用序列化（Serializable） 隔离级别
http://www.it165.net/database/html/201310/4667.html