八、MYSQL8数据库事务MYSQL事务简介事务的特性1. 原子性2. 一致性3. 隔离性4. 持久性MYSQL事务的手动演示操作步骤1. 开启事务1.1.执行SQL语句2.提交事务3. 回滚事务(失败时)示例代码提交失败自动回滚事务隐式提交语句一致性问题事务的隔离级别不同隔离级别的特点事务的隔离级别常用操作查看隔离级别设置隔离级别设置--读未提交(read uncommitted)设置--读已提交(read committed)设置--可重复读(repeatable read)--(MySQL默认的)设置--串行化(serializable)MYSQL事务简介事务就是一组sql要么全部执行要么全部不执行。在MYSQL中只有InnoDB存储引擎支持事务。事务有开始有结束以第一条DML语句开始经历N条DML语句以commit或rollback结束。事务的特性事务具有 4 个特性即:原子性Atomicity所有的语句要么全部执行要么全部不执行一致性Consistency系统从一个正确的状态迁移到另一个正确的状态隔离性Isolation一个事务不能被另一个事务影响事务提交钱对其他事务不可见持久性Durability事务一旦提交则其结果是永久性的这 4 个特性通常简称为ACID。1. 原子性事务是一个完整的操作。事务的各元素是不可分的原子的。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败则整个事务将失败。以银行转账事务为例如果该事务提交了则这两个账户的数据将会更新。如果由于某种原因事务在成功更新这两个账户之前终止了则不会更新这两个账户的余额并且会撤销对任何账户余额的修改事务不能部分提交。2. 一致性当事务完成时数据必须处于一致状态。也就是说在事务开始之前数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态如数据可能有部分被修改。然而当事务成功完成时数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据或者说事务不能使数据存储处于不稳定的状态。以银行转账事务事务为例。在事务开始之前所有账户余额的总额处于一致状态。在事务进行的过程中一个账户余额减少了而另一个账户余额尚未修改。因此所有账户余额的总额处于不一致状态。事务完成以后账户余额的总额再次恢复到一致状态。3. 隔离性事务控制的隔离性越高数据一致性越好并发性越差。对数据进行修改的所有并发事务是彼此隔离的这表明事务必须是独立的它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据或者在另一个使用相同数据的事务结束之后访问这些数据。另外当事务修改数据时如果任何其他进程正在同时使用相同的数据则直到该事务成功提交之后对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账永远是相互独立的。4. 持久性事务的持久性指不管系统是否发生了故障事务处理的结果都是永久的。一个事务成功完成之后它对数据库所作的改变是永久性的即使系统出现故障也是如此。也就是说一旦事务被提交事务对数据所做的任何变动都会被永久地保留在数据库中。MYSQL事务的手动演示操作步骤1. 开启事务-- 查看自动自动状态SELECTautocommit;-- 设置MYSQL的事务为手动提交(关闭自动提交)-- 临时关闭自动提交setautocommit0;-- 临时开启自动提交(Mysql默认已开启)setautocommit1;1.1.执行SQL语句-- 模拟账户转账,章三向李四转账200元-- 开启事务BEGINUPDATEaccountsetmoneymoney-200WHEREname章三;UPDATEaccountsetmoneymoney200WHEREname李四;2.提交事务-- 提交事务commit;3. 回滚事务(失败时)-- 回滚事务rollback;示例代码提交失败自动回滚事务示例中SAVEPOINT语句创建了一个起点以用于部分回滚。由于MySQL 8支持部分回滚和嵌套事务因此使用SAVEPOINT可以更好地控制事务。接下来使用DECLARE EXIT HANDLER FOR SQLEXCEPTION声明了一个异常处理程序以确保在发生异常时能够回滚事务。然后通过SELECT ... FOR UPDATE语句获取账户1的余额并锁定该行数据以防止其他用户访问该行数据。如果账户1的余额不足则回滚事务。否则执行两个 UPDATE 语句将账户1的余额减去100元并将该金额转移到账户2中。最后在提交事务前使用RELEASE SAVEPOINT释放保存点。这是一个良好的编程实践因为可以随时在事务中建立多个保存点并根据需要进行回滚。-- 开始事务STARTTRANSACTION;-- 保存起点以用于部分回滚SAVEPOINTstart_transaction;BEGIN-- 声明异常处理程序DECLAREEXITHANDLERFORSQLEXCEPTIONBEGIN-- 回滚到事务起点ROLLBACKTOstart_transaction;END;-- 获取账户1的余额并锁定该行数据以防止其他用户访问该行数据SELECTbalance:balanceFROMaccountsWHEREid1FORUPDATE;-- 如果余额不足则回滚事务IF(balance100)THENROLLBACKTOstart_transaction;ENDIF;-- 将账户1的余额减去100元UPDATEaccountsSETbalancebalance-100WHEREid1;-- 将账户2的余额增加100元UPDATEaccountsSETbalancebalance100WHEREid2;-- 提交事务COMMIT;END;-- 释放保存点RELEASESAVEPOINTstart_transaction;隐式提交语句一致性问题事务的隔离级别MySQL的默认隔离级别是可重复读(Repeatable read)读未提交Read Uncommitted在该级别下事务可以读取到其他事务未提交的数据。这种隔离级别没有提供多少保护因为其他事务所做的修改可能会影响到当前事务中的查询结果。读已提交Read Committed在该级别下事务只能读取到已经提交的数据这样可以避免脏读的问题。但是由于其他事务可以在当前事务执行期间提交新的数据因此可能出现不可重复读和幻读的情况。可重复读Repeatable Read在该级别下事务可以多次读取相同的数据并且可以确保在整个事务期间读取到的数据相同。但是由于其他事务可以在当前事务执行期间插入新的数据因此仍然会有幻读的情况发生。串行化Serializable在该级别下事务被完全隔离开来相当于每个事务都在独立的时间运行。在该级别下没有并发问题但是可能会导致性能问题和锁等待。不同隔离级别的特点事务隔离级别脏读不可重复读幻读读未提交Read Uncommitted是是是读已提交Read Committed否是是可重复读Repeatable Read否否是串行化Serializable否否否事务的隔离级别常用操作查看隔离级别-- 查看隔离级别showvariableslike%isolation%;设置隔离级别-- 设置隔离级别setsessionTRANSACTIONisolationlevel级别字符串;设置–读未提交(read uncommitted)-- 设置read uncommitted-- 设置隔离级别会引起脏读A事务读取到B事务没有提交的数据setsessionTRANSACTIONisolationlevelreaduncommitted;设置–读已提交(read committed)-- 设置read committed-- 这种隔离级别会引起不克重复读A事务在没有提交事务之前克看到数据不一致setsessionTRANSACTIONisolationlevelreadcommitted;设置–可重复读(repeatable read)–(MySQL默认的)-- 设置repeatable read(MySQL默认的)-- 这种隔离级别会引起幻读A事务在提交之前和提交之后看到的数据不一致setsessionTRANSACTIONisolationlevelrepeatableread;设置–串行化(serializable)-- 设置serializable-- 这种隔离界别比较安全但是效率低A事务操作表时表会被锁起B事务不能操作。setsessionTRANSACTIONisolationlevelserializable;