sql语法：事务的”那些事“

Mysql版本：8.0.26
可视化客户端：sql yog

---

---

前言

随着人工智能与大数据的不断发展，数据库的管理与维护这门技能也越来越重要，很多人都开启了学习sql语言的相关知识，本文简要介绍了数据库sql语言中的事务部分内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、事务是什么？

👉事务是指一组sql操作，要么起成功后提交，要么一起失败后回滚

例如:

张三账号余额是500，李四账号余额是500。
张三要给李四转账，转100元。
张三的余额减少100，李四的余额要增加100。

👉这两个操作要么一起成功，要么一起失败。

一起成功:
张三账号余额是400，李四账号余额是600

不允许出现:
张三 账号余额是400，李四 账号余额是500

不允许出现:
账号余额是500张三，李四账号余额是600

一起失败：
张三余额500，李四余额500

二、事务的特点

👉1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

👉2、事务的ACID属性：

（1）原子性（Automicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

举例说明：

在上面的案例中，张三给李四转账的过程中，张三的余额少100，而李四的余额多100，这余额的一赠一减的两个操作时不可再分，即必须一起进行。

（2）一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

举例说明：

在上面的案例中，张三给李四转账的过程中，张三对李四转账的结果有两种：即要么转账失败，要么转账成功。但是不管转账的结果如何，张三和李四的余额总和必须保持不变。
转账成功的话，张三的余额减少100，还剩400，李四的余额增加100，余额为600，他们两个人的钱加起来是1000；转账失败的话，张三和李四的钱都不变，相加之和为1000。

（3）隔离性（Isolation）:事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

举例说明：

在上面的案例中，无论张三与李四的转账过程中出现什么问题，成也好，败也罢。都不能影响张三对另一个人王五的转账操作。

（4）持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

举例说明：

在上面的案例中，一旦张三转账成功了，李四成功到账，钱就撤不回来了。

三、如何提交事务和回滚事务?

提交事务: COMMIT
回滚事务: ROLLBACK

👉事务回滚是什么意思？

数据库滚回到事务开始时的状态。简单来说，就是当你删除某个表中一行或多行记录后，突然不想删了，回滚一下，刚才删掉的数据又回来了。

测试数据准备如下：

CREATE TABLE `user_profile` (
  `id` int DEFAULT NULL,
  `device_id` int DEFAULT NULL,
  `gender` varchar(14) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `university` varchar(32) DEFAULT NULL,
  `province` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

举例：删除id为5的记录，尝试回滚

回滚之前：

代码演示如下：

DELETE FROM user_profile WHERE id=5;
ROLLBACK;

回滚了，没用！！！

👉原因分析：
mysql中默认事务是自动提交的。 如果需要手动提交和回滚，那么需要设置手动提交模式

要注意，每次操作完之后，要记得手动commit，否则就没有正式的生效。

3.1 手动提交

👉语法：

SET autocommit=FALSE; #设置手动提交模式
#下面的语句仍然是自动提交模式

案例：还是刚才上面的案例需求，删除id为4的记录，尝试回滚

回滚之前：

代码演示如下：

SET autocommit=FALSE;
DELETE FROM user_profile WHERE id=4;
ROLLBACK;

3.2 自动提交模式下开启事务

虽然MySQL默认还是自动提交模式，但是我们可以临时开启一个事务去模拟实现上述操作。

👉语法：

START TRANSACTION;
	这一段sql需要手动处理提交或回滚
ROLLBACK / COMMIT;
下面的语句仍然是自动提交模式

案例：开启事务，删除id为4的记录，尝试回滚

代码演示如下：

SET autocommit = TRUE;#重新设置为自动提交模式
START TRANSACTION;#开始事务
	DELETE FROM user_profile WHERE id = 4;
ROLLBACK;#回滚
下面的语句仍然是自动提交模式

3.3 注意事项

所有的DDL语句，不支持事务。
CREATE ,DROP,TRUNCATE,ALTER 等语句不支持事务
只有 INSERT,UPDATE, DELETE 支持事务。
select不影响数据，所以是否支持事务没什么问题

不信？

案例：开启事务，尝试创建表emp，然后回滚

代码演示如下：

START TRANSACTION;

CREATE TABLE emp(
     id INT
     );
     
ROLLBACK;

备注：
👉删除整个表的数据有两种方式:

• DELETE FROM 表名称;
• TRUNCATE 表名称;截断表

👉区别:
(1) delete效率没有truncate高因为delete是一条语一条语句的除的，而truncate是把整个表drop表，新建一张新表。
(2) delete支持事务，而truncate不支持事务。

---

四、事务的隔离级别

👉数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。**一个事务与其他事务隔离的程度称为隔离级别。**数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

• 脏读：一个事务读取了另一个事务未提交数据；
• 不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
• 幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。

👉MYSQL数据库提供四种事务隔离级别

隔离级别	描述
read-uncommitted	允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题
read-committed	只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题
repeatable-read	确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读。但是幻读问题仍然存在。注意：mysql中使用了MVCC多版本控制技术，在这个级别也可以避免幻读。
serializable	确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免，但性能十分低下。

👉修改隔离级别语法：

set transaction_isolation=‘隔离级别’;

#mysql8之前 transaction_isolation变量名是 tx_isolation

👉查看隔离级别：

select @@transaction_isolation;

👉说明：

Mysql 默认的事务隔离级别为: REPEATABLE-READ

4.1 模拟事务安全问题

准备测试数据如下：

CREATE TABLE `student` (
  `sid` int NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`sid`),
  CONSTRAINT `student_chk_1` CHECK (((`age` >= 18) and (`age` <= 35)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

4.1.1 脏读问题模拟如下：

①修改事务的隔离级别

代码演示如下：

set transaction_isolation='read-uncommitted';
select @@transaction_isolation;

②客户端2开启事务，在student表中插入一条记录，但不提交。客户端1查询表student的数据

代码演示如下：

START TRANSACTION;

INSERT INTO student VALUES(3,'王五',20);

COMMIT;

SELECT * FROM student;

4.1.2 不可重复读问题模拟如下

客户端2开启事务，在student表中更新一条记录，提交事务。客户端1查询表student的数据，前后不一致。

代码演示如下：

START TRANSACTION;

UPDATE student SET  sname='xx' WHERE sid=1;

COMMIT;

SELECT * FROM student;

4.1.3 幻读问题模拟如下

客户端2开启事务，在student表中新插入一条记录，提交事务。客户端1查询表student的数据，记录数量前后不一致。

代码演示如下：

START TRANSACTION;

INSERT INTO student VALUES(4,'jack',30);

COMMIT;

SELECT * FROM student;

---