主流数据库之MySQL的事务控制

文章目录

前言

本文讲了有关MySQL事务控制的知识，明天清明节，祝大家清明节快乐呀！另外，走过路过的小伙伴们点个赞和关注再走吧，欢迎评论区交流，一起努力一起成长！

笔芯

4.5、MySQL事务控制

事务的概念

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务是由一系列相关的SQL语句组成的最小逻辑工作单元。对于事务中的每一个操作要么全部完成，要么全部不执行。（举个例子：银行转账这个事务分两步，一个账户的资金减少、另一个账户的资金增加。财产总额应该是不变的。所以不能只执行其中任何一个）

ACID****四个特性

原子性(Atomicity):事务是原子的，事务中包含的所有SQL语句组成一个不可分割的工作单元，要么所有SQL语句全部完成或者全部不完成。

一致性(Consistency):事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态（数据库的完整性没被破坏）。

隔离性(lsolation):数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。防止了多个事务并发执行时交叉执行导致数据的不一致。

持久性(Durability): 一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。

事务的分类

事务按照定义方式不同可分为如下两类:

自动提交事务

MySQL默认采用此模式运行。当执行一个用于修改表数据的语句后，即使没有用户定义事务，MySQL也会立刻将结果存储到磁盘中，称为自动提交事务。每个MySQL语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句;如果遇到错误，则回滚该语句的操作。

显式事务

是指显式定义了启动(start transaction | begin work)和结束(commit或rollback work )的事务。在实际应用中，大多数的事务是由用户来定义的。事务结束分为提交(commit)和回滚(rollback)两种状态。事务以提交状态结束，将操作结果提交到数据库中。事务以回滚的状态结束，则将事务的操作被全部取消，事务操作失败。

事务的基本操作

在前边的文章中数据库的基础中也有提到过事务控制的基本操作有四个，分别是开启事务，提交事务，事务回滚，保存还原点。以下为相关描述和事务控制语句。

关闭自动提交

MySQL系统变量@@autocommit值默认为1，自动提交功能默认打开。一个语句自动提交后这个事务就结束了。就所以为了能操作由多条SQL语句组成的事务。用户必须关闭自动提交。

关闭自动提交有以下两种方式:

1、执行下面的语句，执行后，必须明确地指示每个事务的终止，事务中的SQL语句对数据库所做的修改才能成为持久化修改。

set @@autocommit=0;

2、使用MySQL命令“start transaction;”或“begin;”可以隐式的关闭自动提

交。隐式的关闭自动提交不会修改系统会话变量@@autocommit的值。

启动事务

START TRANSACTION;

或者BEGIN;

事务提交

COMMIT;

注意:MySQL不允许嵌套的事务。在第一个事务里使用start transaction命令后，当第二个事务开始时，会自动地提交第一个事务。以下MySQL语句运行时都会执行一个commit命令:drop database / drop table /create index/ drop index/alter table /rename table /lock tables / unlock tables /set @@autocommit=1

事务回滚

回滚会结束用户的事务，并撤销正在进行的所有未提交的修改.

ROLLBACK;

或者ROLLBACK WORK;

设置保存点

使事务回滚到某个点，实现事务的部分回滚。允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

SAVEPOINT 保存点名称;

删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

RELEASE SAVEPOINT 保存点名称;

向保存点回滚事务

ROLLBACK TO SAVEPOINT保存点名称;

注意：如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚过程中被回滚。当事务回滚到某个保存点后，在该保存点之后设置的保存点将被删除。

示例：

事务回滚

START TRANSACTION;

INSERT INTO department VALUES(70,'name70','loc70');

INSERT INTO department VALUES(80,'name80','loc80');

ROLLBACK;

SELECT*FROM department;

事务保存点提交回滚事务

START TRANSACTION;

INSERT INTO department VALUES(70,'name70','loc70');

SAVEPOINT sp1;

INSERT INTO department VALUES(80,'name80','loc80');

UPDATE department SET loc='loccc'WHERE deptno=70;

ROLLBACK TO sp1;

SELECT *FROM department;

事务的并发处理

MySQL支持多用户共享同一数据库，但当多用户同时操作同一数据时，会产生一定的并发问题。

事务可以保证数据的完整性和一致性，然而同一数据库中多个事务并发操作同一数据时，就可能产生数据的不一致问题。

数据库的并发操作导致的数据库的不一致主要有4种:

丢失更新

指当两个或多个事务选择同一行，然后根据最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，因此最后的更新将覆盖由其他事务所做的更新，从而导致数据丢失。

脏读

指一个事务正在访问数据，而其他事务正在更新该数据，但尚未提交，此时第一个事务所读取的数据就是“脏”(不正确）数据。脏读问题违背了事务的隔离性原则。

不可重复读

指同一个事务内两条相同的查询语句，查询结果不一致。即当一个事务多次访问同一行且每次读取不同数据时，会出现不可重复读问题。因为其他事务可能正在更新该事务正在读取的数据。

幻读

当对某行执行插入或删除操作，而该行属于某事务正在读取的行的范围时，就会出现幻读问题。由于其他事务的删除操作，使事务第一次读取行范围时存在的行在后续读取时已不存在。与此类似，由于其他事务的插入操作，后续读取显示原来读取时并不存在的行。

事务的隔离级别

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。以下四种隔离级别逐渐增强。

READ UNCOMMITTED:

所有事务都可以看到其他未提交事务的执行结果，很少用于实际应用，并且它的性能也不比其他隔离级别好多少。

READ COMMITTED:

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的)。它满足了隔离的简单定义，即一个事务只能看见已提交事务所做的改变。

REPEATABLE READ:

MySQL默认的事务隔离级别，它确保同一事务内相同的查询语句执行结果一致。

SERIALIZABLE:

通过强制事务排序，使之不可能相互冲突。它会在每条select语句后自动加上lock in sharemode，为每个查询操作施加一个共享锁。有可能导致大量的锁等待现象。该隔离级别主要用于InnoDB存储引擎的分布式事务。

注意：低级别的事务隔离可以提高事务的并发访问性能，但可能导致较多的并发问题（如脏读、不可重复读、幻读等并发问题);高级别的事务隔离可以有效避免并发问题，但会降低事务的并发访问性能，可能导致出现大量的锁等待、甚至死锁现象。