MySQL的四种事务隔离级别

(一)事务的基本要素(ACID)

①:原子性(Atomicity):【针对该事务的操作】事务开始后所有操作,要么全部做完,要么全部不做

②:一致性(Consistency):【针对数据库状态要改 一起改。】事务开始前和结束后--->数据库完整性约束没有被破坏。eg:就像我给你转账,不可能我扣了钱,而你没收到钱。

③:隔离性(Isolation):【针对事务与事务之间】一个事物所做的修改在最终提交之前,对其他事务不可见的。

④:持久性(durability):【针对数据库保存】一旦事务提交后,所做的修改会永久保存数据库中。此时即使系统崩溃了。修改的数据也不会消失。

(二)事务并发产生的问题

①第一类丢失更新【更新的丢了】:在没有事务隔离的情况下,两个事物都同时更新一行数据,但是第二个事务中途失败退出,导致对数据的修改失效。

eg:小明的工资是1000块,事务A获取工资1000,事务B汇入100块,并提交到数据库,工资变为1100。之后呢,事务A发生异常,回滚了,回复小明工资为1000块---->这就导致事务B的更新数据丢失了。

脏读【读的错了】:事务A读取了事务B更新的数据,然后事务B回滚了,这样A读取到的数据就是脏数据。

③:不可重复读【修改数据时】:在A事物内多次取同一数据。在A事务还没有提交时,另一个B事务修改了同一数据,A再次读到的数据就可能不一样。eg:

小明的工资为1000,事务A中把他的工资改为2000,但事务A尚未提交。
  与此同时,事务B正在读取小明的工资,读取到张三的工资为2000。
  随后,事务A发生异常,而回滚了事务。张三的工资又回滚为1000。
  最后,事务B读取到的张三工资为2000的数据即为脏数据,事务B做了一次脏读。

 

④:第二类丢失更新【更新覆盖了】:不可重复读的特例。有A、B两个事物同时读取一行数据,然后A对它修改该提交,而B也进行了修改提交。这回造成A事物操作失效。

⑤:幻读【新增、删除数据时】:事务不独立执行发生的一种现象。eg:系统管理员A将数据库中所有学生成绩从具体分数该为ABCD等级制,但同时,系统管理员B在这个时候插入了一条具体的分数记录。当A修改结束后发现还有一条记录没有修改过来,就好像发生了幻觉一样。

(三)MySQL事务的隔离级别

 

(1)READ_UNCOMMITTED【未提交读---很少用】
  这是事务最低的隔离级别,它充许另外一个事务可以看到这个事务未提交的数据。
  解决第一类丢失更新的问题,但是会出现脏读不可重复读、第二类丢失更新的问题幻读 。
(2)READ_COMMITTED【提交读---大多数数据库默认的隔离级别(MySQL不是)】
  保证一个事务修改的数据提交后才能被另外一个事务读取,即另外一个事务不能读取该事务未提交的数据。
  解决第一类丢失更新和脏读【√】的问题,但会出现不可重复读第二类丢失更新的问题,幻读问题【×】
(3)REPEATABLE_READ【可重复读---MySQL默认的隔离级别(不会锁住,读取到的行)】
  保证一个事务相同条件下前后两次获取数据是一致的

解决第一类丢失更新,脏读、不可重复读、第二类丢失更新的问题,但会出幻读。
(4)SERIALIZABLE【可串行化,最高隔离级别---很少用(每行加锁-->超时,锁争用)】
  事务被处理为顺序执行。
  解决所有问题

 

(四)具体的一些列子:

 

  1、读未提交:

 

    (1)打开一个客户端A,并设置当前事务模式为read uncommitted(未提交读),查询表account的初始值:

 

 

 

    (2)在客户端A的事务提交之前,打开另一个客户端B,更新表account:

 

 

 

 

 

    (3)这时,虽然客户端B的事务还没提交,但是客户端A就可以查询到B已经更新的数据:

 

 

 

    (4)一旦客户端B的事务因为某种原因回滚,所有的操作都将会被撤销,那客户端A查询到的数据其实就是脏数据:

 

 

 

     (5)在客户端A执行更新语句update account set balance = balance - 50 where id =1,lilei的balance没有变成350,居然是400,是不是很奇怪,数据的一致性没问啊,如果你这么想就太天真 了,在应用程序中,我们会用400-50=350,并不知道其他会话回滚了,要想解决这个问题可以采用读已提交的隔离级别

 

 

 

 

  2、读已提交

 

    (1)打开一个客户端A,并设置当前事务模式为read committed(未提交读),查询表account的初始值:

 

 

 

    (2)在客户端A的事务提交之前,打开另一个客户端B,更新表account:

 

 

 

    (3)这时,客户端B的事务还没提交,客户端A不能查询到B已经更新的数据,解决了脏读问题:

 

 

 

    (4)客户端B的事务提交

 

 

    (5)客户端A执行与上一步相同的查询,结果 与上一步不一致,即产生了不可重复读的问题,在应用程序中,假设我们处于客户端A的会话,查询到lilei的balance为450,但是其他事务将lilei的balance值改为400,我们并不知道,如果用450这个值去做其他操作,是有问题的,不过这个概率真的很小哦,要想避免这个问题,可以采用可重复读的隔离级别

 

 

 

 

   3、可重复读

 

     (1)打开一个客户端A,并设置当前事务模式为repeatable read,查询表account的初始值:

 

 

    (2)在客户端A的事务提交之前,打开另一个客户端B,更新表account并提交,客户端B的事务居然可以修改客户端A事务查询到的行,也就是mysql的可重复读不会锁住事务查询到的行,这一点出乎我的意料,sql标准中事务隔离级别为可重复读时,读写操作要锁行的,mysql居然没有锁,我了个去。在应用程序中要注意给行加锁,不然你会以步骤(1)中lilei的balance为400作为中间值去做其他操作

 

 

    (3)在客户端A执行步骤(1)的查询:

 

 

    (4)执行步骤(1),lilei的balance仍然是400与步骤(1)查询结果一致,没有出现不可重复读的 问题;接着执行update balance = balance - 50 where id = 1,balance没有变成400-50=350,lilei的balance值用的是步骤(2)中的350来算的,所以是300,数据的一致性倒是没有被破坏,这个有点神奇,也许是mysql的特色吧

 

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | lilei  |     400 |
|    2 | hanmei |   16000 |
|    3 | lucy   |    2400 |
+------+--------+---------+
3 rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | lilei  |     300 |
|    2 | hanmei |   16000 |
|    3 | lucy   |    2400 |
+------+--------+---------+
3 rows in set (0.00 sec)

 

    (5) 在客户端A开启事务,查询表account的初始值

 

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

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
3 rows in set (0.00 sec)

 

    (6)在客户端B开启事务,新增一条数据,其中balance字段值为600,并提交

 

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

mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)

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

 

    (7) 在客户端A计算balance之和,值为300+16000+2400=18700,没有把客户端B的值算进去,客户端A提交后再计算balance之和,居然变成了19300,这是因为把客户端B的600算进去了

 

,站在客户的角度,客户是看不到客户端B的,它会觉得是天下掉馅饼了,多了600块,这就是幻读,站在开发者的角度,数据的 一致性并没有破坏。但是在应用程序中,我们得代码可能会把18700提交给用户了,如果你一定要避免这情况小概率状况的发生,那么就要采取下面要介绍的事务隔离级别“串行化”

 

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)

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

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

 

  

 

  4.串行化

 

    (1)打开一个客户端A,并设置当前事务模式为serializable,查询表account的初始值:

 

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;
+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
|    1 | lilei  |   10000 |
|    2 | hanmei |   10000 |
|    3 | lucy   |   10000 |
|    4 | lily   |   10000 |
+------+--------+---------+
4 rows in set (0.00 sec)

 

    (2)打开一个客户端B,并设置当前事务模式为serializable,插入一条记录报错,表被锁了插入失败,mysql中事务隔离级别为serializable时会锁表,因此不会出现幻读的情况,这种隔离级别并发性极低,往往一个事务霸占了一张表,其他成千上万个事务只有干瞪眼,得等他用完提交才可以使用,开发中很少会用到。

 

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> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

posted @ 2018-02-28 17:00  瀧思葉  阅读(179)  评论(0编辑  收藏  举报