事务隔离级别测试

jimmy 2019年02月25日 664次浏览

mysql 事务测试

事务的四种隔离级别

  1. Read Uncommited
    可以读取未提交的记录,一个事务还没进行commit,修改的数据就被其他事务读取到了是非常不能接受的,所以我们一般不使用这个隔离级别。
  2. Read Commited
    读已提交级别,就是一个事务commit以后,所进行的修改可以被其他事务读取到。 Read Commited解决了幻读问题(第一次读取的数据被其他事务update了,本次再读取同一条记录,结果不一样),通过对读取到的每一条数据加锁解决。
  3. Repeatable Read
    重复读级别,可重复读会对读取到的记录加锁,这样每次读取的数据是一样的,主要解决了其他事务进行insert后,自己读取到的数据量变多(通过加间隙锁解决)。
  4. Serializable
    序列化级别,所有的操作都是按照顺序执行,并发效果很差,一般也不使用。

经常使用的就是Read Commited(RC) 和 Repeatable Read (RR)

事务的控制语句(mysql)

BEGIN或START TRANSACTION 显式地开启一个事务。

COMMIT 提交事务,并使已对数据库进行的所有修改成为永久性的,会写入到磁盘;

ROLLBACK 回滚会结束用户的事务,并撤销之前执行的但是未提交的修改。

SAVEPOINT xxx SAVEPOINT允许在事务中创建一个保存点,这样后面可以回滚到这个回滚点,类似于快照的概念。

RELEASE SAVEPOINT xxx 删除一个事务的保存点。

ROLLBACK TO xxx 把事务回滚到回滚点。

SET TRANSACTION ISOLATION LEVEL xxx 。xxx为后面的四个隔离级别之一。 用来设置事务的隔离级别。必须是InnoDB存储引擎,提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

设置事务隔离级别

在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别:

– READ-UNCOMMITTED
– READ-COMMITTED
– REPEATABLE-READ
– SERIALIZABLE

•   例如:
[mysqld]
transaction-isolation = READ-COMMITTED

也可以在会话中是用 SET TRANSACTION ISOLATION LEVEL xxx 设置。

事务隔离级别的作用范围分为两种: 
当前会话
SET SESSION TRANSACTION ISOLATION LEVEL xxx;
全局会话 : 
 SET GLOBAL TRANSACTION ISOLATION LEVEL xxx;

查看事务的隔离级别

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

测试数据

为了方便测试,在命令行登录,不要用IDE,登录命令

mysql -uroot -h10.10.51.212 -P13306 -p --ssl-mode=DISABLED

根据提示,输入登录密码。ssl-mode参数主要是去掉加密,便于wireshark进行抓捕分析。

mysql> desc users;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | 0       |       |
| name        | varchar(50) | YES  |     | NULL    |       |
| create_time | datetime(6) | YES  |     | NULL    |       |
| update_time | datetime(4) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

autocommit设置:

set autocommit=0;
当前session禁用自动提交事物,自此句执行以后,每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。否则每一条语句就会自动启动一个事务。 设置为0后,我们就可以自己使用 start transaction 启动一个新事务。

一些测试语句

 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

  
 insert into users values (1,\'zhangsan\',now(),now());
 insert into users values (3,\'jimmy\',now(),now());
 update users set name=\'zhangsan3\' where id=1;

 select id,name from users;

测试四种隔离级别;

测试READ UNCOMMITTED

请输入图片描述

从上图可以看出,A客户端在事务中对users表插入了一条记录(步骤7)但是还没有commit,B客户端就能读取到数据了(步骤8),A客户端回滚后(步骤9),B客户端就读取不到数据了,B读取到了A未提交的记录。对于READ UNCOMMITTED是针对自己线程而言,对上图就是B客户端。
上图展示的是A客户端插入数据的情况,对于修改也是一样的。
不管A客户端设置的隔离级别是什么,对B客户端结果都是一样的。

测试READ COMMITTED (解决脏读)

请输入图片描述

从上图可以看出,两个客户端同时开启事务,A客户端在事务过程中对数据进行了修改,B客户端在一个事务中执行了多次select操作,在A客户端执行update后commit之前,B客户端获取的数据是一样的,也就是解决了脏读的问题。B客户端在A客户端commit执行之后select操作返回了不同的数据,没有解决不可重复读的问题。

测试REPEATABLE READ (解决不可重复读)

请输入图片描述

可重复读,A客户端 和 B客户端同时开启事务,在A客户端修改一条数据,并插入一条心数据后commit之前,B客户端读取到的数据只有一条记录,name=zhangsan,A客户端commit之前,对B客户端数据读取无影响,解决脏读问题。
A客户端commit之后,B客户端执行了select也没有看到数据的修改,B客户端执行一条insert后报错了,主键冲突,说明这条数据和A客户端插入的数据冲突了。B客户端执行了一条update语句,虽然update这条数据没有显示出来,但是update成功了,肯定是A客户端插入成功的。B客户端再执行select操作,两条记录都显示了,并且第一条记录是name=zhangsan。
以上说明B客户端在一个会话中执行了两次select操作,并且对id=1看到的值都是一样的,可重复读,虽然A客户端修改了这个值为lisi。
幻读指第二次读取到的数量比第一次多,但是这里没出现这个现象,也就是说mysql在REPEATABLE READ级别也不存在幻读。

测试SERIALIZABLE (解决幻读)

请输入图片描述

所有的操作必须等待前一个事务结束,后一个事务才能开始,否则就需要等待,比如A客户端update一条记录的时候,B客户端对这个记录是无法查询的,要等到A客户端commit以后,B客户端才能返回结果,也有可能是超时SERIALIZABLE级别解决了幻读,待考察。