MySQL数据库--事务TCL

事务：一个或多个SQL语句组成一个执行单元，这个单元要么全部执行，要么全部不执行。如果执行过程中某条语句执行出错，整个单元都会回滚，返回最开始的状态。

隐式事务：
事务没有明显的开启和结束的标记（如：insert、update、delete）。
显示事务
事务具有明显的开启和结束标记。
前提：必须设置自动提交功能为禁用。

#(1)开启事务
show variables like 'autocommit'; --查看自动提交服务
#或
select @@autocommit;
set autocommit = 0; --关闭自动提交功能（可根据实际情况选择）
start transaction; --开启事务（主要用于标记，其实关闭自动提交就把所有事务变成显示的了，相当于在commit之前事务已经开始，每一句SQL都是事务的一部分）
#(2)编写事务中的SQL语句
#(3)结束事务（提交事务）
rollback; --回滚事务
savepoint 节点名 --保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 --撤销保存点

事务ACID特性

1.原子性(Atomicity)
原子性指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生（要么都成功，要么都失败）。
2.一致性( Consistency)
事务必须使数据库从一个一致性状态变换到另一个一致性状态。（表示事务结束后数据不会因外界原因丢失）
3.隔离性(ISolation)
事务的隔离性是指一个事务的执行不能被其他事物干扰，即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4.持久性(Durabliity)
持久性指一个事务一旦被提交，他对数据库中的数据改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

并发问题

  同时运行多个事务访问数据库中相同的数据时如果没有必要采取隔离机制，就会导致各种并发问题：

• 脏读：对于两个事务T1、T2，T1读取了T2更新但没提交的数据，若T2回滚或提交，T1读取的数据就是临时且无效的。（简单来说，写回磁盘的数据可能是事务提交后的数据，也可能是事务进行中未提交的数据。每次写回磁盘都会建立一个检查点CheckPoint，内存中未写回磁盘的为脏页，事务未提交的数据是脏数据。）
  

• 不可重复读：对于两个事务T1，T2并发执行，T1读取了一个字段，T2更新了该字段并提交后，T1再读取同一个字段，数据就不同了（即T1整个过程中在未更改数据的情况下读取同一条记录不同）。
  

• 幻读：对于两个事务，A在表中读取了一个字段，B在表中插入了一些新的行后，如果A再读取或更改同一个表就会多出几行。
  

  不可重复读重点在于update和delete，而幻读的重点在于insert。

读未提交（read uncommitted）：一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。（基本没用）
读已提交（read committed）：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，但仍然存在不可重复读和幻读问题。
可重复读（repeatable read）：同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但幻读依然存在。
串行化（serializable）：事务串行执行（即事务不可以交替修改数据，）。避免了以上所有问题。但性能较低。
以上是SQL-92标准中定义的四种隔离级别。在MySQL中，默认的隔离级别是REPEATABLE-READ（可重复读），并且解决了幻读问题（Oracle默认的是READ-COMMITTED）。简单的来说，mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。

-- 查看当前隔离级别（MySQL8查询事务应该使用transaction_isolation，tx_isolation在MySQL 5.7.20后被弃用）
select @@transaction_isolation;
-- 设置本次会话的事务隔离级别，只在本会话有效，不会影响到其它会话。设置数据库全局使用global。
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

-- 再次查看发现已改成了read committed
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
-- 再登录其它窗口，再查看，发现还是repeatable read
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
-- 测试
CREATE TABLE account(
id INT(3) NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
money DECIMAL(9,2) NOT NULL,
PRIMARY KEY(id)
);

INSERT INTO account(name, money)
VALUES('A', 2000.00),('B', 1000.00);
-- 模拟转账 事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始事务
UPDATE account SET money = money - 500 WHERE 'name' = 'A'; -- A-500
UPDATE account SET money = money + 500 WHERE 'name' = 'A'; -- A+500
COMMIT; -- 提交事务，持久化数据
ROLLBACK; --  回滚（无效）
SET autocommit = 1; -- 恢复默认值

补充

  事务中使用truncate不可回滚，delete可回滚。