八、MYSQL8数据库事务

MYSQL事务简介

事务就是一组sql要么全部执行，要么全部不执行。
在MYSQL中只有InnoDB存储引擎支持事务。

事务：有开始，有结束，以第一条DML语句开始，经历N条DML语句，以commit或rollback结束。

事务的特性

事务具有 4 个特性，即:

• 原子性（Atomicity）：所有的语句要么全部执行，要么全部不执行
• 一致性（Consistency）：系统从一个正确的状态，迁移到另一个正确的状态
• 隔离性（Isolation）：一个事务不能被另一个事务影响，事务提交钱对其他事务不可见
• 持久性（Durability）：事务一旦提交，则其结果是永久性的

这 4 个特性通常简称为 ACID。

1. 原子性

事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。

2. 一致性

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

3. 隔离性

事务控制的隔离性越高，数据一致性越好，并发性越差。

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。

4. 持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

MYSQL事务的手动演示操作步骤

1. 开启事务

-- 查看自动自动状态
SELECT @@autocommit;

-- 设置MYSQL的事务为手动提交(关闭自动提交)
-- 临时关闭自动提交
set autocommit=0;

-- 临时开启自动提交(Mysql默认已开启)
set autocommit=1;

1.1.执行SQL语句

-- 模拟账户转账,章三向李四转账200元
-- 开启事务
BEGIN
UPDATE account set money=money-200 WHERE name='章三';
UPDATE account set money=money+200 WHERE name='李四';

2.提交事务

-- 提交事务
commit;

3. 回滚事务(失败时)

-- 回滚事务
rollback;

示例代码：提交失败自动回滚事务

示例中，SAVEPOINT 语句创建了一个起点以用于部分回滚。由于MySQL 8支持部分回滚和嵌套事务，因此使用 SAVEPOINT 可以更好地控制事务。

接下来，使用 DECLARE EXIT HANDLER FOR SQLEXCEPTION 声明了一个异常处理程序，以确保在发生异常时能够回滚事务。然后，通过 SELECT ... FOR UPDATE 语句获取账户1的余额并锁定该行数据以防止其他用户访问该行数据。如果账户1的余额不足，则回滚事务。否则，执行两个 UPDATE 语句，将账户1的余额减去100元并将该金额转移到账户2中。

最后，在提交事务前使用 RELEASE SAVEPOINT 释放保存点。这是一个良好的编程实践，因为可以随时在事务中建立多个保存点，并根据需要进行回滚。

-- 开始事务
START TRANSACTION; 

-- 保存起点以用于部分回滚
SAVEPOINT start_transaction;


BEGIN
  -- 声明异常处理程序
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
    
    -- 回滚到事务起点
    ROLLBACK TO start_transaction; 
  END;

  -- 获取账户1的余额并锁定该行数据以防止其他用户访问该行数据
  SELECT @balance := balance FROM accounts WHERE id = 1 FOR UPDATE; 
  
  -- 如果余额不足，则回滚事务
  IF (@balance < 100) THEN 
    ROLLBACK TO start_transaction;
  END IF;
  
  -- 将账户1的余额减去100元
  UPDATE accounts SET balance = balance - 100 WHERE id = 1; 
  -- 将账户2的余额增加100元
  UPDATE accounts SET balance = balance + 100 WHERE id = 2; 
  
  -- 提交事务
  COMMIT; 
END;

-- 释放保存点
RELEASE SAVEPOINT start_transaction; 

隐式提交语句

一致性问题

事务的隔离级别

MySQL的默认隔离级别是可重复读(Repeatable read)

1. 读未提交（Read Uncommitted）：在该级别下，事务可以读取到其他事务未提交的数据。这种隔离级别没有提供多少保护，因为其他事务所做的修改可能会影响到当前事务中的查询结果。
2. 读已提交（Read Committed）：在该级别下，事务只能读取到已经提交的数据，这样可以避免脏读的问题。但是，由于其他事务可以在当前事务执行期间提交新的数据，因此可能出现不可重复读和幻读的情况。
3. 可重复读（Repeatable Read）：在该级别下，事务可以多次读取相同的数据，并且可以确保在整个事务期间读取到的数据相同。但是，由于其他事务可以在当前事务执行期间插入新的数据，因此仍然会有幻读的情况发生。
4. 串行化（Serializable）：在该级别下，事务被完全隔离开来，相当于每个事务都在独立的时间运行。在该级别下，没有并发问题，但是可能会导致性能问题和锁等待。

不同隔离级别的特点

事务隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	是	是	是
读已提交（Read Committed）	否	是	是
可重复读（Repeatable Read）	否	否	是
串行化（Serializable）	否	否	否

事务的隔离级别常用操作

查看隔离级别

-- 查看隔离级别
show variables like '%isolation%';

设置隔离级别

-- 设置隔离级别
set session TRANSACTION isolation level 级别字符串;

设置–读未提交(read uncommitted)

-- 设置read uncommitted
-- 设置隔离级别会引起藏读，A事务读取到B事务没有提交的数据
set session TRANSACTION isolation level read uncommitted;

设置–读已提交(read committed)

-- 设置read committed
-- 这种隔离级别会引起不克重复读，A事务在没有提交事务之前，克看到数据不一致
set session TRANSACTION isolation level read committed;

设置–可重复读(repeatable read)–(MySQL默认的)

-- 设置repeatable read(MySQL默认的)
-- 这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致
set session TRANSACTION isolation level repeatable read;

设置–串行化(serializable)

-- 设置serializable
-- 这种隔离界别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。
set session TRANSACTION isolation level serializable;