七、事务控制
简介
说明
Transaction Control Language 事务控制语言
事务
事务由单独单元的一个或一组sql语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行。
拓展
Mysql中的存储引擎
- 在mysql中的数据用各种不同的技术存储在文件(内存)中
- 通过 show engines; 命令来查看Mysql支持的存储引擎
- 在Mysql中用的最多的存储引擎：innodb，MyISAM，memory等。其中 innodb 支持事务，而 myisam、memory等不支持事务
delete和truncate在事务使用时的区别
- delete支持事务回滚
- truncate不支持事务回滚
事务的ACID()属性
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其它事务干扰，即一个事务内部的操作及使用的数据对并发的其它事务是隔离的，并发执行的各个事务之间不能相互干扰。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其它操作和数据库故障不应该对其有任何影响
案例
转账：
马 云：1000元
马化腾：1000元
马云向马化腾转账500元，简单 sql 如下：
update 表 set 余额=500 where name='马云';
update 表 set 余额=1500 where name='马化腾';
如果上面两条sql，再马云的余额减少 500 的情况下，数据库挂了，如果没有事务控制的话，马云的钱就变成了 500，马化腾还是 1000。
事务的创建
隐式事务
介绍
事务没有明显的开启和关闭。
例如：insert、update、delete
通过查看变量，执行命令：
show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
可查看，自动提交为 开启 状态，这样的话，执行增、删、改语句时，默认开启了事务，如果操作的是一组sql(多条sql)，隐形事务就没用了。
显式事务
介绍
事务具有明显的开启和结束的标记，使用显式事务的话，前提需要把 autocommit 关闭
关闭autocommit
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
语法
步骤1：开启事务
set autocommit = 0;
start transaction; # 可选的
步骤2：编写事务中的sql语句
语句1;
语句2;
.............
步骤3：结束事务
commit; 提交事务
rollback; 回滚事务
演示
提交事务：
set autocommit = 0;
start transaction;
update user_info set `password` = 'qqqqqqq' where id = 1;
update user_info set `password` = 'wwwwwww' where id = 2;
commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info where id in (1,2);
+----+-----------+----------+--------+---------------------+---------------------+
| id | user_name | password | status | create_time | update_time |
+----+-----------+----------+--------+---------------------+---------------------+
| 1 | liuyang | qqqqqqq | 1 | 2019-09-07 14:38:47 | 2019-09-07 14:38:47 |
| 2 | zhangsan | wwwwwww | 1 | 2019-09-07 14:38:47 | 2019-09-07 14:38:47 |
+----+-----------+----------+--------+---------------------+---------------------+
2 rows in set (0.06 sec)
回滚事务：
set autocommit = 0;
start transaction;
update user_info set `password` = 'xxxxxxx' where id = 1;
update user_info set `password` = 'aaaaaaa' where id = 2;
rollback;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info where id in (1,2);
+----+-----------+----------+--------+---------------------+---------------------+
| id | user_name | password | status | create_time | update_time |
+----+-----------+----------+--------+---------------------+---------------------+
| 1 | liuyang | qqqqqqq | 1 | 2019-09-07 14:38:47 | 2019-09-07 14:38:47 |
| 2 | zhangsan | wwwwwww | 1 | 2019-09-07 14:38:47 | 2019-09-07 14:38:47 |
+----+-----------+----------+--------+---------------------+---------------------+
2 rows in set (0.08 sec)
由上可见，如果开启了事务，修改命令执行成功后，结果会存在 内存 中，只有commit了之后才会写进磁盘中进行持久化。
同时运行多个事务
对于同时运行多个事务，当这些事务访问 数据库中相同的数据 时，如果没有采取必要的隔离机制，就会导致各种并发问题：
脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还 没有被提交 的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
不可重复读：对于两个事务T1、T2，T1读取了一个字段，然 后 T2 更新了该字段之后，T1再次读取了同一个字段，值就不同了
幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行
数据库事务的隔离性： 数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别： 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱
四种事务隔离级别
| 隔离级别 | 描述 |
|---|---|
| READ UNCOMMITTED （读未提交数据） | 允许事务读取未被其他事务提交的变更，脏读，不可重复读和幻读的问题都会出现 |
| READ COMMITED （读已提交数据） | 只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现 |
| REPEATABLE READ （可重复读） | 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其它事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在 |
| SERIALIZABLE （串行化） | 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，多有并发问题都可以避免，但性能十分低下。 |
Oracle支持2中事务隔离级别：READ COMMITED和SERIALIZABLE。Oracle默认的事务隔离级别为：READ COMMITED
Mysql支持4种事务隔离级别，Mysql默认的事务隔离级别为：REPEATABLE READ
演示事务的隔离级别
脏读
图1
图2
图3：图1 进行了 rollback
图4：再次查询图2
不可重复读
图1
设置数据库事务级别为 read-committed（读已提交数据）
修改数据，让这个事务处于未提交状态
图2
开启一个新事务
图3
图4：再次查询图2
发现数据已经更改，由此说明，read-committed事务中，如果T1事务未提交时查询T2事务中的数据时，避免了脏读，但是如果在T1事务已提交，T2事务再次查询时候，发现上一次查询结果数据已经改变（不可重复读）。由此可见，read-committed事务中也有可能出现脏读情况，只是相比 read-uncommitted事务会好些。
可重复读
图1
图2
由此可见，这个事务级别没有脏读
图3
图4
由此可见，repeatable read事务级别可解决脏读和不可重复读
幻读
图1
图2
图3
解决办法：事务隔离级别修改为最高级（serializabel）,修改为最高级别的话，T1开启事务修改数据，T2如果此时插入数据的话，会阻塞（加了锁，等待状态，如果等待时间长，就会超时），等到T1提交了事务，T2会继续执行。
事务级别总结
| 事务级别 | 脏读 | 幻读 | 不可重复读 |
|---|---|---|---|
| read uncommitted | √ | √ | √ |
| read committed | × | √ | √ |
| repeatable read | × | √ | × |
| serializabel | × | × | × |
命令
# 查看隔离级别
select @@tx_isolation;
# 设置隔离级别【session：当前，global：全局】
set session | global transaction isolation level 隔离级别;
Mysql和Oracle默认事务
Mysql中默认的是 repeatable read；
Oracle中默认的是 read committed
回滚点
savepoint 节点，这是保存点
