mysql --8事务
事务
一、概念
transaction
是一个完整的业务逻辑，是一个最小的工作单元，不可再分
1.2、涉及语句：DML语句
insert、delete、update 三个语句和事务有关系
以上三个语句是数据库表中的数据进行增删改的
涉及语句安全
1.3、存在事务的原因
因为业务中需要多条DML语句共同联合起来才能完成，如果复杂业务能通过一条DML语句实现，那么事务则 没有存在的价值
1.4、本质
批量的DML语句（多条DML语句）同时成功，或者失败
1.5、如何实现多条DML语句同时成功或失败
innodb 提供一组用来记录事务性活动的日志文件
提交或者回滚事务都会清空事务性活动的日志文件，只是一个是全部成功，一个是全部失败
1.6、事务分类
提交事务：commit
回滚事务：rollback
1.7、默认事务
默认事务为commit (提交事务)
关闭默认提交事务:start transaction;
-- 默认提交事务
mysql> desc tb12;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into tb12(name,age)values('张三',15);
Query OK, 1 row affected (0.06 sec)
mysql> insert into tb12(name,age)values('李四',15);
Query OK, 1 row affected (0.06 sec)
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
+----+--------+------+
2 rows in set (0.00 sec)
-- 使用start transaction 后选择commit与rollback
-----------------回滚事务-----------------------
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb12(name,age)values('吴邪',15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb12(name,age)values('老痒',15);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
| 4 | 吴邪 | 15 |
| 5 | 老痒 | 15 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
+----+--------+------+
2 rows in set (0.00 sec)
---------------------提交事务-----------------------------
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> insert into tb12(name,age)values('吴邪',15);
Query OK, 1 row affected (0.06 sec)
mysql> insert into tb12(name,age)values('老痒',15);
Query OK, 1 row affected (0.07 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
| 6 | 吴邪 | 15 |
| 7 | 老痒 | 15 |
+----+--------+------+
4 rows in set (0.00 sec)
mysql> rollback; -- 回滚无效
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 张三 | 15 |
| 3 | 李四 | 15 |
| 6 | 吴邪 | 15 |
| 7 | 老痒 | 15 |
+----+--------+------+
4 rows in set (0.00 sec)
1.8 事务的4个特性（acid）
- 原子性（Atomicity）
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
- 一致性（Consistency）
- 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。
- 隔离性（Isolation）
- 隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
- 要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务不可见。 也可以理解为多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。例如一个用户在更新自己的个人信息的同时，是不能看到系统管理员也在更新该用户的个人信息（此时更新事务还未提交）。
- 持久性（Durability）
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。
1.9、并发情况出现的问题
在数据库操作中，在并发的情况下可能出现如下问题：
- 更新丢失（Lost update）
如果多个线程操作，基于同一个查询结构对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了。
第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。
解决方法：对行加锁，只允许并发一个更新事务。 - 脏读（Dirty Reads）
脏读（Dirty Read）：A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。
解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。 - 不可重复读（Non-repeatable Reads）
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。 - 幻象读
指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为 游标稳定性 的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。
解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。
正是为了解决以上情况，数据库提供了几种隔离级别。
2.事务隔离级别
2.1、读未提交：read uncommit （未提交读到数据）（最低的隔离级别）
概念
该隔离级别的事务会读到其它未提交事务的数据，此现象也称之为 脏读 。
这种隔离事务一般都是理论上的，大多数的数据库隔离级别都是二挡起步！
2.2、读已提交： read committed（提交读到数据）
用途与价值
事务A只能读取到事务B提交后的数据，解决了脏读的现象
存在问题
这种隔离级别存在什么问题：不可重复读取数据
不可重复读取数据：在事务开启后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取 的时候，读到的数据是4条，3不等于4，称为不可重复读取
每一次读到的数据是绝对真实，oracle数据库默认的隔离级别是：read committed
2.3可重复读 ： repeatable read （提交之后也读不到）
mysql默认隔离级别
概念
事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B 将数据已经修改，并 且提交了，事务A读取到的数据没有发生改变，只有A事务结束提交了，之后才能读到数据
用途与价值
解决了不可重复读取数据
存在问题：
每一次读取的数据都是幻想，不够真实
2.4序列化/串行化： serializable （最高的隔离级别）
这是最高隔离级别，效率最低，解决了所有问题
这种隔离级别表示事务排队，不能并发！
线程同步（事务同步）
每一次读取的数据都是最真实的，并且效率是最低的
3.事务隔离级别验证
-- 查看事务隔离级别
老版本mysql用的是select @@global.tx_isolation；
8.0版本语句：select @@global.transaction_isolation;
默认级别为 REPEATABLE-READ（可重复读）
-- 设置事务隔离级别
set global transaction isolation level （read uncommitted）;
3.1读未提交
--1. 设置事务隔离级别为read uncommitted;
窗口A
3.mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
4.mysql> insert into t12(name,age) values('lis',56);
Query OK, 1 row affected (0.00 sec)
窗口B
这里窗口B需要新开一个
2.1.mysql> start transaction;
2.2.mysql> select * from t12; 先行验证未操作时t12表的情况
5.mysql> select * from t12; 再行验证发现窗口A未提交数据
3.2、读已提交
--1. 设置事务隔离级别为read committed;
窗口A
3.mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
4.mysql> insert into t12(name,age)value('张三',15);
Query OK, 1 row affected (0.00 sec)
6.mysql> commit; 提交
Query OK, 0 rows affected (0.07 sec)
窗口B
新开窗口B(或者退出重进)
2.1.mysql> start transaction;
2.2.mysql> select * from t12;
Empty set (0.00 sec)
5.mysql> select * from t12; 未提交，读取不到
Empty set (0.00 sec)
7.mysql> select * from t12; 提交后，读取
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 15 |
+----+--------+------+
3.3、可重复读（幻读）
窗口A
1.mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
2.mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
4.mysql> insert into t12(name,age)values('吴邪',23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t12(name,age)values('吴一穷',46);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t12(name,age)values('吴二白',43);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t12(name,age)values('吴三省',40);
Query OK, 1 row affected (0.00 sec)
mysql> delete from t12 where name='张三';
Query OK, 1 row affected (0.00 sec)
5.mysql> commit; 提交
Query OK, 0 rows affected (0.10 sec)
7.mysql> SELECT * FROM T12; 可以看到当前窗口依旧变更数据
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 吴邪 | 23 |
| 3 | 吴一穷 | 46 |
| 4 | 吴二白 | 43 |
| 5 | 吴三省 | 40 |
+----+-----------+------+
4 rows in set (0.00 sec)
窗口B
1.mysql> use db2;
Database changed
2.mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
3.mysql> select * from t12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 15 |
+----+--------+------+
1 row in set (0.00 sec)
4.mysql> select * from t12; 出现幻读的情况
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 15 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from t12;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 15 |
+----+--------+------+
6.mysql> select * from t12; 可以看到窗口A提交了也不能查询到新内容
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 15 |
+----+--------+------+
1 row in set (0.00 sec)
8.退出当前mysql,重进即可
mysql> use db2
Database changed
mysql> select * from t12;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 吴邪 | 23 |
| 3 | 吴一穷 | 46 |
| 4 | 吴二白 | 43 |
| 5 | 吴三省 | 40 |
+----+-----------+------+
4 rows in set (0.00 sec)
3.4序列化/串行化
事务排队
窗口A
1.mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
2.退出后
2.mysql> use db2;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
3.mysql> select * from t12;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 吴邪 | 23 |
| 3 | 吴一穷 | 46 |
| 4 | 吴二白 | 43 |
| 5 | 吴三省 | 40 |
+----+-----------+------+
4 rows in set (0.00 sec)
4.mysql> insert into t12(name,age)values('张起灵',11);
Query OK, 1 row affected (0.00 sec)
6.mysql> commit; 提交后
Query OK, 0 rows affected (0.10 sec)
窗口B
1.重启后
2.mysql> use db2;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
5.mysql> select * from t12; 需要等待窗口A完成才能
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
7.mysql> select * from t12; 窗口A提交后B窗口能查询数据，并且能读取插入的数据
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 吴邪 | 23 |
| 3 | 吴一穷 | 46 |
| 4 | 吴二白 | 43 |
| 5 | 吴三省 | 40 |
| 7 | 张起灵 | 11 |
+----+-----------+------+
5 rows in set (0.00 sec)
