我们在利用MySQL进行事务的操作时,不同客户端对一个事务进行并发操作时,难免会出现事务的冲突问题。MySQL提供了解决这些问题的代码,即隔离等级。接下来让我为大家讲解这些问题的类型和隔离等级的作用。
目录
——初步认识问题以及隔离级别
---问题:
(1)脏读
一个事务读到另一个事务还没有提交的数据。当B事务进行更新的操作时,该操作作为事务还未提交,但影响了A事务,使A事务读到了B事务未提交的脏数据。
(2)不可重复读
一个事务先后读取同一条记录,但两次读取的数据不同,称为不可重复读这里A事务两次查询的事务不同,是因为B事务对数据进行了修改,A事务在重复读取数据的时候会发现数据不同。
(3)幻读
一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已存在,好像出现了幻影。
---隔离等级
(1)Read uncommitted:
脏并发事务读x 不可重复读√ 幻读√(性能最高,数据安全性最差)
(2)Read committed:
脏读x 不可重复读√ 幻读√
(3)Repeatable read
脏读x 不可重复读x 幻读√(MySQL的默认隔离等级)
(4)Serializable
脏读x 不可重复读x 幻读x(性能最底下,数据安全性最高)
---隔离级别的查询和设置
查询隔离级别:
SELECT @@TRANSACTION_ISOLATION;
设置隔离级别:
SET [SESSION | GOLBAL ] TRANSACTION LEVEL (Read uncommitted/Read committed/Repeatable/Serializable();
/*其中SESSION 表示只设置当前事务,而GOLBAL表示设置全局事务*/
——隔离级别演示以及问题介绍
这里我们打开两个命令操作符号,用 mysql -u 用户名 -p 载入数据库,分别表示用户1和用户2在同时操作一个数据集。该数据集为account,表达了用户银行的信息。具体数据如下:
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
---Read uncommitted:
read uncommitted没有任何的隔离功能。
-脏读问题:
/*这里使用命令提示符来输入的,现在是用户A*/
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
可见用户A采用了read uncommitted隔离模式,这个隔离模式起不到任何的隔离作用。A开启了一个事务,对数据集进行了查询,但没有结束事务
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> update account set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
这里用户B也开启了一个事务,并且更新了数据,但并没有结束事务。
mysql> /*用户A*/
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
这里我们在切换到用户A的客户端,再次进行查询,发现数据变换了,出现了“脏读”现象。
---Read committed:
-脏读问题的解决:
read committed可以解决脏读问题。
mysql> /*用户A*/
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
这里用户A进行了read committed级别的隔离,并且开启事务对account进行了查询。
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
用户B开启了事务,并再次成功更改了数据。
mysql> /*用户A*/
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
而这次用户A再次进行查询时,数据并没有发生变化,说明脏读问题得到了解决。
-不可重复读问题:
mysql> /*用户A*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
数据复原后,用户A再次开启新的事务,对account进行查询。
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
这时,用户B再次更改了数据,并且进行了提交
mysql> /*用户A*/
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
当用户A在它的事务中再次查询时,出现了已经更改过的数据,这就出现了不可重复读的问题。
---Repeatable Read
Repeatable可以解决脏读,不可重复读的问题。
-不可重复读问题的解决:
mysql> /*用户A*/
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
用户A开启了Repeatable read隔离级别
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money = money - 1000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
这时,用户B再次更改了数据,并且进行了提交
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
+----+------+-------+
2 rows in set (0.00 sec)
这是,用户A事务不会被B已经提交的事务数据而污染,解决了不可重复读的问题。
-幻读问题:
mysql> /*用户A*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where id=3;
Empty set (0.01 sec)
开启A的事务,查询id为3的人,结果显示不存在。
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account(id,name,money) values(3,'王五',2000);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
用户B插入一条新数据,并且提交了事务。
mysql> /*用户A*/
mysql> select * from account where id=3;
Empty set (0.00 sec)
mysql> insert into account(id,name,money) values(3,'二麻子',2000);
ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'
这是用户A再次查询,还是没有数据,可当A想插入一条数据时,却出现了数据冲突的错误,就像出现幻觉一样。这就是幻读问题
--- Serializable
-幻读问题的解决:
mysql> /*用户A*/
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where id=4;
Empty set (0.00 sec)
让用户A使用Serializable隔离级别,并开启事务,查询到id为4的数据不存在
mysql> /*用户B*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account(id,name,money) values(4,'二麻子',2000);
_
在用户B想插入一条id为4的数据时,返回并没有成功,只是光标一直在下面一行闪动。说明事务出现了堵塞,因为A事务在执行,为了避免幻读,B事务在A事务未提交事务完成时不可以插入任何数据。这就可以解决幻读的问题