3.数据库事务的控制&特征

最新推荐文章于 2023-08-06 17:09:51 发布
最新推荐文章于 2023-08-06 17:09:51 发布
阅读量129 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 mysql 事务 隔离性
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jeanlu/article/details/105255222
版权

参考：
一天学会 MySQL 数据库
MySQL学习笔记

一、事务

在 MySQL 中，事务是一个最小的不可分割的工作单元,保证一个业务的完整性。

比如银行转账：

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';
-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

实际项目中，若只有一条语句执行成功，而另一条失败，就会出现数据前后不一致。
因此，在执行多条有关联 SQL 语句时，事务会要求这些语句执行要么都成功，要么都失败。

控制事务 - COMMIT / ROLLBACK / BEGIN

在 MySQL 中，事务的自动提交状态默认是开启的。

查询事务的自动提交状态：
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

默认事务开启（自动提交）的作用：当执行一条 SQL 语句时，其产生的效果就会立即体现出来，且不能回滚。
回滚：撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。

  • 1.自动提交
    查看自动提交状态：SELECT @@AUTOCOMMIT ；
    设置自动提交状态：SET AUTOCOMMIT = 0 。

  • 2.手动提交
    @@AUTOCOMMIT = 0 时，使用 COMMIT 命令提交事务。

  • 3.事务回滚
    @@AUTOCOMMIT = 0 时，使用 ROLLBACK 命令回滚事务。

CREATE DATABASE bank;
USE bank;
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);
INSERT INTO user VALUES (1, 'a', 1000);
//在执行插入语句后数据立刻生效，是因为 MySQL 中的事务自动将它提交到了数据库中。

由于所有执行过的语句都已经被提交过了，所以数据不能发生回滚。
让数据发生回滚（撤销sql语句执行效果）得先关闭自动提交（设置mysql自动提交为false）：
	-- 关闭自动提交
	SET AUTOCOMMIT = 0;
	-- 查询自动提交状态
	SELECT @@AUTOCOMMIT;
	+--------------+
	| @@AUTOCOMMIT |
	+--------------+
	|            0 |
	+--------------+

将自动提交关闭后，插入/测试数据回滚：
	INSERT INTO user VALUES (2, 'b', 1000);
	-- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，
	-- 发生变化的数据并没有真正插入到数据表中。
	SELECT * FROM user;
	+----+------+-------+
	| id | name | money |
	+----+------+-------+
	|  1 | a    |  1000 |
	|  2 | b    |  1000 |
	+----+------+-------+
	
由于数据还没有真正提交，可以使用回滚
	ROLLBACK;
	SELECT * FROM user;	//此结果为表中真实的数据
	+----+------+-------+
	| id | name | money |
	+----+------+-------+
	|  1 | a    |  1000 |
	+----+------+-------+

使用 COMMIT 将虚拟的数据真正提交到数据库中 :
	INSERT INTO user VALUES (2, 'b', 1000);
	COMMIT;-- 手动提交数据到数据库中（持久性）
	ROLLBACK;
	SELECT * FROM user;	-- 再次查询（回滚无效，不可撤销）
	+----+------+-------+
	| id | name | money |
	+----+------+-------+
	|  1 | a    |  1000 |
	|  2 | b    |  1000 |
	+----+------+-------+

实际应用：银行转账项目，假设在转账时发生了意外，就可以使用 ROLLBACK 回到了发生意外之前的状态。假设数据正常，可用COMMIT 手动将数据真正提交到数据表中。

-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';
-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 假设转账发生了意外，需要回滚。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，可手动开启一个事务处理事件，使其可以发生回滚：
-- 使用 BEGIN； 或者 START TRANSACTION； 手动开启一个事务

BEGIN;	-- START TRANSACTION; 可替换，等价
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交，此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
COMMIT;-- 提交数据

事务开启之后，一旦commit提交，就不可回滚（当前事务在提交时就结束了，此时a1000，b1000）

总结：

  • 事务开启：
    1.修改默认提交 set autocommit=0；
    2.默认提交开启后，手动修改BEGIN； /START TRANSACTION；
  • 事务手动提交：commit；
  • 事务手动回滚：rollback；

二、事务的 ACID 特征与使用

事务的四大特征：

  • A 原子性：事务是最小的单位，不可以再分割；
  • C 一致性：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；
  • I 隔离性：事务1 和 事务2 之间是具有隔离性的；
  • D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。
事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

  • 1.READ UNCOMMITTED ( 读取未提交，脏读 )
     若有多个事务，某事物对数据操作但未提交，其他事务可读取其未提交数据。
  • 2.READ COMMITTED ( 读取已提交，不可重复读 )
     只能读取已提交数据，导致在读取同表的数据时，可能会发生前后不一致的情况。
  • 3.REPEATABLE READ ( 可被重复读，幻读 )
     如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
     如：事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可能造成幻读。
  • 4.SERIALIZABLE ( 串行化 )
     所有的事务按序执行，当user表被一个事务操作时，不可执行其他事务的写操作，执行完一个事务后方可执行下一个事务。

性能比较：
隔离级别越高，性能越差，问题越少；
READ UNCOMMITTED >READ COMMITTED>REPEATABLE-READ>SERIALIZABLE；
mysql默认隔离级别是 REPEATABLE-READ。

查看当前数据库的默认隔离级别：
	-- MySQL 8.x
	SELECT @@GLOBAL.TRANSACTION_ISOLATION;	//GLOBAL 表示系统级别
	SELECT @@TRANSACTION_ISOLATION;			//表示会话级别
	
	-- MySQL 5.x
	SELECT @@GLOBAL.TX_ISOLATION;
	SELECT @@TX_ISOLATION;
	
	+--------------------------------+
	| @@GLOBAL.TRANSACTION_ISOLATION |
	+--------------------------------+
	| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。
	+--------------------------------+

1）脏读
修改隔离级别READ UNCOMMITTED：
	-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
	SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	
	-- 查询系统隔离级别，发现已经被修改。
	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
	+--------------------------------+
	| @@GLOBAL.TRANSACTION_ISOLATION |
	+--------------------------------+
	| READ-UNCOMMITTED               |
	+--------------------------------+
	
INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果，发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果可被其淘宝店看见，因此淘宝店的查询结果是正确的，淘宝店确认到账。

	-- 若此时小明在它所处的事务上又执行了 ROLLBACK 命令：
	ROLLBACK;
	-- 此时无论对方是谁，如果再去查询结果就会发现：
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |  1000 |
	|  4 | 淘宝店    |  1000 |
	+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

2）不可重复读
把隔离级别设置为 READ COMMITTED 
	SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
	+--------------------------------+
	| @@GLOBAL.TRANSACTION_ISOLATION |
	+--------------------------------+
	| READ-COMMITTED                 |
	+--------------------------------+
这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。

但是对于当前事务来说，它们看到的还是未提交的数据，例如：
	-- 正在操作数据事务（当前事务）
	START TRANSACTION;
	UPDATE user SET money = money - 800 WHERE name = '小明';
	UPDATE user SET money = money + 800 WHERE name = '淘宝店';
	
	-- 虽然隔离级别被设置为了READ COMMITTED，但在当前事务中仍然是数据表中临时数据。
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |   200 |
	|  4 | 淘宝店    |  1800 |
	+----+-----------+-------+
	
	-- 假设此时在远程开启了一个新事务，连接到数据库。
	$ mysql -u root -p12345612
	-- 此时远程连接查询到的数据只能是已经提交过的
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |  1000 |
	|  4 | 淘宝店    |  1000 |
	+----+-----------+-------+

假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：
	-- 小张此时查询数据：
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |   200 |
	|  4 | 淘宝店    |  1800 |
	+----+-----------+-------+
	
	-- 在小张求表的 money 平均值之前，小王做了一个操作：
	START TRANSACTION;
	INSERT INTO user VALUES (5, 'c', 100);
	COMMIT;
	
	-- 此时表的真实数据是：
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |  1000 |
	|  4 | 淘宝店    |  1000 |
	|  5 | c         |   100 |
	+----+-----------+-------+
	
	-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
	SELECT AVG(money) FROM user;
	+------------+
	| AVG(money) |
	+------------+
	|  820.0000  |
	+------------+

虽然 READ COMMITTED 只能读取到其他事务已经提交的数据，但在读取同一个表的数据时，可能会发生前后不一致的情况。

3）幻读
将隔离级别设置为 REPEATABLE READ ( 可重复读 ) 
	SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
	+--------------------------------+
	| @@GLOBAL.TRANSACTION_ISOLATION |
	+--------------------------------+
	| REPEATABLE-READ                |
	+--------------------------------+
	
测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :
	-- 小张 - 成都
	START TRANSACTION;
	INSERT INTO user VALUES (6, 'd', 1000);
	
	-- 小王 - 北京
	START TRANSACTION;
	
	-- 小张 - 成都
	COMMIT;


一般说来，当前事务开启没提交之前，查询不到，提交后可以被查询。
若提交之前其他事务开启，那么在这条事务线上不会查询到当前有操作事务的连接。

因为小王在小张 COMMIT 之前开启了一个新的事务，小王在他自己事务线上，跟其他事务无联系，故只能查询到自己所处事务的记录：
	SELECT * FROM user;
	+----+-----------+-------+
	| id | name      | money |
	+----+-----------+-------+
	|  1 | a         |   900 |
	|  2 | b         |  1100 |
	|  3 | 小明      |  1000 |
	|  4 | 淘宝店    |  1000 |
	|  5 | c         |   100 |
	+----+-----------+-------+
	
若小王此时插入了同一条数据：
	INSERT INTO user VALUES (6, 'd', 1000);
	-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
	-- 报错了，操作被告知已存在主键为 6 的字段。
	
这种现象被称为幻读，一个事务提交的数据，不能被其他事务读取到。
4）串行化，所有事务的写入操作全都是串行化的
把隔离级别修改成 SERIALIZABLE :
	SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
	+--------------------------------+
	| @@GLOBAL.TRANSACTION_ISOLATION |
	+--------------------------------+
	| SERIALIZABLE                   |
	+--------------------------------+

在两个地方同时对表进行操作：
	-- 小张 - 成都
	START TRANSACTION;
	-- 小王 - 北京
	START TRANSACTION;
	
小王插入一条数据：
	INSERT INTO user VALUES (7, '王小花', 1000);

出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。
jeanlu
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Spring Boot数据库事务控制.pptx
01-15
1.问题引入 2.事务基本概念、特性 3.事务并发引起问题，隔离级别 4.Springboot事务控制
数据库——事务
weixin_47270825的博客
04-11 3077
事务前言一、事务 Transaction二、1.总结 前言 一、事务 Transaction 1.指向含义广泛，并不一定是特指数据库中的事务处理 2.狭义上：在业务方（开发者）看来，是一个不可再分的业务动作，这个动作可能是一条或者多条SQL语句 3.事务的4个特性：ACID **Atomic 原子性：**业务动作对应的SQL应该看作一个整体，不可再分的。针对数据的修改只能是 All 全部 或者 None 没有 。不存在只完成一部分的情况。 **Consistency 一致性：**业务方来定义的特性。.
数据库事务
weixin_46203713的博客
06-08 1592
数据库事务
数据库的事务
m0_53077601的博客
09-14 1682
事务是逻辑上的一组数据库操作，要么都执行，要么都不执行。转账业务：必须同时成功或失败。
多表的多种连接查询和事务ACID
weixin_44070630的博客
08-27 669
1.多表之间的多种连接查询 文章目录1.多表之间的多种连接查询1.交叉查询2.外连接2.1 左外连接2.2 右外连接3.内连接（重点）3.1 隐式内连接3.2 显式内连接3.3 举个栗子。。。2.子查询（重点）2.1 解析2.2 子查询进阶3.事务3.1 事务的解释：3.2 事务作用：3.3 MYSQL事务管理3.3.1什么是回滚点3.3.2 回滚点的操作语句3.3.3具体操作3.3.4应用场...
Mysql高级— 事务
weixin_45659953的博客
12-25 303
事务 1. 为什么要有事务 事务广泛的运用于订单系统、银行系统等多种场景 例如： A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事： 检查A的账户余额>500元； A 账户中扣除500元; B 账户中增加500元; 正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。 那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的...
Spring Boot数据库事务控制.zip
01-16
Spring Boot数据库事务控制.zip
数据库 事务
01-19
文章目录数据库 事务1、什么是事务2、事务的四大特性3、多并发事务引起的问题4、事务的隔离级别 1、什么是事务 指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 简单的说，事务就是...
数据库开发 Oracle数据库 SQL开发教程 第11章 数据操作与事务控制（共52页）.pdf
10-13
数据库开发 Oracle数据库 SQL开发教程 第11章 数据操作与事务控制（共52页）.pdf 数据库开发 Oracle数据库 SQL开发教程 第12章 Oracle数据库网络连接（共39页）.pdf 数据库开发 Oracle数据库 SQL开发教程 第13章 ...
数据库实验报告：事务与并发控制.docx
10-03
数据库实验报告：事务与并发控制.docx
深入理解数据库事务（超详细）
最新发布
weixin_55772633的博客
08-06 793
事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
数据库事务的四大特性及事务隔离级别
热门推荐
Evan's Blog ٩(๑❛ᴗ❛๑)۶
03-10 1万+
概要： 事务的四个特性：原子性、一致性、隔离性、持久性 事务不隔离带来的问题：更新丢失、脏读、不可重复读、虚读（幻读）。其中更新丢失就是并发写，这是一定不允许的，因此一定要解决更新丢失问题。 事务隔离的级别：读未提交（1000）、读已提交（1100）、可重复读（1110）、串行化（1111）。 更新丢失 脏读 不...
快速理解关于数据库事务
Sera的博客
05-05 140
数据库事务(transcation)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。MVCC(multiple version concurrent control) 是一种数据库管理系统的并发控制方法，旨在提高数据库系统的并发性能和可扩展性。在MVCC中，每个事务读取的数据都是数据库中的一个版本，而每个版本都有一个时间戳，用于区分不同的版本。
跨多种类型数据库 事务控制
06-08
跨多种类型数据库的事务控制是一项复杂的任务，需要考虑到不同数据库之间的差异性，以及如何确保数据的一致性和可靠性。一种常见的方法是使用分布式事务管理器，这种管理器可以协调跨多个数据库的事务，并确保它们在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值