2.1.9 事务基础
2.1.9.1 事务简介
事务 ( Transactions )
事务是一组具有原子性的 SQL 语句，或者说一个独立单元。可以理解为一个事务对应的是一组完整的业务，这个业务有一条或多条 SQL 语句组成。所谓原子性是指，这一组业务中的 SQL 语句不可分割，所以，要么全部 SQL 语句都执行成功，事务也就执行成功；只要有一条 SQL 语句执行失败，则整个事务都要回滚到事务开始前。
记录事务的日志，可以根据此日志实现事务的回滚 (undo) ，重新提交 (redo) 等功能。
事务 仅限于 InnoDB 引擎 下，在 MySQL 中， MyISAM 引擎是不支持事务的。
2.1.9.2 事务特性
事务有 ACID 四个特性
原子性 ( Atomicity )
一致性 ( Consistency )
隔离性 ( Isolation )
持久性 ( Durability )
Transaction 生命周期
2.1.9.3 事务的管理命令
显式启动事务 语法解读
BEGIN
BEGIN WORK
START TRANSACTION
结束事务
# 提交执行
COMMIT
# 回滚
ROLLBACK
准备两个终端 A 和 B ，分别执行如下的命令
mysql > select * from student where id = 11 ;
2.1.9.4 开启事务
注：左边是终端A 右边是终端B 终端A没有提交执行（commit）的时候，终端B没有更新
相关命令如下，如需请复制粘贴
begin; update student set age=30 where id=11;
insert into student (name,age,gender)values('zhangsan',30,'F');
2.1.9.5 提交事务
终端B确认效果 - 数据更新后的效果查看到了
相关命令如下，如需请复制粘贴
commit; select * from student where name = "zhangsan"
select * from student where id = 11 ;
2.1.9.6 事务回滚
结果显示：事务操做过程中移除的数据，又回来了。
delete from student where id<5; select * from student where id<5; rollback;
2.1.10 事务实践
2.1.10.1 谁支持事务操作
只有 事务型存储引擎 中的 DML 语句 才能支持事务操作 （DML语句即insert，update，delete）
truncate 和 drop 都属于 DDL 语句，不支持事务操作。
truncate 命令的工作原理是直接释放表的数据存储空间，因此速度通常比逐行删除（如使用 DELETE 语句）更快，同时也不会记录在事务日志中
drop 语句是 DDL 语句的一种，用于删除数据库对象。具体来说，它可以用于删除数据库、数据表、索引、视图等。
2.1.10.2 自动提交能力
对于 mysql 来说，每一个对于数据的增删改的动作，都是一个事务，为什么我们操作过数据之后，另外的终端可以立刻生效呢？
原因是， mysql 对于这种操作，启用了 自动提交的能力。
MySQL 默认开启了自动提交 命令： select @@autocommit;
关闭自动提交能力 命令：set autocommit =0;
实践 （ 区别和之前的事务：开始没有begin开始）
A 终端，对 student 表里面的数据进行修改
update student set age=36 where id=11;
select * from student where id=11;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 11 | Kevin | 36 | M |
+----+-------+------+--------+
B终端 确认效果 - 依然是旧的数据
select * from student where id=11;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 11 | Kevin | 30 | M |
+----+-------+------+--------+
A终端，提交事务
commit;
B终端确认效果 - 可以看到，数据更新了
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 11 | Kevin | 36 | M |
+----+-------+------+--------+
2.1.10.3 合并提交
MySQL 中，每次写操作都是一个事务操作，批量写时，可以将多次提交合并成一次提交，以加快执行速 度
执行存储过程 1~3分钟
call sp_testlog;
清理数据
drop database testlog;
使用事务的方式创建数据库和插入 100000 条数据
加载脚本环境
source / root / testlog.sql;
以事务方式，合并100000条数据提交，时间减少到了 2.77s
mysql > begin;
Query OK, 0 rows affected ( 0.00 sec)
mysql > call sp_testlog; # 稍微卡顿一下 -- 其实是命令的执行
Query OK, 1 row affected ( 2.77 sec) # 结果显示：才 2.77 秒
mysql > commit;
Query OK, 0 rows affected ( 0.14 sec)
2.1.10.4 事务支持保存点
事务保存点可以只提交部份事务
我们之前所做的事务回滚，只能回滚到最初的状态，而事务支持保存点的能力，能够实现，让我们回滚到特定的动作的那个时间点上。
2.1.10.5 事务保存点事件
准备工作
mysql > use testdb;
mysql > select * from stu1;
Empty set ( 0.00 sec)
结果显示： 这里面没有数据
开始执行事务操作
begin ;
尝试插入数据 - 增加保存点能力
insert into stu1 (name,age,gender)values( 'u1' , 11 , 'M' );
savepoint p1; # 保存回滚点 p1
insert into stu1 (name,age,gender)values( 'u2' , 22 , 'M' ); # 插入数据
savepoint p2; # 保存回滚点 p2
insert into stu1 (name,age,gender)values( 'u3' , 33 , 'M' ); #插入数据
savepoint p3; # 保存回滚点 p3
select * from stu1; # 未提交状态
rollback to p2; # 回滚至保存点 p2
commit; # 提交
终端 2 查看事务提交后的效果 -- 只提交了一部分的数据
select * from stu1; # 只提交了部份数据
2.1.10.6 查看事务
# 查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
以下两张表在MySQL8.0中已取消#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; B 终端执行一个事务动作
begin ;
insert into stu1 (name,age,gender)values( 'u4' , 23 , 'M' );
当 B 终端执行 insert 语句之后，回到 A 终端后，查看效果
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \ G
B 终端提交事务 commit;
A 终端后，查看效果
2.1.10.7 死锁简介
死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等的进程称为死锁进程。
死锁并不是 MySQL 中独有的，只要出现资源互相竞争的情况都有可能出现死锁。
在 MySQL 中出现死锁时， MySQL 服务会自行处理，自行回滚一个事务，防止死锁的出现。
死锁语法解析
查看 innodb 状态，可以查看锁信息
show engine innodb status;
查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;
查看锁
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM performance_schema.data_locks; #MySQL8 .0.13 及以后使用此语句查看锁
查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.data_lock_waits; #MySQL8 .0.13 及以后查看锁
查看事务锁的超时时长，默认 50s, 也就是说，超过 50 秒之后，事务会自动断开，事务过程中的操作也会自动消失
show global variables like 'innodb_lock_wait_timeout' ;
创建死锁环境
终端 A ，创建一个事务，提交一个语句
mysql > \ r # 查看当前的会话
mysql > begin ;
mysql > update student set age = 1 where id = 10 ;
终端 B ，创建一个事务，提交一个语句
mysql > \ r # 查看当前的会话
mysql > begin ;
mysql > update student set age = 2 where id = 12 ; # id 不一样
找到死锁会话并杀死
新开一个终端 C 查看锁效果 -- 可以确认是 171 和 172 两个会话出现死锁的情况
show engine innodb status \ G
在 C 终端里面，查看正在进行的事务
SELECT * FROM information_schema.INNODB_TRX\G
# 查看所有线程
show processlist\G
# 杀掉未完成的事务
mysql> kill 171 ;
mysql> kill 172 ;
确认 171 和 172 会话里面执行的信息是否清理
2.1.11 事务隔离
2.1.12.1 事务隔离级别
MySQL 的事务隔离级别一共有四个，分别是 读未提交 、读已提交、 可重复读 以及 可串行化。 MySQL的隔离级别的作用就是让事务之间互相隔离，互不影响，这样可以保证事务的一致性。
读未提交（READ UNCOMMITTED） 你没有提交的时候，我就看到你修改的数据。
读已提交（ READ COMMITTED） 你提交后，我才可以看到你修改的数据。
可重复读（ REPEATABLE READ） 你提交了，我也提交了，我才有能力读自己的数据。
可串行化（ SERIALIZABLE） 两个人，谁先开始【另外一个人处于等待状态】，谁后开始的问题。
以上四种隔离级别，从上往下，隔离强度逐渐增强，性能逐渐变差，需要消耗的 MySQL 的资源越多， 所以并不是隔离强度越高越好，采用哪种隔离级别要根据系统需求权衡决定，MySQL 中默认的隔离级 别是 可重复读 。
2.1.12.2 事务并发中可能出现的问题
脏读（Dirty Read） - 只在 读未提交 隔离级别才会出现
不可重复读（Non-Repeatable Read） - 在 读未提交 和 读已提交 隔离级别都可能会出现
幻读（Phantom）-在 读未提交、读已提交、可重复读 隔离级别都可能会出现
2.1.12.3 MVCC和事务的隔离级别
MVCC 和 事务的隔离级别
MVCC （多版本并发控制机制）只在 读已提交 和 可重复读 两个隔离级别下工作。
其他两个隔离级别都和 MVCC 不兼容，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。
MySQL8.0 及以后 , 使用 @@transaction_isolation 查看隔离级别
mysql > select @ @transaction_isolation ;
MySQL8.0 以前及 Mariadb, 使用 @@tx_isolation 属性查看
2.1.12.4 指定事务隔离级别方式
关于事务隔离级别的指定，可在 SESSION 和 GLOBAL 级别进行设置
#MySQL8.0之前及MariaDB
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
#MySQL8.0及以后
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE' 也可以在服务器选项中指定
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
transaction-isolation = SERIALIZABLE
2.1.12.5 事务隔离级别的修改实践
修改事务隔离级别，查看未提交的事务中的数据
隔离级修改为 读未提交
A 终端开启事务操作
mysql > begin ;
mysql > update student set age = 33 where id = 10 ;
B 终端，可以立刻看到， A 终端事务未提交的数据
mysql> select * from student where id = 10 ;
A 终端回退事务
mysql > rollback;
B 终端，可以立刻看到， A 终端事务回退后的数据
mysql> select * from student where id = 10 ;
