MySQL (三) 事务隔离级别

俯首思远
678 阅读6分钟
1、mysql架构图

mysql架构

自己又画了一遍：

self

2、事务隔离级别常用命令

-- 查询设置会话的事务隔离级别：
SELECT @@tx_isolation;
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
-- 查询设置全局事务隔离级别：
select @@global.tx_isolation;
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;
-- 查询当前连接id：
select connection_id();
-- 查询当前会话信息：
select * from sys.session;
-- 查询当前会话下的事务：
select * from INFORMATION_SCHEMA.INNODB_TRX  WHERE TRX_MYSQL_THREAD_ID = connection_id();

事务隔离级别的文字解释有很多，不再赘述，直接实操一下，看看有什么区别：

（1）建库建表：

create DATABASE study;

create table if not EXISTS `study1`(
	id int not null AUTO_INCREMENT,
	val varchar(128) NOT NULL DEFAULT'',
  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

-- 插入数据
insert into study1 (val) VALUES ('one'),('two'),('three');

（2）Navicat打开两个查询窗口,查询连接id，确认不是同一个会话，以便测试。

  • 设置隔离级别为未提交读 read uncommitted；
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

查询各自的连接id 连接id

在29会话中开始事务，暂不提交，修改one为one1；

mysql> use study
Database changed
mysql> select * from study1;
+----+-------+---------------------+---------------------+
| id | val   | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | one   | 2021-01-15 18:13:41 | 2021-01-18 09:46:53 |
|  2 | two   | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
|  3 | three | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
+----+-------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update study1 set val = 'one1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

看30会话中时候可以查询到未提交的修改：

mysql> use study
Database changed
mysql> select * from study1;
+----+-------+---------------------+---------------------+
| id | val   | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | one1  | 2021-01-15 18:13:41 | 2021-01-18 10:04:45 |
|  2 | two   | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
|  3 | three | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
+----+-------+---------------------+---------------------+
3 rows in set (0.00 sec)

如果此时在29中输入ROLLBACK;则30中查询的one1改回one，但是之前查询到one1会导致脏读；

  • 提交读 read committed;
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

设置全局变量，29、30查询事务隔离级别都是提交读，重复第一步的操作：发现还是未提交读，和预想的不一样，是不是会话的隔离级别还没改过来，重新登录mysql，再执行第一步操作（重新登陆后，会话id改变，下文用会话一、会话二表示）：

会话一：开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update study1 set val = 'one1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话二：会话一未提交前查询数据；
mysql> select * from study1;
+----+-------+---------------------+---------------------+
| id | val   | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | one   | 2021-01-15 18:13:41 | 2021-01-18 09:46:53 |
|  2 | two   | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
|  3 | three | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
+----+-------+---------------------+---------------------+
3 rows in set (0.02 sec)
未提交的修改在会话二中查询不到；

会话一：提交one1修改；
mysql> commit;
Query OK, 0 rows affected (0.15 sec)

会话二：可以查询到修改后的one1；
mysql> select * from study1;
+----+-------+---------------------+---------------------+
| id | val   | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | one1  | 2021-01-15 18:13:41 | 2021-01-18 10:18:24 |
|  2 | two   | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
|  3 | three | 2021-01-15 18:13:41 | 2021-01-15 18:13:41 |
+----+-------+---------------------+---------------------+
3 rows in set (0.00 sec)

一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

  • 可重复读 repeatable read
会话一：修改全局事务隔离级别，再查询一下当前会话的事务隔离级别，发现不一致，要么重连，要么直接修改会话事务隔离级别，上一次选用的重连，这次直接修改会话的隔离级别吧：

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
 -- 查询全局事务隔离级别
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- 查询当前会话事务隔离级别
mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
-- 设置当前会话事务隔离级别为可重复读
mysql> set session 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)

同理：记得对会话二也修改事务隔离级别

-- 会话一：开启事务，修改one的值为one1，暂不提交

START TRANSACTION;

update study1 set val = 'one1' where id = 1;


-- 会话二：开启事务，查询id为1的记录，

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

mysql> select * from study1 where id = 1;
Query OK, 0 rows affected (0.00 sec)

-- 查询记录val为修改前的one 
+----+-----+---------------------+---------------------+
| id | val | create_time         | update_time         |
+----+-----+---------------------+---------------------+
|  1 | one | 2021-01-15 18:13:41 | 2021-02-26 16:57:48 |
+----+-----+---------------------+---------------------+
1 row in set (0.00 sec)

会话一提交修改语句事务后，会话二再查询id为1的值，发现val还是one，只有把会话二事务提交后，才可以查询到one修改为one1。

所以可重复读的意思就是:如果数据在本事务内没有修改，事务内每次查询的值都会和第一次查询保持一致；等同于其他事务在本事务开始后提交的修改，对本事务没有影响。当然，新增记录可能在范围查询的时候导致幻行。

  • 串行化（SERIALIZABLE） 上边3种隔离级别都允许对同一条记录进行读-读、读-写、写-读的并发操作，如果不允许这些并发操作，可以修改会话一二的事务隔离级别为串行化：

会话一：开启事务，修改id为1的记录，事务暂不提交

START TRANSACTION;

update study1 set val = 'one1' where id = 1;

会话二：开启事务，查询id为1的记录，查询等待

START TRANSACTION;

select * from study1 where id = 1;

篇幅关系，只叙述写-读这一种并发模式

要想真正了解可重复读的原理，需要了解mysql的mvcc协议，主要讲的是undo日志中版本链的实现以及查询时事务id判断

参考链接： MySQL事务隔离级别和MVCC

avatar
文章
阅读
粉丝