MySQL学习笔记（8）：TCL（事务控制语言）

事务控制语言

一、事务综述

（1）定义
由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。（整个单独单元作为一个不可分割的整体），要么都执行，要么都不执行。

（2）身边的事务
假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

（2）事务的特点（总结ACID）

1. 原子性（Atomicity）
   事务是最小的执行单位，不允许分割。事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
3. 隔离性（Isolation）
   并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
4. 持久性（Durability）
   一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

二、事务的创建

（1）创建
步骤1：开启事务
SET autocommit = 0；
START TARNSACTION；
步骤2：编写事务中的sql语句（select、insert、update、delete）
语句1；
语句2；
…
步骤3：结束事务
commit； 提交事务
rollback； 回滚事务

（2）savepoint的使用
savepoint的使用举例（只搭配回滚使用，指事务处理中设置的临时占位符，你可以对它发布退回（与回退整个事务不同））

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;       //设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;   //回滚到保存点

**实现现象：**28号没有被删除，25号被删除了

（3）在mysql中用的最多的存储引擎有：innodb，myisam,memory 等。其中innodb支持事务，而myisam、memory等不支持事务

三、并发事务带来的问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致如下并发问题。

1. 脏读（Dirty read）
   对于两个事务T1、T2。 T1 读取了已经被T2 更新但还没有被提交的字段。 之后, 若T2 回滚，T1读取的内容就是临时且无效的。

2. 丢失修改（Lost to modify）
   指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

3. 不可重复读（Unrepeatableread）
   对于两个事务T1、T2，T1 读取了一个字段, 然后T2 更新了该字段。之后，T1再次读取同一个字段, 值就不同了。

4. 幻读（Phantom read）
   对于两个事务T1、 T2。T1 从一个表中读取了一个字段, 然后T2 在该表中插入了一些新的行。 之后， 如果T1 再次读取同一个表，就会多出几行，就好像发生了幻觉一样。

不可重复度和幻读区别：
不可重复读的重点是修改，幻读的重点在于插入或删除。

举例：

• 不可重复读：（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000。

• 幻读：（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条。

脏读是读到了未提交的数据(有其他用户在更改数据)，而不可重复读读的是已经提交的，但是违反了事务的一致性要求。

脏读：数据读错方只读了一次数据库。
不可重复读：数据读错放读了多次数据库。

四、事务隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是REPEATABLE-READ（可重读）。

（1）定义
一个事务与其他事务隔离的程度。（ 隔离级别越高, 数据一致性就越好, 但并发性越弱）

（2）四个隔离级别

1. READ-UNCOMMITTED(读取未提交)
   最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

2. READ-COMMITTED(读取已提交)
   允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

3. REPEATABLE-READ(可重复读)
   对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

4. SERIALIZABLE(可串行化)
   最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

（3）四个隔离界别的区别

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

查看当前的隔离级别: SELECT @@tx_isolation;
设置当前mySQL 连接的隔离级别:
set transaction isolation level read committed;
设置数据库系统的全局的隔离级别:
set globaltransaction isolation level read committed;

五、并发事务问题的演示

事务1和事务2的事务隔离等级必须都先设置成一样的

1.脏读（读到未提交的数据）

（1）举例

（2）避免脏读的方法
从上图可以看到脏读的原因是事务1读到了事务2未提交的数据，所以要避免脏读，事务2只需要将数据提交至数据库中，这个时候事务1就不会发生脏读了。

（3）举例用的sql语句

• 事务1的sql语句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;		//设置事务等级

SELECT @@TX_ISOLATION;			//查看当前事务等级

START TRANSACTION;				//开启事务

USE myemployees;			//选择数据库

SELECT salary FROM employees WHERE employee_id=100;   //查看薪水

• 事务2的sql语句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;		//设置事务等级

SELECT @@TX_ISOLATION;			//查看当前事务等级

START TRANSACTION;				//开启事务

USE myemployees;			//选择数据库

UPDATE employees SET salary = 1000 WHERE employee_id=100;   //更改薪水

ROLLBACK;			//回滚事务

2.不可重复读

在事务等级为READ UNCOMMITTED的前提下。就算事务2将修改后的数据提交，但是却出现了一个事务还没结束，发生了不可重复读的问题（事务1还没结束，但是读取到的两次薪水却不一样，一次是5000，一次是1000）
（1）举例
在事务等级为READ UNCOMMITTED的前提下。

（2）避免事务不可重复读
修改事务隔离等级为REPEATABLE-READ。可以看到无论事务2对薪水的修改提交了还是没提交，事务2读到的薪水都是5000。

3.幻读

（1）概念
一个事务对数据库进行操作，这种操作的范围是数据库的全部行，然后第二个事务也在对这个数据库操作，这种操作可以是插入一行记录或删除一行记录，那么第一个是事务就会觉得自己出现了幻觉，怎么还有没有处理的记录呢? 或者 怎么多处理了一行记录呢?
（2）特点
幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。