mysql 提交读 可重复读

环境

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.31-log |
+------------+
1 row in set (0.00 sec)

提交读

查看当前数据库隔离级别

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

查看测试表结构

mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` varchar(30) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

解决脏读问题，但是会出现不可重复读

#session 1                                          session 2

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

mysql> select * from t;                     
Empty set (0.00 sec)                        
                                            mysql> insert into t(comment,time) values ('r1',now());
                                            Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t;
+----+---------+---------------------+
| id | comment | time                |
+----+---------+---------------------+
|  1 | r1      | 2016-09-23 15:56:17 |
+----+---------+---------------------+
1 row in set (0.00 sec)

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

可重复读

设置全局的隔离级别为REPEATABLE-READ

mysql> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

清空测试数据

mysql> truncate table t;
Query OK, 0 rows affected (0.03 sec)

重复执行上面的实验，可见在可重复读的隔离级别下，不可重复读得到了解决

#session 1                                          session 2

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

mysql> select * from t;                     
Empty set (0.00 sec)                        
                                            mysql> insert into t(comment,time) values ('r2',now());
                                            Query OK, 1 row affected (0.00 sec)

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

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


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

清空测试数据

mysql> truncate table t;
Query OK, 0 rows affected (0.03 sec)

进一步测试，幻读现象出现

#session 1                                          session 2

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

mysql> select * from t;                     
Empty set (0.00 sec)                        
                                            mysql> insert into t(comment,time) values ('r3',now());
                                            Query OK, 1 row affected (0.00 sec)

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

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

mysql> update t set comment='c3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+----+---------+---------------------+
| id | comment | time                |
+----+---------+---------------------+
|  1 | c3      | 2016-09-23 16:53:28 |
+----+---------+---------------------+
1 row in set (0.00 sec)


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