mysql可提交读_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)