mysql事务之二：MySQL隔离级别演示

登录mysql：

mysql -u root -p123456

Mysql 版本号

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.31-0ubuntu0.15.10.1 |
+-------------------------+
1 row in set (0.06 sec)

mysql>

查看InnoDB存储引擎 系统级的隔离级别 和 会话级的隔离级别

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

mysql> 

更改会话级的隔离级别

 

mysql> set session tx_isolation='read-uncommitted'; 
Query OK, 0 rows affected (0.08 sec)

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

mysql> 

打开两个mysql连接：

在第一个连接中设置不同隔离级别，然后在两个连接上查看设置结果：

session1：

mysql> set session tx_isolation='read-uncommitted'; 
Query OK, 0 rows affected (0.08 sec)

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

mysql>

session2：

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

mysql> 

更改系统级的隔离级别

 session1：

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

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

mysql> 

session2:

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

mysql> 

关闭SQL语句的自动提交

mysql> set autocommit=off;  
Query OK, 0 rows affected (0.04 sec)

mysql>

查看SQL语句自动提交是否关闭

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.16 sec)

mysql> 

建立实验表

mysql> create table tao (col1 tinyint unsigned, col2 varchar(20), primary key(col1)); 
Query OK, 0 rows affected (0.14 sec)

mysql> show create table tao \G; 
*************************** 1. row ***************************
       Table: tao
Create Table: CREATE TABLE `tao` (
  `col1` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `col2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

1、演示脏读

 session1：

mysql> use duan;
Database changed
mysql> select @@global.tx_isolation, @@tx_isolation; 
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| READ-UNCOMMITTED      | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

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

mysql> create table tao (col1 tinyint unsigned, col2 varchar(20), primary key(col1)); 
Query OK, 0 rows affected (0.05 sec)

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

mysql> insert into tao values(1,'2017');
Query OK, 1 row affected (0.00 sec)

mysql> 

session2：

mysql> use duan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from tao;
+------+------+
| col1 | col2 |
+------+------+
|    1 | 2017 |
+------+------+
1 row in set (0.00 sec)

mysql>

1.1、场景1： 演示脏读（未提交的数据被读到）read-uncommitted

下面所有的session1和session2都是分别是一个独立的事务。

1.2、场景2（不演示）

1、session1查询tao表，session2更新tao表里的一条记录，但未提交。

2、session1在同一个事务内再查询tao表时，发现结果已经改变。

3、session2回滚后，session1再查询时，结果已经变回去了。

1.3、场景3(read-uncommitted)

1、session1查询tao表，session2更新tao表里的一条记录，但未提交。

2、session1在同一个事务内再查询tao表时，发现结果已经改变。（可读）

3、session1同时修改相同的记录行（id=2），修改不成功。（不可修改）

结论：可以看出READ-UNCOMMITTED隔离级别，当两个事务同时进行时，即使事务没有提交，所做的修改也会对事务内的查询做出影响，这种级别显然很不安全。但是在表对某行进行修改时，会对该行加上行共享锁。

 

更改隔离级别为Read Committed后，不存在脏读的问题。

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

mysql> set session tx_isolation='read-committed'; 
Query OK, 0 rows affected (0.00 sec)

mysql>

2、演示不可重复读

（正确应该是：一个会话中的两次查询，返回结果应该一样。）

2.2、场景2：

1、session1开启一个事务，查询tao表；

2、session2开启一个新事务，修改tao表的某条记录；

3、session1重复查询，记录没有变化；

4、session2提交后，session1再重复查询时，记录有变化。

2.3、场景3

1、session1开启一个新的事务修改id=2的记录，成功。

2、session2开启一个新的事务，修改id=2的记录，失败。（行共享锁）

3、session2开启一个新的事务，修改id=3的记录，成功。

总结：READ-COMMITTED事务隔离级别，只有在事务提交后，才会对另一个事务产生影响，并且在对表进行修改时，会对表数据行加上行共享锁。

 

更改隔离级别为Repeatable Read后，不存在不可重复读的问题。

mysql> set global tx_isolation='repeatable-read';  
Query OK, 0 rows affected (0.01 sec)

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

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

mysql> 

3、幻读

3.1、场景1（一个事务被另一个事务已提交的insert数据所影响）

3.2、场景2

1、session1开启一个新事务，并查询一次。

2、session2开启一个新事务，并对id=4进行一次修改，修改成功。

3、session1重复查询一次，结果没有变化。

4、session2提交

5、session1再查询一次，结果没有变化。

6、session1提交事务后，再查询时记录发生变化。

3.3、场景3

1、session1开启一个新的事务修改id=2的记录，成功。

2、session2开启一个新的事务，修改id=2的记录，失败。（行共享锁）

3、session2开启一个新的事务，修改id=3的记录，成功。

总结：REPEATABLE-READ事务隔离级别，当两个事务同时进行时，其中一个事务修改数据对另一个事务不会造成影响，即使修改的事务已经提交也不会对另一个事务造成影响。

　　在事务中对某条记录修改，会对记录加上行共享锁，直到事务结束才会释放。

更改隔离级别为完全串行化 Serializable 后，不存在幻读的问题。

mysql> set global tx_isolation='serializable';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> set session tx_isolation='serializable';  
Query OK, 0 rows affected (0.00 sec)  

在这种情况下，只允许一个事务在执行，其它事务必须等待这个事务执行完后才能执行。没有并发，只是单纯的串行。

4、串行化

4.1、场景1

1、session1开启一个新事务，并作一次查询

2、session2对表进行查询，正常得出结果，可知对user表的查询是可以进行的

3、session2开始事务，并对记录做修改，因为A事务未提交，所以session2的修改处于等待状态，等待session1事务结束，最后超时，说明session1在对tao表做查询操作后，对表加上了共享锁。（实验结果，session2 update成功。不知道是不是mysql版本问题？）

结论：READ-COMMITTED事务隔离级别，只有在事务提交后，才会对另一个事务产生影响，并且在对表进行修改时，会对表数据行加上行共享锁。

 

这块的总结见：《事务之一：数据库事务基础知识回顾》