事务控制语言
一、事务综述
(1)定义
由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。(整个单独单元作为一个不可分割的整体),要么都执行,要么都不执行。
(2)身边的事务
假如小明要给小红转账1000元,这个转账会涉及到两个关键操作就是:将小明的余额减少1000元,将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃,导致小明余额减少而小红的余额没有增加,这样就不对了。事务就是保证这两个关键操作要么都成功,要么都要失败。
(2)事务的特点(总结ACID)
- 原子性(Atomicity)
事务是最小的执行单位,不允许分割。事务中的操作要么都发生,要么都不发生。 - 一致性(Consistency)
执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的。 - 隔离性(Isolation)
并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的。 - 持久性(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等不支持事务
三、并发事务带来的问题
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致如下并发问题。
-
脏读(Dirty read)
对于两个事务T1、T2。 T1 读取了已经被T2 更新但还没有被提交的字段。 之后, 若T2 回滚,T1读取的内容就是临时且无效的。 -
丢失修改(Lost to modify)
指在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。 -
不可重复读(Unrepeatableread)
对于两个事务T1、T2,T1 读取了一个字段, 然后T2 更新了该字段。之后,T1再次读取同一个字段, 值就不同了。 -
幻读(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)四个隔离级别
-
READ-UNCOMMITTED(读取未提交)
最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。 -
READ-COMMITTED(读取已提交)
允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。 -
REPEATABLE-READ(可重复读)
对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。 -
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)特点
幻读和不可重复读有些相似之处 ,但是不可重复读的重点是修改,幻读的重点在于新增或者删除。