MySQL事务
什么是事务
事务是伴随着交易类型的业务场景出现的工作机制，用来保证交易的完整性
例如：A 给 Ｂ 转账100元
开启事务
A账户：- 100元 update操作
B账户：+100元 update操作
提交事务(事务结束)
A 给 B 转账时，要么A扣除100元同时B增加100元，要么A不扣除100元同时B也不增加100元。即转账操作要么同时成功，要么同时失败
事务标准特性(ACID)
A(atomicity)原子性
现实生活：原子构成一般物质的最小单位，在化学反应中不可再分
MySQL：在一个事务工作单元中，所有标准事务语句(DML)，要么全部执行成功，要么全部回滚(回退到事务执行前的状态，DML语句都不执行)
C(consistency)一致性
在事务发生前，中，后都应该保证数据是在始终一致状态
MySQL的各项功能的设计，最终都是要保证数据一致性
如果事务在执行的过程中，MySQL服务器发生故障停止工作，于是DQL语句执行就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，也就是不一致的状态
I(isolation)隔离性
MySQL是可以支持多事务并发工作的系统，在多事务执行的时候，可能有些数据在多个事务中都要进行修改。隔离性确保了，在事务A执行的时候，其他事务就不能更改A事务需要的数据，即并发执行的事务之间不能相互干扰
D(durability)持久性
当事务执行成功(commit成功)后，此次事务操作的所有数据都必然写入到了磁盘中(永久保存)，不会因为MySQL服务器发生故障导致数据丢失
事务生命周期管理
标准事务控制语句
- begin / start transaction 开启事务
- commit 提交事务
- rollback 回滚事务
标准事务语句
在一个事务中通常有多条的DML语句，这些语句要么全部执行成功，要么全部执行失败
- insert
- update
- delete
事务工作流程
mysql> use world
mysql> begin;
mysql> delete from city where id=100;
mysql> rollback;
mysql> select * from city where id=100;
+-----+---------+-------------+------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+---------+-------------+------------+------------+
| 100 | Paraná | ARG | Entre Rios | 207041 |
+-----+---------+-------------+------------+------------+
# 执行rollback语句(回滚操作), 发现delete操作并没有执行成功
mysql> begin;
mysql> delete from city where id=100;
mysql> commit;
mysql> select * from city where id=100;
Empty set (0.00 sec)
# 执行commit语句(提交数据)，发现delete操作执行成功
savepoint 部分回滚
mysql> begin;
mysql> delete from city where id=1000;
mysql> savepoint s1;
mysql> delete from city where id=1001;
mysql> rollback to savepoint s1;
mysql> commit;
mysql> select * from city where id=1000;
Empty set (0.00 sec)
mysql> select * from city where id=1001;
+------+-------+-------------+------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------+-------------+------------+------------+
| 1001 | Depok | IDN | Yogyakarta | 106800 |
+------+-------+-------------+------------+------------+
隐式提交
MySQL默认支持自动提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
mysql> delete from city where id=101;
mysql> select * from city where id=101;
Empty set (0.00 sec)
关闭自动提交（临时生效）
mysql> set @@autocommit=0;
mysql> delete from city where id=102;
mysql> rollback;
mysql> select * from city where id=102;
+-----+---------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+-----+---------+-------------+----------+------------+
| 102 | Posadas | ARG | Misiones | 201273 |
+-----+---------+-------------+----------+------------+
关闭自动提交（永久生效）
vim /etc/my.cnf
[mysqld]
autocommit=0
重启数据库永久生效
在autocommit=1的时候，在执行DML语句时，如果没有加begin(没有显式开启事务)，MySQL会自动在这个DML语句之前加一个begin语句，在执行完这个DML语句后又会自动执行commit语句
autocommit=0，不会自动执行commit语句，一般适合交易类的业务
注意：如果在事务语句中级执行了非标准事务语句，会自动提交非标准事务语句之前的事务语句
交易类的业务解决方案：
autocommit=0，commit手工提交数据autocommit=1，begin,commit都手工执行
隐式回滚
在开启一个事务之后，如果执行过程中，终端断开，MySQL服务器故障，都会触发隐式回滚
事务隔离级别
事务隔离性(transaction_isolation)
- RU(read-uncommitted)：读未提交
- 出现的问题：脏读，不可重复读，幻读
- RC(read-committed)：读已提交
- 出现的问题：不可重复度，幻读
- RR(repeatable-read)：可重复读
- 出现的问题：可能幻读
- SR(serializable)：可串行化
- 将事务串行执行，以上问题都能规避，但是不利于事务并发
这里的读不代表select，而是存储引擎的读，是page的读取
脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据，在生产业务中是不允许的
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读
隔离级别参数修改
查询当前隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
临时修改
mysql> set transaction_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
永久修改，需要修改mysql配置文件
vim /etc/my.cnf
[mysqld]
transaction_isolation="READ-UNCOMMITTED"
重启数据库永久生效
隔离性演示
需要2个会话
脏读
会话1
mysql> begin;
mysql> update city set name='abc' where id=2;
会话2
mysql> select name from city where id=2;
+------+
| name |
+------+
| abc |
+------+
会话1没有执行commit语句，会话2已经读取到了未提交的值，如果会话1发生的回滚操作此时的会话2读取到的数据就是脏数据
不可重复读
会话1
mysql> select name from city where id=10;
+---------+
| name |
+---------+
| Tilburg |
+---------+
会话2
mysql> begin;
mysql> update city set namm='test' where id=10;
mysql> commit;
会话1
mysql> select name from city where id=10;
+---------+
| name |
+---------+
| Tilburg |
+---------+
会话1中的同一次事务，在第二次读取数据时，读取到的是会话2已经修改的数据，此时2次读取结果就不相同
在同一事务内，不同的时刻，读取的数据是不一样的，读取的数据受到了别的事务的影响，违背了事务隔离性原则
幻读
数据准备
mysql> create table t1(
id int primary key auto_increment,
age int,
name varchar(20)
)charset=uttf8mb4;
mysql> insert into t1(age,name) values(10,"a"),(10,"b"),(10,"c"),(20,"d"),(15,"e");
mysql> select * from t1;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 10 | a |
| 2 | 10 | b |
| 3 | 10 | c |
| 4 | 20 | d |
| 5 | 15 | e |
+----+------+------+
会话1
mysql> begin;
mysql> update t1 set age=20 where age<20;
会话2
mysql> begin;
mysql> insert into t1(age,name) values(5,'abc');
mysql> commit;
会话1
mysql> commit;
mysql> select * from t1;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | a |
| 2 | 20 | b |
| 3 | 20 | c |
| 4 | 20 | d |
| 5 | 20 | e |
| 6 | 5 | abc |
+----+------+------+
会话1在执行update之后commit之前，会话2插入了一条数据而且还commit了，此时会话1执行commit后发现仍然有数据没有被更新，会话1还以为是SQL语句写错了，以为产生了幻觉。所以称这种现象为幻读
