Mysql - 5 事务与视图
约 2889 字大约 10 分钟
视图
视图（View）是一个虚拟表，它是基于 SQL 查询结果集的可视化表示
视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];
WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入，创建后，我们就可以使用select语句来直接查询视图上的数据了，因此，还能在视图的基础上，导出其他的视图
-- 创建视图
CREATE VIEW user_summary AS
SELECT id, name, age
FROM users
WHERE age >= 18;
-- 使用视图（就像查询表一样）
SELECT * FROM user_summary WHERE age > 25;
-- 删除视图
DROP VIEW user_summary;
某些简单视图支持 INSERT、UPDATE、DELETE 操作
视图只是一个查询的"窗口"，当你对视图进行增删改操作时，MySQL 会将这些操作转换为对基础表的操作。
- 若视图是由两个以上基本表导出的，则此视图不允许更新。
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
- 若视图的字段来自集函数，则此视图不允许更新。
- 若视图定义中含有GROUP BY子句，则此视图不允许更新。
- 若视图定义中含有DISTINCT短语，则此视图不允许更新。
- 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
- 一个不允许更新的视图上定义的视图也不允许更新
事务
事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！
只有Innodb引擎支持事务
使用
Mysql 默认开启事务自动提交
-- 查看事务提交方式（1=自动提交，0=手动提交）
SELECT @@autocommit;
-- 设置为手动提交
SET @@autocommit = 0;
-- 设置为自动提交
SET @@autocommit = 1;
手动控制事务
方式一
-- 开启事务
BEGIN; -- 或 START TRANSACTION;
-- 执行SQL操作
UPDATE account SET money = money - 1000 WHERE name = '张三';
UPDATE account SET money = money + 1000 WHERE name = '李四';
-- 提交事务
COMMIT;
-- 或者回滚事务（撤销所有操作）
ROLLBACK;
方式二
-- 关闭自动提交
SET @@autocommit = 0;
-- 执行SQL
UPDATE account SET money = money - 1000 WHERE name = '张三';
UPDATE account SET money = money + 1000 WHERE name = '李四';
-- 提交事务
COMMIT;
-- 或回滚
ROLLBACK;
事务四大特性 (ACID)
| 特性 | 说明 |
|---|---|
| 原子性 Atomicity | 事务是不可分割的最小操作单元，要么全部成功，要么全部失败 |
| 一致性 Consistency | 事务完成时，必须使所有数据保持一致状态 |
| 隔离性 Isolation | 数据库提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行 |
| 持久性 Durability | 事务一旦提交或回滚，它对数据库中数据的改变就是永久的 |
- 持久性是通过 redo log（重做日志）来保证的；
- 原子性是通过 undo log（回滚日志）来保证的；
- 隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
- 一致性则是通过持久性 + 原子性 + 隔离性来保证；
原子性
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-- 要么两条都执行成功，要么都不执行
UPDATE account SET money = money - 1000 WHERE name = '张三'; -- ✓
UPDATE account SET money = money + 1000 WHERE name = '李四'; -- ✗
-- 如果第二条失败，第一条也会回滚
一致性
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-- 转账前后，总金额保持不变
-- 转账前：张三1000 + 李四1000 = 2000
-- 转账后：张三0 + 李四2000 = 2000
隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，包括:
- 读未提交（Read uncommitted）
- 读提交（read committed）
- 可重复读（repeatable read）
- 串行化（Serializable）。
持久性
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
并发事务问题
| 问题 | 说明 |
|---|---|
| 脏读（Dirty Read） | 一个事务读到另一个事务还没提交的数据 |
| 不可重复读（Non-Repeatable Read） | 一个事务先后读取同一条记录，但两次读取的数据不同 |
| 幻读（Phantom Read） | 一个事务查询数据时，没有对应记录，但插入数据时又发现数据已存在 |
- 脏读：读到其他事务未提交的数据；
- 不可重复读：前后读取的数据不一致；
- 幻读：(有不存在的数据出现) 查询时数据不在，但插入确是数据存在；前后读取的记录数量不一致
示例
脏读
读到存在一下就被删除的数据
-- 事务A
BEGIN;
UPDATE account SET money = 2000 WHERE id = 1;
-- 未提交
-- 事务B
SELECT money FROM account WHERE id = 1; -- 读到2000（脏数据）
-- 事务A回滚
ROLLBACK; -- 事务B读到的数据无效
不可重复读
两次获取的数据不一致
-- 事务A
BEGIN;
SELECT money FROM account WHERE id = 1; -- 读到1000
-- 事务B
UPDATE account SET money = 2000 WHERE id = 1;
COMMIT;
-- 事务A
SELECT money FROM account WHERE id = 1; -- 读到2000（前后不一致）
幻读
查询数据时，没有对应记录，但插入数据时又发现数据已存在
-- 事务A
BEGIN;
SELECT * FROM account WHERE id = 5; -- 查询不到
-- 事务B
INSERT INTO account VALUES(5, 'test', 1000);
COMMIT;
-- 事务A
INSERT INTO account VALUES(5, 'test', 1000); -- 插入失败，主键冲突（明明查不到）
事务隔离级别
- 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
- 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
- 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
- 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| Read Uncommitted（读未提交） | ✓ | ✓ | ✓ | 最高 |
| Read Committed（读已提交） | ✗ | ✓ | ✓ | 较高 |
| Repeatable Read（可重复读，默认） | ✗ | ✗ | ✓ | 较低 |
| Serializable（串行化） | ✗ | ✗ | ✗ | 最低 |
读未提交
- 事务可以读取其他事务未提交的数据
- 性能最高，但数据一致性最差
- 几乎不使用
- 脏读 不可重复读 幻读均会发生
读已提交
- 只能读取已提交的数据，解决了脏读
- Oracle、SQL Server 的默认隔离级别
- 仍然存在不可重复读和幻读
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT money FROM account WHERE id = 1; -- 读到1000
-- 会话B（在会话A之后执行）
UPDATE account SET money = 2000 WHERE id = 1;
COMMIT;
-- 会话A
SELECT money FROM account WHERE id = 1; -- 读到2000（不可重复读）
-- INSERT 就幻读
可重复读
MVCC 机制解决幻读
- MySQL 的默认隔离级别
- 同一事务中多次读取同一数据，结果一致，解决了不可重复读
- MySQL 通过 MVCC 机制基本解决了幻读（但某些场景仍可能发生）
串行化
- 最高隔离级别，完全串行执行
- 解决所有并发问题，但性能最差
- 通过锁机制，强制事务串行执行
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM account WHERE id = 1;
-- 会话B
UPDATE account SET money = 2000 WHERE id = 1; -- 会被阻塞，等待会话A提交
场景
| 隔离级别 | 适用场景 |
|---|---|
| Read Uncommitted | 几乎不用，数据一致性太差 |
| Read Committed | Oracle、SQL Server 默认，适合大多数场景 |
| Repeatable Read | MySQL 默认，兼顾性能和一致性 |
| Serializable | 极高一致性要求，如金融核心系统 |
InnoDB 避免幻读
MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章），解决的方案有两种：
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
- 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
实现方式
- 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
- 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
- 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。