数据库隔离机制在mysql下的测试 ---- 20161022

一、数据库隔离机制简介。

关于数据库隔离机制的概念，可以参考看另一篇文章http://simon-9527.iteye.com/blog/2311351。

首先了解什么是事务

1、事务概念：事务是包含了一组有序的数据库操作命令的序列，它是数据库并发操作的最小控制单位。

2、事务特性：原子性：事务包含的数据库操作命令要么都执行，要么都不执行。

一致性：当事务完成时，数据库处于稳定而一致的状态。即事务执行后，数据库数据要符合规定，而且所有数据查询       的结果是一致的。

隔离性：多个事务同时执行时，他们之间是互不干扰的。

永久性：一旦事务提交成功，它引发的变化也就永久保存了下来，硬件与应用程序发生错误也不能改变。

3、事务状态：

4、事务操作命令

         

 

在并发执行事务时会发生什么问题呢？

 

1、丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖（事务A和B并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了）。

 

2、脏读：一个事务读到另一个事务未提交的更新数据（事务A和B并发执行，B事务执行更新后，A事务查询B事务没有提交的数据，B事务回滚，则A事务得到的数据不是数据库中的真实数据。也就是脏数据，即和数据库中不一致的数据）。

3、不可重复读：一个事务读到另一个事务已提交的更新数据（事务A和B事务并发执行，A事务查询数据，然后B事务更新该数据，A再次查询该数据时，发现该数据变化了）。

4、覆盖更新：这是不可重复读中的特例，一个事务覆盖另一个事务已提交的更新数据（即A事务更新数据，然后B事务更新该数据，A事务查询发现自己更新的数据变了）。

 

5、虚读（幻读）：一个事务读到另一个事务已提交的新插入的数据（A和B事务并发执行，A事务查询数据，B事务插入或者删除数据，A事务再次查询发现结果集中有以前没有的数据或者以前有的数据消失了）。

数据库系统提供了四种事务隔离级别供用户选择

1、Serializable（串行化）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新（事务执行的时候不允许别的事务并发执行。事务串行化执行，事务只能一个接着一个地执行，而不能并发执行。）。

2、Repeatable Read（可重复读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新。

3、Read Commited（读已提交数据）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新。

4、Read Uncommitted（读未提交数据）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新。

 

 

 

	丢失更新	脏读	非重复读	覆盖更新	幻像读
未提交读	Y	Y	Y	Y	Y
已提交读	N	N	Y	Y	Y
可重复读	N	N	N	N	Y
串行化	N	N	N	N	N

MYSQL默认是什么隔离级别？

    Repeatable read

 

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

 

 

如何查看/修改隔离级别？

    查看：select @@GLOBAL.tx_isolation, @@session.tx_isolation;
    修改：set session transaction isolation level repeatable read;  #repeatable read 可修改成 serializable等

 

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

 

 

二、在mysql中的测试。

1. 建立测试表

 

 CREATE TABLE `test_isolation` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 2. 直接在本地打开两个mysql命令窗口，其实就应该是两个连接，连个不同的事务，进行测试。

 

第一个窗口直接插入一条数据:

 

mysql> insert into test_isolation (name) values ('simon');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
+----+-------+
1 row in set (0.00 sec)

 

 

第二个窗口查询结果：

 

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
+----+-------+
1 row in set (0.00 sec)

 

 

发现能够查出结果。这并不是说明数据库的隔离机制没有起作用，而是由于mySql数据库的自动提交特性导致的。当采用默认的自动提交时，每一个sql都是一个独立的事务，执行完后直接提交了，这样你在其他的连接或者说事务中也就可以看到提交的结果了。

 

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
+----+-------+
1 row in set (0.00 sec)

 3. 禁止mysql自动提交功能。

（1）可以直接自己手动建立transaction， 通过begin，或者start transaction。

 

 

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

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

 

 

（2）关掉自动提交功能，session级别的可以直接设置，数据库级别的需要设置配置文件，不过低版本好像不支持，具体可以自己搜索相关的文章。

 

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

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

 

 

4. 这时再进行测试

 第一个窗口：

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

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
| 52 | simon |
+----+-------+
2 rows in set (0.00 sec)

 

 

第二个窗口，这时是看不到第一个事务没有提交的数据的。

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
+----+-------+
1 row in set (0.00 sec)

 

在第一个窗口提交：

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

 

这时第二个窗口的结果，已经可以看到数据。

mysql> select * from test_isolation;
+----+-------+
| id | name  |
+----+-------+
| 51 | simon |
+----+-------+
1 row in set (0.00 sec)

 

其他的update，delete的测试过程也类似。也可以设置session的隔离级别后再进行测试，以熟悉不同隔离级别的表现差异。