mysql select 40001_mysql事务隔离的一点理解

前言

先介绍一下事务的概念

事务(Transaction)就是数据库管理的一个逻辑单位，由一个有效的数据库操作序列构成。

事物ACID特性

原子性(Atomicity)：事务作为一个整体被执行，要么全部成功执行，要么全部失败

一致性(Consistency)：指的是逻辑上的一致性，即所有操作是符合现实当中的期望的

隔离性(Isolation)：多个事务并发时，一个事务不应该影响其他事务的执行

持久性(Durability)：被提交过的事务对数据库的修改应该永久保存在数据库中

通俗的理解，就是将一系列的数据库操作(增删改查)看做一个整体，要么所有操作都成功，要么都失败。

一、产生的问题

如果没有事务隔离的话，会发生以下的问题

1. 脏读

脏读是指一个事务读取了另一个事务未提交的数据

2. 不可重复读

不可重复读是指事务中多次读取，数据改变。

不可重复读和脏读的区别：不可重复读是事务A读取某行数据后，事务B插入了一行新数据并提交之后，事务A再去读数据，读到了修改后的数据集。

3. 幻读

幻读是指事务多次读取，结果集数量改变。

上面还有点不清晰，借鉴一下别人的白话解释

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读；

不可重复读针对的是同一条数据，幻读针对的是一片数据。

二、事务隔离

Read uncommitted(读未提交)

Read Committed(读已提交)

Repeatable Reads(可重复读)

Serializable(串行化)

1. 读未提交

隔离级别最低的一种事务级别，会发生脏读，不可重复读，幻读

2. 读已提交

读到的都是别人提交后的值。这种隔离级别下，会引发不可重复读和幻读，但避免了脏读。

3. 可重复读

这种隔离级别下，会引发幻读，但避免了脏读、不可重复读。

4. 串行化

最严格的隔离级别。在串行化隔离级别下，所有事务按照次序依次执行。脏读、不可重复读、幻读都不会出现。

三、测试

下面我们在MYSQL下，来对上面四种事务隔离进行测试

在mysql中首先建立一张student表，有如下数据

学生id

学生姓名

学生性别

1

花轮

男

2

小丸子

女

看一下mysql的默认隔离级别

mysql> SELECT @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.03 sec)

默认是可重复读，下面我们就来测试

1. 读未提交

首先开启事务A，读表中数据数据

mysql> begin;

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.02 sec)

打开另一个窗口，开启事务B，修改表中数据，不提交

mysql> begin ;

mysql> update student set sex='女' where id=1;

Query OK, 0 rows affected (0.00 sec)

再回到事务A中读表数据，看是否改变

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.01 sec)

可以发现在mysql下没有发生脏读。把上面两个事务进行回滚(rollback)操作

2. 读已提交

首先开启事务A，读表中数据数据

mysql> begin;

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.02 sec)

打开另一个窗口，开启事务B，修改表中数据，并提交

mysql> begin ;

mysql> update student set sex='女' where id=1;

Query OK, 0 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

再回到事务A中读表数据，看是否改变

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.00 sec)

可以发现在mysql下避免了不可重复读。将之前修改的数据改回来

mysql> update student set sex='男' where id=1;

3. 可重复读

开启A事务，读取表中数据

mysql> begin ;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.00 sec)

打开另一个窗口，开启事务B，插入一条数据，并提交

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values ('3','飞飞' ,'女' );

Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

| 3 | 飞飞 | 女 |

+----+------------+------+

3 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

在A事务中查看数据

mysql> select * from student ;

+----+------------+------+

| id | name | sex |

+----+------------+------+

| 1 | 花轮 | 男 |

| 2 | 小丸子 | 女 |

+----+------------+------+

2 rows in set (0.00 sec)

可以看出，mysql在这里帮我们处理了幻读(这里是MVCC多版本并发控制解决了这个)

4. 串行化

前面的测试已经证明，在mysql的REPEATABLE-READ下，事务不会串行。

补充

这里多提一点，就是在REPEATABLE-READ下，事务是会发生死锁的，但mysql会自动给你处理死锁。

表student

学生id

学生姓名

学生性别

1

花轮

男

2

小丸子

女

表student1

学生id

学生姓名

学生性别

1

qq

女

2

ww

男

第一步，开启事务A，将student表 id=1 的 sex更改为女

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update student set sex='女' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

第二步，开启事务B，将student1表 id=1 的 sex更改为男

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> update student1 set sex='男' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

第三步，在事务A中，将student1表中 id=1的 sex更改为女

mysql> update student1 set sex='女' where id=1;

这里，事务A处于等待状态，因为这个更新操作需要等到事务B处理完成才能进行，而且如果超过一定时间没有处理，会提示超时错误

第四步，在事务B中，将将student1表中 id=1的 sex更改为男

mysql> update student set sex='男' where id=1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

可以看到在事务B中报错，产生了死锁

再回去看事务A，发现第三步的更新成功了

mysql> update student1 set sex='女' where id=1;

Query OK, 0 rows affected (19.03 sec)

Rows matched: 1 Changed: 0 Warnings: 0

这里涉及到的其实是数据库锁的概念，以后有机会再写篇有关的文章

第一次写文章，有很多不足的地方，请多多见谅～～