事物隔离级别

最新推荐文章于 2024-08-11 21:41:16 发布

包华杰

最新推荐文章于 2024-08-11 21:41:16 发布

阅读量555

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/u013268035/article/details/86011806

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、事物

一、事物并发带来的问题：脏读、幻读、不可重复读

二、事物的隔离级别

三、不同隔离级别在业务场景中的表现

四、不同事物隔离级别的演示

一、事物

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。

举例说明通过数据库事务保证数据的完整性和准确性。熟悉关系型数据库事务的都知道从帐号A到帐号B需要6个操作：

1、从A账号中把余额读出来（500）。

2、对A账号做减法操作（500-100）。

3、把结果写回A账号中（400）。

4、从B账号中把余额读出来（500）。

5、对B账号做加法操作（500+100）。

6、把结果写回B账号中（600）。

原子性:保证所有过程要么都执行，要么都不执行。一旦执行中出现问题，必须回滚到起始状态。

一致性：在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。

隔离性：并发访问数据库不同事务间不受影响。

持久性：一旦转账成功（事务提交commit），两个账户钱发生改变（会把数据写入数据库作持久化保存）

事物的原子性和一致性缺一不可！

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

一、事物并发带来的问题：脏读、幻读、不可重复读

1、脏读：事物A正在对一条数据进行修改，在事物A完成之前（可能提交也可能回滚），此时，事物B来读取该条没有提交或者回滚的数据，就会发生脏读的现象。例如：事物A将小明的工资由100元改为200元，在事物A提交或者回滚之前，事物B来读取小明的工资，事物B读完数据之后，事物A数据发生了回滚，事物B就做了一次脏读。

2、不可重复读：是指在同一个事务内两次读到的数据是不一样的。事物A读取一条数据，在事物A还没有结束之前，事物B来修改该条数据并提交事物，之后事物A又来读取该条数据，事物A前后两次读取的结果不一样，就产生了不可重复读的现象。例如：事物A读取到小明的工资为100元，此时事物A还没提交，与此同时，事物B将小明的工资修改为200元并进行提交，之后事物A再来读取小明的工资为200元了。

3、幻读：当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如：目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。此时，事务B插入一条工资也为5000的记录。此时，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

4、第一类更新丢失问题：A事务撤销时，把已提交的B事务的数据覆盖掉。

5、第二类更新丢失问题：A事务提交时，把已提交的B事务的数据覆盖掉。

注：不可重复读的重点是修改，同样的条件，两次读取的数据不一样。幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

二、事物的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

Mysql支持以上四种事物隔离级别，默认的事务隔离级别为repeatable_read。

oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别，默认系统事务隔离级别是READ COMMITTED。

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

三、不同隔离级别在业务场景中的表现

以下内容摘自
作者：jiesa
来源：CSDN
原文：https://blog.csdn.net/jiesa/article/details/51317164

Read uncommitted 读未提交
公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有 2000元，singo空欢喜一场。

出现上述情况，即我们所说的脏读，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

当隔离级别设置为Read uncommitted 时，就可能出现脏读，如何避免脏读，请看下一个隔离级别。

Read committed 读提交
singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在 singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为何......

出现上述情况，即我们所说的不可重复读，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed 时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。

Repeatable read 重复读
当隔离级别设置为Repeatable read 时，可以避免不可重复读。当singo拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），singo的老婆就不可能对该记录进行修改，也就是singo的老婆不能在此时转账。

虽然Repeatable read避免了不可重复读，但还有可能出现幻读。

singo的老婆工作在银行部门，她时常通过银行内部系统查看singo的信用卡消费记录。有一天，她正在查询到singo当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而singo此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction ... ），并提交了事务，随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，singo的老婆很诧异，以为出现了幻觉，幻读就这样产生了。

注：Mysql的默认隔离级别就是Repeatable read。

Serializable 序列化
Serializable 是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

四、不同事物隔离级别的演示

查看当前事物隔离级别：

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

开启两个mysql客户端A、B，模拟两个用户A、B。

建表和数据：

create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
 );
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);

1、脏读

（1）窗口A执行如下操作：设置事物隔离级别为read uncommitted，然后开启事物。

mysql> set global  transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

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

mysql>

（2）窗口B执行如下操作：将A客户的钱加100，但是不进行提交。

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

mysql> update account set money=money+100 where name='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

（3）窗口A执行如下操作：查询A的钱，发现A的钱多了100块（此时窗口B并没有提交事物）。

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1100 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

结论：由于事物隔离级别设置成了read uncommitted，事物B没有提交事物，事物A也读到了事物B对数据修改后的结果，出现了脏读的现象，read uncommitted可以引发脏读、不可重复读、幻读。

2、不可重复读

（1）窗口A执行如下操作：设置事物隔离级别为read committed，然后开启事物，此时A的钱是1000块。

mysql> mysql> set global  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 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

（2）窗口B执行如下操作：将A客户的钱加100，并进行提交。

mysql> update account set money=money+100 where name='A';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

（3）窗口A执行如下操作：查询A的钱，发现A的钱多了100块。

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

结论：由于事物隔离级别设置成了read committed，事物B提交事物修改了数据，事物A再进行读取时数据发生了改变，read committed可以引发不可重复读、幻读。

3、幻读

（1）窗口A执行如下操作：设置事物隔离级别为repeatable read（mysql默认级别），然后开启事物，此时A的钱是1000块。

mysql> set global 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 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

（2）窗口B执行以下操作：修改A的数据但是不提交，在事物B中，A的钱为1100块。

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

mysql> update account set money=money+100 where name='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1100 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

（3）窗口A查询数据：此时在事物A中A的钱为1000块，以为事物的隔离级别为repeatable read并且事物B对数据修改后还没提交。

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

结论1：由于事物隔离级别设置成了repeatable read不会出现脏读和不可重复读的问题，但是可能出现幻读，下面继续演示幻读。

（4）窗口B执行如下操作：

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

mysql> insert into account(name,money) values('D',1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
|  5 | D    |  1000 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> commit;

（5）窗口A查询数据，多出了一条D用户

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
|  5 | D    |  1000 |
+----+------+-------+
4 rows in set (0.00 sec)

结论2：由于事物隔离级别设置成了repeatable read不会出现脏读和不可重复读的问题，但是可能出现幻读。

4、事物隔离级别设置成SERIALIZABLE

（1）窗口A执行如下操作

mysql> set global 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 | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  1000 |
|  3 | C    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

（2）窗口B执行如下操作发现插入失败，根本不能进行插入操作。

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

mysql> insert into account(name,money) values('D',1000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

结论：当事物隔离级别设置成SERIALIZABLE时，事物A完成之前，事物B是不能进行事物操作的，从而避免了脏读、不可重复读、幻读问题。

五、mysql中的事物提交模式

mysql中的默认事物是自动提交的，查看方式如下，变量autocommit在会话系统变量中与全局系统变量的值如下，ON表示自动提交开启，OFF表示自动提交关闭。

mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

自动事物提交关闭：

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

注意，上述SQL修改会话系统变量或全局系统变量，只对当前实例有效，如果MySQL服务重启的话，这些设置就会丢失，如果要永久生效，就必须在配置文件中修改系统变量。

[mysqld]

autocommit=0