MySQL数据库 - 事务

1. 事务的概念

事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中, 要删除一个人员,即需要删除人员的基本资料,又需要删除和该人员相关的信息,如信箱, 文章等等。这样,这些数据库操作语句就构成一个事务!

  • 事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行。
  • 事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元。
  • 事务适用于多用户同时操作的数据库系统的场景,如银行、保险公司及证券交易系统等等。
  • 事务是通过事务的整体性以保证数据的一致性。

所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。

2. 事务的ACID特点

ACID,是指在可靠数据库管理系统(DBMS)中,事务(transaction)应该具有的四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。这是可靠数据库所应具备的几个特性。

  • 原子性:

    • 指事务是一个不可再分割的工作单位,事务中的操作要么都发生,要么都不发生。
      • 事务是一个完整的操作,事务的各元素是不可分的。
      • 事务中的所有元素必须作为一个整体提交或回滚。
      • 如果事务中的任何元素失败,则整个事务将失败。
  • 一致性:

    • 指在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
      • 当事务完成时,数据必须处于一致状态。
      • 在事务开始前,数据库中存储的数据处于一致状态。
      • 在正在进行的事务中,数据可能处于不一致的状态。
      • 当事务成功完成时,数据必须再次回到已知的一致状态。
  • 隔离性

    • 指在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。
      • 对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务。
      • 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据,或者在另一个使用相同数据的事务结束之后访问这些数据。
  • 持久性:

    • 在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
      • 指不管系统是否发生故障,事务处理的结果都是永久的。
      • 一旦事务被提交,事务的效果会被永久地保留在数据库中

当多个用户进行同时操作, 如果不存在隔离性的事务,就会出现以下状况:

① 脏读(读取未提交数据):

脏读 指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据。读到了并一定最终存在的数据,这就是脏读

案例:

假设在银行转账的场景中,有两个并发运行的事务:

  • 事务A:从账户A向账户B转账100元。这个过程包括两个步骤:
    • 减少账户A的余额(UPDATE Account SET balance = balance - 100 WHERE account_id = A)。
    • 增加账户B的余额(UPDATE Account SET balance = balance + 100 WHERE account_id = B)。
  • 事务B:在事务A的第一步执行完毕,第二步还未执行时,事务B开始读取账户A和账户B的余额。

如果此时数据库系统的事务隔离级别设置为读未提交(Read Uncommitted),那么事务B可以看到事务A尚未提交的中间状态,即账户A的余额已经减少,但账户B的余额尚未增加。假设事务A因为某种原因回滚,那么事务B读到的就是“脏”数据,因为它反映了从未真正发生的交易。

        实际情况应避免脏读的发生,大多数数据库系统通过设置恰当的事务隔离级别(如读已提交、可重复读、序列化等)来防止这种情况。在上述案例中,如果采用读已提交或更高级别的隔离策略,事务B在读取数据时,只会看到事务A已经提交后的结果,从而避免了脏读。        

② 不可重复读(前后多次读取,数据内容不一致):

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

案列:

假设在图书馆管理系统中有两个并发的事务

  • 事务A:管理员正在统计某个时间段内借阅次数最多的书籍。事务A开始并执行查询:
SELECT COUNT(*) AS borrow_count, book_id 
FROM BorrowRecord 
WHERE borrow_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY book_id
ORDER BY borrow_count DESC;

得到了一份统计报表。

  • 事务B:与此同时,另一名读者前来归还一本书籍,并且这本书恰好在事务A查询的范围内。事务B执行:
UPDATE BorrowRecord 
SET return_date = NOW() 
WHERE book_id = 'book_X' AND borrow_date <= '2023-12-31';

并提交了事务。

  • 事务A:管理员再次执行相同的查询以验证数据准确性,但这次查询的结果与第一次不同,因为事务B已经在其间归还了一本书,导致该书的借阅次数减少。

在这个案例中,事务A在两次查询之间遭遇了不可重复读问题,尽管查询条件不变,但因为其他事务的修改,导致事务A前后两次读取的数据不一致。

        为了避免不可重复读,数据库系统通常会提供不同级别的事务隔离,如可重复读(Repeatable Read)隔离级别,可以确保在一个事务内部多次读取同一数据时,除非该事务自己修改了数据,否则总是能看到相同的结果。在MySQL InnoDB存储引擎中,默认的事务隔离级别是可重复读,通过MVCC(多版本并发控制)机制来避免不可重复读问题。

③ 幻读(前后多次读取,数据总量不一致):

一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表中还有没有修改的数据行,就好象发生了幻觉一样。

案列:

假设一个电商网站上有两个并发的事务:

  • 事务A:买家正在进行商品筛选,想要购买价格低于100元的库存充足的某个类别的商品。事务A首先执行查询:
SELECT * FROM Products 
WHERE category = 'Electronics' AND price < 100 AND stock > 0;

此时事务A获取到了50条符合条件的商品记录。

  • 事务B:同时,另一位卖家在后台上传了一批新的电子产品,并设定价格低于100元,这批商品立即生效,事务B执行并提交了以下插入操作:
INSERT INTO Products (category, price, stock)
VALUES ('Electronics', 99, 10),('Electronics', 85, 20),...;
  • 事务A:买家随后准备下单,再次执行了同样的查询:
SELECT * FROM Products 
WHERE category = 'Electronics' AND price < 100 AND stock > 0;

这次查询的结果包含了事务B刚刚插入的记录,所以结果集中商品数量大于50条,对于事务A而言,就好像出现了“幻影”般的记录。

        在标准的可重复读(Repeatable Read)隔离级别下,MySQL的InnoDB存储引擎通过间隙锁(Gap Locks)和Next-Key Locks可以一定程度上防止幻读问题的发生,但如果查询条件不包含唯一索引或主键,仍有可能发生幻读。要完全避免幻读,需要将事务隔离级别提升到序列化(Serializable)级别。在序列化级别下,数据库会采取更强的锁策略,以确保事务间的严格串行化执行,但这会牺牲一定的并发性能。        

④ 丢失更新:

两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B的修改结果覆盖了A的修改结果。

案列:

假设在一个银行账户转账系统中,有两个并发的事务:

  • 事务A:Alice想要从她的账户转账100元给Bob,事务A首先读取Alice的账户余额为1000元。
SELECT balance FROM Accounts WHERE account_id = Alice;
// 返回结果:balance = 1000
  • 事务B:几乎同时,Bob的父亲Charlie也想转账100元给Bob,事务B也读取了Alice的账户余额为1000元。
SELECT balance FROM Accounts WHERE account_id = Alice;
// 返回结果:balance = 1000
  • 事务A:事务A开始执行转账操作,减少Alice账户100元,并更新数据库。
UPDATE Accounts SET balance = balance - 100 WHERE account_id = Alice;
  • 事务B:事务B也开始执行转账操作,也减少Alice账户100元,并更新数据库。
UPDATE Accounts SET balance = balance - 100 WHERE account_id = Alice;

事务提交:事务A和事务B都成功提交了自己的更新操作。

现在的问题是,Alice实际上应该只转出了100元,但是由于两个事务并发且没有采取适当的并发控制机制,Alice的账户减少了200元,导致Alice的100元更新丢失。

        要避免丢失更新的问题,数据库系统可以采用乐观锁(如使用version字段或时间戳字段进行比较)或 悲观锁(如在事务开始时对要更新的数据行加排他锁)等机制。在SQL Server中,可以使用UPDLOCK提示在SELECT语句中获取更新锁来防止丢失更新。而在MySQL的InnoDB存储引擎中,通过使用适当的事务隔离级别(如可重复读及以上)以及在更新时添加FOR UPDATE或LOCK IN SHARE MODE子句,也可以避免这类问题。

3. 事务的隔离级别

事务的隔离级别是数据库管理系统(DBMS)用来控制并发事务之间相互影响的程度,以确保事务的执行不会彼此干扰,维持数据的完整性和一致性。标准SQL规范定义了四种事务隔离级别,它们分别是:

  • 读未提交(Read Uncommitted)

    • 在这个级别,一个事务可以读取到另一个事务尚未提交(commit)的数据变更,这可能导致“脏读”(Dirty Read)。这是最低的隔离级别,允许最大的并发,但并发问题最严重。

    • 安全性最差   但性能最好 (不使用)

  • 读已提交(Read Committed)

    • 在这个级别,一个事务只能读取到已经提交的数据,即事务开始读取数据时,其他事务对数据的修改只有在它们提交后才能被这个事务看到。这解决了“脏读”问题,但可能导致“不可重复读”(Non-repeatable Read)和“幻读”(Phantom Read)现象。
    • 安全性较差  性能较好  (oracle 数据库 默认就是这种)
  • 可重复读(Repeatable Read)

    • 在这个级别,一个事务在自身持续时间内多次读取同一数据,总会得到相同的结果,即使有其他事务提交了对该数据的更新。MySQL的InnoDB存储引擎默认使用这个级别,并通过多版本并发控制(MVCC)机制防止了“脏读”和“不可重复读”。但在这个级别,“幻读”现象依然可能存在。
    • 安全性较高,性能较差 (mysql 默认的就是这种)
  • 串行化(Serializable)

    • 可以解决 脏读 不可重复读 和 幻读—相当于锁表,完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。    这是最高的隔离级别,提供完全的事务隔离。在这种情况下,事务按照完全串行化的顺序执行,好像被安排成依次执行一样。通过使用范围锁或更严格的锁机制,该级别可以防止“脏读”、“不可重复读”和“幻读”等问题,但可能导致更高的并发开销和更低的性能,因为它会限制事务并发执行的能力。
    • 安全性高  性能差   不使用

mysql默认的事务处理级别是 repeatable read ,而Oracle和SQL Server是 read committed 。

事务隔离级别的作用范围分为两种:
全局级: 对所有的会话有效
会话级: 只对当前的会话有效

4. 事务级别的查看与设置

 variables 指变量, global variables 指全局变量

4.1 查询全局事务隔离级别

show global variables like '%isolation%';SELECT @@global.tx_isolation;

4.2 查询会话事务隔离级别

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

4.3 设置全局事务隔离级别

set global transaction isolation level read committed;
#将当前数据库服务器中所有新启动的事务的默认隔离级别设置为“读已提交”

4.4 设置会话事务隔离级别

set session transaction isolation level read committed;

5. 事务控制语句

begin 或  start transaction;  
#显示的开启一个服务commit  或  commit work;
#提交事务,并使已对数据库进行的所有修改变为永久性的。rollback  或  rollback work;
#回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。savepoint s1
#使用 SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个 
'S1'代表回滚点名称。rollback to [savepoint] s1; 把事务回滚到标记点。

5.1 使用 set 设置控制事务

SET AUTOCOMMIT=0;						#禁止自动提交
SET AUTOCOMMIT=1;						#开启自动提交,Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback 或者 commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交,mysql会把每个sql语句当成一个事务,然后自动的commit。
当然无论开启与否,begin; commit|rollback; 都是独立的事务。

5.2 测试提交事务

另一台连此数据库

这里,我们提交事务

再去查看

5.3 测试回滚事务

5.4 测试多点回滚

多点回滚:

在数据库中,一旦一个事务被回滚(rollback),事务中所做的所有更改都将被撤销,回到事务开始前的状态。理论上,一旦事务被回滚,那些被撤销的更改就无法再恢复,因为回滚操作是不可逆的。

然而,如果你希望回到事务回滚后的某个状态,通常有两种方法:

  1. 重新执行事务: 如果你知道事务中做了哪些更改,你可以重新执行相同的事务操作,前提是其他并发事务没有对相同数据进行更改。

  2. 依赖于数据库的特定功能: 某些数据库系统提供了诸如闪回(Flashback)等高级功能,允许你在一定条件下恢复到过去某一时间点的数据状态。例如,在Oracle数据库中,可以使用FLASHBACKFLASHBACK DATABASE命令来恢复到之前的时间点。

但是,大部分数据库系统并没有内置的直接手段去恢复一个已被回滚的事务。因此,最佳实践是谨慎对待事务的提交和回滚操作,并且定期备份数据库以防数据丢失。在开发阶段,可以使用版本控制系统来追踪代码更改,以便在必要时恢复代码的某个版本。在生产环境,做好数据库备份和恢复策略是非常关键的。

总结:

事务的ACID特性:
原子性(Atomicity)
一致性(Consistency )
隔离性(Isolation)
持久性(Durability)
这是可靠数据库所应具备的几个特性。

事务之间的相互影响
当多个客户端并发地访问同一个表时,可能出现下面的一致性问题:

脏读
不可重复读
幻读
丢失更新
事务的隔离级别:
(1)未提交读(Read Uncommitted(RU)) :允许脏读。

(2)提交读(Read Committed (RC)) :防止脏读。

(3)可重复读(Repeatable Read(RR)):—mysql默认的隔离级别,防止脏读和不可重复读。

(4)串行读(serializable):—相当于锁表,可以防止脏读、不可重复读和幻读,(事务串行化)会降低数据库的执行效率。

查询全局事务隔离级别:
show global variables like '%isolation%';
select @@global.tx_isolation;
查询会话事务隔离级别:
show session variables like '%isolation%';
select @@session.tx_isolation;
select @@tx_isolation;
设置全局事务隔离级别:
set global transaction isolation level 隔离级别; #永久生效
示例: set global transaction isolation level read committed;
设置会话事务隔离级别:
set session transaction isolation level 隔离级别; #退出连接后失效
示例:set session transaction isolation level read committed;
事务控制语句:
BEGIN 或 START TRANSACTION: 显式地开启一个事务。
COMMIT 或 COMMITWORK: 提交事务,并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK: 回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
SAVEPOINT S1: 使用SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个SAVEPOINT;“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1: 把事务回滚到标记点。
使用 set 设置控制事务:
set autocommit=0; #禁止自动提交(仅针对当前会话)

set autocommit=1; #开启自动提交(仅针对当前会话),Mysql默认为1

set global autocommit=0; #禁止自动提交(针对全局事务)

set global autocommit=1; #开启自动提交(针对全局事务),Mysql默认为1

show variables like 'autocommit';   #查看当前会话的autocommit值

show global variables like 'autocommit';    #查看全局事务的autocommit值

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/282762.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

移动硬盘故障解析:解决无法访问且位置不可用问题

在我们日常的工作和生活中&#xff0c;移动硬盘已成为存储和传输数据的重要工具。然而&#xff0c;有时我们会遇到移动硬盘无法访问且位置不可用的情况&#xff0c;这无疑给数据的存储和访问带来了极大的困扰。本文将深入探讨这一问题&#xff0c;分析其原因&#xff0c;并给出…

网络工程师练习题6

网络工程师 综合题 计算并填写下表&#xff1a; TP地址191.23.181.13子网掩码255.255.192.0地址类型 &#xff08;1&#xff09;网络地址&#xff08;2&#xff09;直接广播地址&#xff08;3&#xff09;主机号&#xff08;4&#xff09;子网内的最后一个可用IP地址&#xf…

mysql的学习笔记

干前端好几年了,只会前端总感觉少了条腿,处处不自在,决定今年学习下后端的东西.以前总想着学node会更快,但是实际工作上却用不上. 出来混,总是要还的,该学的javaWeb这一套体系的东西,总是需要学习的. 那就开始啦. 一,在本地电脑mac上安装mysql 这个参考的这篇文章,照着做一次…

线性顺序表算法库

list.cpp 具体函数实现 #include <stdio.h> #include "list.h" #include <malloc.h>/************************************************** ①函数名: CreateList 功 能: 用数组构建顺序表 参 数: ①SqList *&L:传入的线性表 ②ElemType a[]:使用…

文件上传基础篇

文件上传基础篇 文件上传漏洞原理 ​ 目标网站存在文件上传接口&#xff0c;但是对用户上传的文件没有做仔细甄别&#xff0c;导致黑客可以根据此功能点直接上传木马到网站服务器&#xff0c;造成危害 文件上传存在点 ​ 通常有头像上传&#xff0c;pdf上传 文件上传防护 …

Git浅谈配置文件和免密登录

一、文章内容 简述git三种配置ssh免密登录以及遇见的问题git可忽略文件git remote 相关操作 二、Git三种配置 项目配置文件(局部)&#xff1a;项目路径/.git/config 文件 git config --local user.name name git config --local user.email 123qq.cc全局配置文(所有用户): …

X1 grok-1 开源大语言模型下载

Grok 前言 大型语言模型 Grok-1 的基本模型权重和网络架构。Grok-1 是一个 3140 亿参数的专家混合模型&#xff0c;由 xAI 从头开始训练。 这是 2023 年 10 月结束的 Grok-1 预训练阶段的原始基础模型检查点。这意味着该模型不会针对任何特定应用&#xff08;例如对话&#x…

Linux的学习之路:1、发展史与编译环境的搭建

一、发展史 1991年10月5日&#xff0c;赫尔辛基大学的一名研究生Linus Benedict Torvalds在一个Usenet新闻组 &#xff08;comp.os.minix&#xff09;中宣布他编制出了一种类似UNIX的小操作系统&#xff0c;叫Linux。新的操作系统是受到另一个UNIX的小操作系统——Minix的启发…

【群晖】Docker Compose部署 Emby Server

【群晖】Docker Compose部署 Emby Server 本来群晖上面的 Emby 是用套件安装的&#xff0c;但是不巧的是前两天脑袋一抽装了两个插件&#xff0c;导致 Emby Server被当肉鸡了&#xff0c;还找不到脚本代码在哪儿&#xff0c;一天时间上传了3T的流量。无奈之下&#xff0c;只能尝…

计算机二级大题

题目来源&#xff1a;计算机二级Python半个月抱佛脚大法&#xff08;内呈上真题版&#xff09; - 知乎 1.大题1 注意csv文件读取的处理 ls[] for line in f: ls.append(line.strip(\n).split(,)) 2. 大题2 第一问&#xff1a; #计算有效票张数 fopen("vote.txt",…

LinuxU盘挂载原理,为什么要用到U盘挂载及实现U盘挂载

目录 一、U盘挂载原理 二、为什么要用到U盘挂载 三、实现U盘挂载 一、准备工作 1、安装gcc 2、下载ntfs-3g 3、解压 4、编译准备 5、编译并安装 二、挂载演示 一、U盘挂载原理 Linux的U盘挂载原理涉及以下几个方面&#xff1a; 设备识别&#xff1a;当您将U盘插入Lin…

leetcode刷题(javaScript)——动态规划相关场景题总结

动态规划在 JavaScript 刷题中有一定的难度&#xff0c;但也是非常常见和重要的算法思想。动态规划通常适用于需要求解最优解、最大值、最小值等问题的场景&#xff0c;可以将复杂问题拆分成子问题&#xff0c;通过存储子问题的解来避免重复计算&#xff0c;从而提高效率。 理解…

整数和浮点数在内存中存储

整数在内存中的存储 整数的2进制表⽰⽅法有三种&#xff0c;即原码、反码和补码。 对于整形来说&#xff0c;数据存放内存中的其实是补码。 在计算机系统中&#xff0c;数值一律用补码来表示和存储。原因是&#xff0c;使用补码&#xff0c;可以使符号位和数值域统一处理&am…

【爬虫】专栏文章索引

为了方便 快速定位 和 便于文章间的相互引用等 作为一个快速准确的导航工具 爬虫 目录&#xff1a; &#xff08;一&#xff09;web自动化和接口自动化 &#xff08;二&#xff09;实战-爬取Boss直聘信息数据

构建强大的API:Django中的REST框架探究与实践【第146篇—Django】

&#x1f47d;发现宝藏 前些天发现了一个巨牛的人工智能学习网站&#xff0c;通俗易懂&#xff0c;风趣幽默&#xff0c;忍不住分享一下给大家。【点击进入巨牛的人工智能学习网站】。 构建强大的API&#xff1a;Django中的REST框架探究与实践 在当今的Web开发中&#xff0c;构…

面试算法-83-不同路径 II

题目 一个机器人位于一个 m x n 网格的左上角 &#xff08;起始点在下图中标记为 “Start” &#xff09;。 机器人每次只能向下或者向右移动一步。机器人试图达到网格的右下角&#xff08;在下图中标记为 “Finish”&#xff09;。 现在考虑网格中有障碍物。那么从左上角到…

如何一键下载微信视频号的视频至本地电脑

社交媒体平台上的短视频已经成为我们获取信息和娱乐的重要来源&#xff0c;尤其是微信视频号。这个平台汇聚了丰富多样的内容&#xff0c;从生活分享到专业知识&#xff0c;应有尽有。然而&#xff0c;有时我们可能希望将这些有趣的或有用的视频保存到本地以便离线观看或分享给…

单片机学到什么程度才可以去工作?

单片机学到什么程度才可以去工作? 如果没有名校或学位的加持&#xff0c;你还得再努力一把&#xff0c;才能从激烈的竞争中胜出。以下这些技能可以给你加分&#xff0c;你看情况学&#xff0c;不同行业对这些组件会有取舍: . Cortex-M内核:理解MCU内核各部件的工作机制&#…

如何利用RunnerGo简化性能测试流程

在软件开发过程中&#xff0c;测试是一个重要的环节&#xff0c;需要投入大量时间和精力来确保应用程序或网站的质量和稳定性。但是&#xff0c;随着应用程序变得更加复杂和庞大&#xff0c;传统的测试工具在面对比较繁琐的项目时非常费时费力。这时&#xff0c;一些自动化测试…

学习总结2

解题思路 用bfs进行搜索,标记A罐B罐所保存的水的出现情况,当再次出现的时候停止搜索,然后用数组模拟链表进行保存路径.最后输出. 代码 #include <iostream> #include <cstdio> #include <fstream> #include <algorithm> #include <cmath> #in…