程序员经验分享-hwhale

MySQL数据库事务处理

2023-05-16

事务处理用于有效记录某机构感兴趣的业务活动（称为事务）的数据处理（例如销售、供货的定购或货币传输）。通常，联机事务处理 (OLTP) 系统执行大量的相对较小的事务。——百度百科

事务处理是将多个操作或者命令一起执行，所有命令全部成功执行才意味着该事务的成功，任何一个命令失败都意味着该事务的失败。

以银行转账为例（100块都不给），
A要给B 转账100元，
A转账的指令已经成功发出，而B 由于未知的原因接收失败，
如果两个命令单独执行，那么A账户少了100块，但是B又没收到100块，显而易见是不合理的；
如果将A向B 转账100元当成一个事务处理，那么由于B 接收的失败，整个转账事务都将失败，A不会少100，B更不会增加100，这个时候“100块都不给”才是合理的情况。
事务处理

因此，事务处理是不是一荣俱荣，而是一毁全毁。

接下来介绍MySQL数据库中如何进行事务处理以及锁定。

0 数据库事务处理相关命令

进行的操作命令
查看存储引擎SHOW CREATE TABLE 表名；
更改引擎ALTER TABLE 表名 ENGINE=新引擎名；
回滚ROLLBACK;
声明事务开始BEIGIN;
事务提交COMMIT;
查询自动提交功能状态SELECT @@AUTOCOMMIT;
设置自动提交功能SET AUTOCOMMIT=0或1;
设置分离水平SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;

1 存储引擎

MySQL数据库的存储引擎是可以选择改变和替换的（可替换存储引擎构架，Pluggable Storage Engine Architecture）。MySQL主要有8种存储引擎：

存储引擎特征
MyISAM高速引擎，不支持事务处理
InnoDB支持行锁定以及事务处理，速度比MyISAM稍慢
ISAMMyISAM的前身
MERGE将多个MyISAM类型的表作为一个表来处理的引擎
MEMORY,HEAP只在内存上保存数据
Falcon一种新的存储引擎，支持事务处理
ARCHIVE将数据压缩后保存（只能支持INSERT/SELECT操作
CSV以CSV形式保存数据（应用于跨平台数据交换）

MySQL的存储引擎种类和特征

（1）查看存储引擎 SHOW CREATE TABLE 表名;
查看某表使用的存储引擎，语法代码如下：

 `SHOW CREATE TABLE 表名;`

如，要查看表customer的存储引擎，可以输入代码：

 `SHOW CREATE TABLE customer;`

查看存储引擎
如图表所示ENGINE=后面显示的就是存储引擎。

（2）更改存储引擎ALTER TABLE 表名 ENGINE=新引擎名；

若要更改存储引擎，可以使用代码：

>ALTER TABLE 表名 ENGINE=新引擎名；

eg:将表customer的存储引擎修改为MyISAM，输入指令：

ALTER TABLE customer ENGINE=MyISAM；

更改存储引擎

2 事务处理

之前讲到，事务处理是一毁全毁，因此事务中任意一个任务或指令失败，整个事务都将失败。那是怎么实现的呢？方法是时间中多个任务全部成功，则任务成功结束，并且会进行提交（COMMIT），如果任何一件任务失败，则强制回滚（ROLLBACK）到初始状态。

事务处理涉及到三个最重要的命令：BEGIN,ROLLBACK,COMMIT，分别表示声明事务开始，回滚和确认提交。

（1）回滚演示（ROLLBACK）
首先将表格customer 的存储引擎设置为InnoDB，
确认表格数据；SELECT * FROM customer;
事务开始；BEGIN;
删除表格数据；DELETE FROM customer;
再次查看表格数据；SELECT * FROM customer;
回滚到初始状态；ROLLBACK;
再次查看表格数据；SELECT * FROM customer;

回滚
可以看到，当执行ROLLBACK;之后，删除的记录又恢复到了BEGIN之前的状态，如果将ROLLBACK 换成COMMIT，那么事务将会提交，删除的记录就不能恢复了。

（2）自动提交

当搜索引擎为MyISAM时，因为不支持事务处理，因此命令一旦执行，就一定会提交，这种默认的提交方式被称为自动提交。

而当搜索引擎设置为InnoDB时，可以设置自动提交功能是否开启，当自动提交功能为ON时，命令执行就会提交（COMMIT）,而自动提交设置为OFF 时，必须执行COMMIT才提交，可以使用ROLLBACK进行回滚。

查询当前自动提交功能状态：

>SELECT @@AUTOCOMMIT;

设置自动提交功能：

>SET AUTOCOMMIT=0或1;

如图，将自动提交设置为OFF，插入一条记录，然后使用回滚ROLLBACK，再次查看记录，会发现不见了。
自动提交

（3）部分回滚 SAVEPOINT

直接ROLLBACK会回滚到BEGIN开始之前的地方，而通过SAVEPOINT可以保存一个点，通过ROLLBACK TO SAVEPOINT就可以回滚到保存点了，也就实现了“想去哪就去了哪”。
部分回滚

部分回滚主要有两个步骤：
①保存点

>SAVEPOINT 保存点名;

②回滚到保存点

>ROLLBACK TO SAVEPOINT 保存点名;

eg:
mysql部分回滚

（4）不能事务处理的命令（直接提交）
大部分命令都可以通过事务处理（BEGIN -ROLLBACK- COMMIT）进行操作，但是：

  • DROP DATABASE;
  • DROP TABLE;
  • DROP;
  • ALTER TABLE

不能通过事务处理，会直接COMMIT;

3 锁定与事务处理

前面讲到的ROLLBACK 等操作指令都是基于一个用户进行的。但是事务类型往往不只一个用户，多个用户同时操作，如被人广泛诟病的“12306”火车票购票系统，全国各地的售票窗口以及互联网购票注册账户，成千上万的用户同时使用，因此事务处理必须能够处理多个用户同时操作的情况，这就需要锁定。

举个例子，如果某班火车只剩最后一张票，A和B 同时登陆网站购票，得到的反馈是还剩一张，于是A，B 都赶紧下单，处理这种冲突事件，就需要对该事务进行锁定（LOCK），接触锁定被称为解锁（Unlock）。

锁定

3.1 锁定

（1）锁定的分类

锁定分为共享锁定（Shared Lock）和排他锁定（Exclusive Lock）：

  • 共享锁定是将对象数据变为只读形式，不能进行更新，所以也成为读取锁定；
  • 排他锁定是当执行INSERT/UPDATE/DELETE的时候，其它事务不能读取该数据，因此也成为写入锁定。

（2）锁定的粒度

锁定对象的大小是锁定的粒度，有三种粒度：

  • 记录
  • 表
  • 数据库

3.2 事务处理的分离水平

需要使用锁定来有效解决事务冲突的情况，但是锁定也会使性能下降（因为别人无法访问），因此频繁锁定不一定合理，数据库中，使用分离水平来表示事务处理之间的影响程度。

事务处理的分离水平对应的数据整合情况：

分离水平非提交读取不可重复读取幻象读取
READ UNCOMMITED√√√
READ COMMITED×√√
REPEATABLE READ××√
SERIALIZABLE×××

设置分离水平可以使用命令： 

>SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;


为了模拟多个用户对数据库进行访问和操作，我们打开两个命令窗口接入MySQL。
（1）非提交读取

非提交读取指的是别的事务能够读取到还没有提交的更新数据，只发生在分离水平为READ UNCOMMITED的情况下。

因为对事务处理的读取没有任何限制，所以一般不推荐使用。

eg：
非提交读取

两个窗口的执行顺序如图红色序号所示:
①首先再A窗口对Id为g001的记录的nam进行修改;
②然后B进行访问，发现已经能够读取新的nam;
③A执行ROLLBACK,回滚到初始状态（nam恢复原来的记录）；
④B再次查询，又得到OLD记录（旧记录）；
⑤A再次UPDATE，并COMMIT;
⑥B再次SELECT,得到新纪录（new）。
可以看到，当A还没提交，B就可以看到更新的数据，这个时候很可能出现问题，比如A后来执行ROLLBACK，B看到的数据实际上是错误的数据。

（2）不可重复读取

不可重复读取是指在某事务处理过程中对数据进行读取，由于该事务更新操作导致多次读取数据时发生了改变。

不可重复读取发生在READ COMMITED 一下的分离水平。

eg：
不可重复读取
命令顺序依然如图红色数字所示：
①A更新id为g001的nam；
②B查询，结果是OLD数据；
③A提交更新；
④B再次查询，得到更新后的NEW数据。
B先后两次查询，结果不一致。

（3）幻象读取

幻象读取指的是，在某事物处理数据过程中对数据多次读取，由于该事务的插入/删除操作而导致在多次读取过程中读取到不存在或者消失的数据。

下图是幻象读取发生的例子：

幻象读取发生
①A事务开始；
②B查询；
③A插入一条记录并提交；
④B再次查询；
可以看到B连续执行两次的查询，前后结果不一致。

当设置分离水平为SERIALIZABLE时，可以消除幻象读取。

消除幻象读取
①A事务开始；
②B查询；
③A插入一条数据；（此时A会一直等待，直到B提交）
④B提交；
⑤A提交；
⑥B再次查询；

特别需要注意的是，由于B正在读取数据，当A 执行插入命令时，无法立马得到结果，而是一直等待，直到B提交。当B提交时，A的插入命令自动完成。

3.3 死锁 Dead Lock（狭路相逢勇者胜）

死锁指的是两个事务互相对待对方释放锁定，则永远也不可能接触锁定的状态。

如A，B两个用户都对表customer中记录’g001’和’g002’实施了排他锁定，由于两个事务互相等待对方释放锁定，所以形成了死锁。
死锁
A和B执行命令顺序红色字体所示：
①A事务开始，并对’g001’进行了更新；
②B事务开始，并对’g002’进行了更新；
③A对’g002’进行更新；（A等待B提交释放锁定）
④B对’g001’进行更新；（B等待A提交释放锁定，A、B互相等待陷入死锁）

此时,MySQL数据库让B强制解除锁定，A继续执行；

⑤A提交；
⑥B回滚并查询，得到A事务更新的数据；

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库事务处理

MySQL数据库事务处理 的相关文章

  • 如何列出允许登录mysql的ip？

    我知道使用下面的命令 可以允许远程IP登录MySQL GRANT select insert update delete ON TO root my ip IDENTIFIED BY my password FLUSH PRIVILEGES

  • MAMP Pro mysql 无法启动

    我遇到问题无法找到解决此问题的方法 我收到这个错误 2017 01 11 23 58 25 7fffbac563c0 InnoDB Operating system error number 2 in a file operation In

  • 计算唯一值的数量

    如果我有三列 orderNumber name email 我想计算表中有多少个唯一的电子邮件 我该怎么做 像这样的声明 SELECT count email FROM orders 给我总数 I tried SELECT DISTINCT

  • Laravel 5：如何检索并显示属于特定类别的所有帖子

    我有3张桌子 user id username subreddits id name created at posts id title link user id subreddit id 问题是 我手动获取 subreddit 类别的 i

  • 跨数据库管理系统检查字符串是否为数字的方法

    好的 我有这个字段 code varchar 255 它包含我们导出例程中使用的一些值 例如 DB84 DB34 3567 3568 我需要仅选择自动生成的 全数字 字段 WHERE is numeric table code is num

  • 为什么我的 php 代码无法连接到远程 MySql 数据库？

    我正在尝试连接到远程 MySql 数据库 但收到以下错误消息 警告 mysqli connect HY000 2002 连接尝试失败 因为连接方在一段时间后没有正确响应 或者由于连接的主机未能响应而建立的连接失败 在 C myLocalDi

  • MySql - 自动完成

    我正在创建一个 Ajax 自动完成应用程序 并且想知道是否有一个 SQL 查询可以用于此目的 例如 如果有人键入 p 我想检索所有以 p 开头的单词 如果他们添加 e 检索所有以 pe 开头的单词 并继续这样 有人提出了下面的查询 但我认为

  • 备份MySQL数据库

    我有一个大约 1 7GB 的 MySQL 数据库 我通常使用 mysqldump 进行备份 这大约需要 2 分钟 但是 我想知道以下问题的答案 mysqldump 是否阻止对数据库的读取和 或写入操作 因为在实际场景中 我不想在备份数据库时

  • 如何防止大型 MySQL 导入的连接超时

    在开发过程中 我们的本地 WAMP 服务器如何从测试服务器获取最新数据 即生成数据库转储 然后使用 source 命令上传该转储以加载 sql 文件 最近 在导入的最后 我们收到了有关 old 变量的错误 这些变量在更改之前存储了原始设置

  • MySQL - 重命名列

    如何重命名 mysql 列help to content在我的桌子上tbl help mysql query ALTER TABLE tbl help CHANGE COLUMN help content 您必须在更改列语句中包含列的定义

  • LEFT JOIN 返回与 INNER JOIN 相同的结果

    我有一张桌子 磨砂膏 里面有 1600 个独特的物品 第二张桌子有100万以上 我运行 INNER JOIN 并获得 65 个匹配项 SELECT a BW Parent Number a Vendor Name b Parent Supp

  • 在 MySQL 5.6 中重置 ROOT 密码

    我一直在关注这些说明 http dev mysql com doc refman 5 6 en resetting permissions html用于重置root本地安装的密码MySQL 5 6在 Windows 7 笔记本电脑上 我停止

  • 如何解决 MySQL Workbench 上的这些行错误？

    正如您所看到的 我的代码中没有语法错误或类似的错误 你们能帮我吗 我想这只是错误标记机制中的一个小错误 尝试编辑代码或关闭此编辑器并打开一个新编辑器 如果您有重现此问题的步骤列表 您甚至可以创建一个错误报告 http bugs mysql

  • sqlalchemy 中的随机 ID（pylon）

    我正在使用 pylons 和 sqlalchemy 我想知道如何将一些随机 id 作为primary key 最好的方法是使用随机生成的 UUID import uuid id uuid uuid4 uuid 数据类型在某些数据库中本机可用

  • MySQL 性能 DELETE 或 UPDATE？

    我有一个超过 10 7 行的 MyISAM 表 向其中添加数据时 我必须在最后更新 10 行 删除它们然后插入新行更快 还是更新这些行更快 应更新的数据不是索引的一部分 索引 数据碎片怎么样 UPDATE到目前为止要快得多 当你UPDATE

  • mysql查询先慢后快

    我有 2 个 myISAM 表 分别称为 tests 和 completed tests 一个有 170 个条目 另一个有 118k 条目 当我运行此查询时 SELECT ct archive ct status ct score ct u

  • PHP 5.4 PDO 无法使用旧的不安全身份验证连接到 MySQL 4.1+

    我知道有很多类似的问题 事实上我已经阅读了所有 9 个问题 但是 他们都没有解决我的问题 我有一个共享托管包 最低限度 我的包中包含域名和托管 MySQL 服务器的单独 IP 地址 为了开发 我正在使用http localhost 与 PH

  • MySQL LAST_INSERT_ID() 和 FOUND_ROWS()

    当 PHP 脚本每秒有数百个查询时会发生什么 它会影响这些函数吗 是否保证它们会返回当前脚本中最后一个插入语句中最后插入的 id 它会返回当前脚本中最后一次选择的行数吗 如果同时从另一个脚本进行新的插入或选择 在 FOUND ROWS 的情

  • 选择每组最新的项目[重复]

    这个问题在这里已经有答案了 可能的重复 检索每组中的最后一条记录 https stackoverflow com questions 1313120 retrieving the last record in each group 我有 2

  • Delphi XE5 FireDAC 错误：无法加载供应商库 [libmysql.dll 或 libmysqld.dll]

    我在 Windows 7 64 位上使用 Delphi XE5 只是尝试 FireDAC 组件 我正在使用一个 TFDConnection 组件连接到本地 MySQL 数据库 v5 6 15 我已经将 libmysql dll 32位 v5

随机推荐

  • 使用wsl命令迁移WSL。

    1 打开CMD xff0c 查看所有WSL 执行 xff1a wsl l all v 显示 xff1a Windows Subsystem for Linux Distributions Ubuntu 20 04 Default 2 导出W

  • 使用脚本重启WSL（ubuntu)。

    1 创建 重启脚本文件 bat cmd 编辑脚本 xff0c 输入 xff1a net stop LxssManager net start LxssManager 保存关闭 2 运行脚本 脚本运行需要管理员权限 解决方法 xff1a xf

  • C# winform treeview GDI+流程连线 流程图 树形流程图

    C winform treeview GDI 43 流程连线 流程图 树形流程图 TreeView Paint的方法呈现效果 xff0c 具体如下 1 点击连线按钮开始准备连线 2 点击第一个连线的起始节点 3 再点击第二个连线的结束节点

  • 使用STM32寄存器点亮LED流水灯

    目录 一 什么是寄存器GPIO 二 使用寄存器点亮LED灯1 建立工程模板1 1 建立相关文件1 2 建立工程1 3 配置环境 2 配置GPIO端口2 1 配置时钟使能2 2 初始化结构体2 3 配置输入输出模式 3 主要函数3 1 led

  • frp内网穿透—将kali代理在公网中进行渗透测试

    文章目录 前言Frp简介准备工具实现内网穿透frp服务端配置frp客户端配置 测试HTTP服务测试ssh服务测试反弹shell测试 注意 前言 由于工作常需要用到kali xff0c 但是公网服务器并没有安装kali镜像 xff0c 本地只

  • 最小二乘法解的矩阵形式推导

    最小二乘法解的矩阵形式推导 首先 xff0c 什么是最小二乘 xff1f 维基百科给出了一个定义 xff0c 戳这里 在我看来 xff0c 最小二乘法是一种数据拟合方法 我们从矩阵的角度来理解 xff1a 首先我们给出一个矩阵中的定义 xf

  • pytesseract识别数字

    针对工程需求精度不够 xff0c 目前只能识别率为86 左右 用tesseract的深度学习可能会好一点 xff0c 没搞懂怎么用 xff1f 单张图片 import cv2 import numpy as np import matplo

  • Arduino利用NTPClient库获取网络时间，显示时间与实时时间不同步

    网络时间协议 xff08 Network Time Protocol xff0c NTP xff09 是用来使计算机时间同步化的一种协议 xff0c 它可以 使计算机对其服务器或时钟源 xff08 如石英钟 xff0c GPS等等 做同步化

  • 【C++】链表反转逆序|建立、删除、修改、插入|linux内核链表与普通链表

    目录 C 43 43 实现链表逆序 链表的建立 删除 修改 插入 linux内核链表与普通链表 C 43 43 实现链表逆序 实现链表逆序 xff0c 首先要有一个链表 xff0c 下面是链表的结构体 xff1a typedef struc

  • https 自签名SSL证书

    介绍 TLS或称传输层安全性 xff0c 及其前身SSL xff08 代表安全套接字层 xff09 是用于将正常流量包装在受保护的加密包装中的Web协议 使用这种技术 xff0c 服务器可以在服务器和客户端之间安全地发送流量 xff0c 而

  • Gitlab安装及配置

    主要内容 xff1a 使用docker运行 gitlab xff1b 配置LDAP及邮箱 xff1b 配置管理员账号 xff1b 配置邮箱通知 xff1b 关闭用户注册 xff1b 安装 我们使用docker来安装Gitlab xff0c

  • windows 文件夹正在使用 “操作无法完成，因为其中的文件夹或文件已在另一程序中打开“ 解决办法

    解决办法 xff1a 右键状态栏 gt 任务管理器 gt 性能 gt 打开资源监视器 gt CPU gt 在 关联的句柄 处搜索被占用的文件夹名称 现象 解决办法 第1步 第2步 第3步 例如搜索 test 第4步 右键找到占用的程序 第5

  • Windows服务器更新服务的配置-client

    0x01 WSUS客户端 wsus客户端可以使windows的各种系统 windows7 8 10 server 无须安装任何软件 0x02 客户端配置 wsus客户端通过配置本地组策略来实现 运行中输入 xff1a gpedit msc

  • Anime4K：目前最热的开源实时动漫放大算法，Github上一周收获2600星！

    点击我爱计算机视觉标星 xff0c 更快获取CVML新技术 今天跟大家介绍一款动漫画面放大算法Anime4K xff0c 目前最热的开源 实时 视频动漫放大算法 https github com bloc97 Anime4K 一周内该库在G

  • 统计学：离散型和连续型随机变量的概率分布

    主要随机变量一览表 随机变量概率分布均值方差一般离散型变量 p x 的 表 公 式 或 者 图 x x p x x x 2 p x 二项分布 p x 61 C x n p x

  • Stata基本功能及其函数实现

    Stata简介 xff1a Stata使用简单 xff0c 功能强大 xff0c 是数据分析中常用的统计计量软件 本人使用的是Stata14 xff0c 如若需要 xff0c 可评论留言 xff01 1 Stata基本描述 1 1主窗口 如

  • MySQL安装过程问题：服务器名无效，发生系统错误 5 拒绝访问

    1 服务器名无效 安装好MySQL之后 xff0c 使用CMD系统命令程序 xff0c 输入命令启动MySQL xff0c 提示 服务器名无效 原因1 xff1a 服务器名字错误 解决方法 检查命令行中服务器名是否正确 xff0c MySQ

  • R语言基础画图/绘图/作图

    R语言基础画图 R语言免费且开源 xff0c 其强大和自由的画图功能 xff0c 深受广大学生和可视化工作人员喜爱 xff0c 这篇文章对如何使用R语言作基本的图形 xff0c 如直方图 xff0c 点图 xff0c 饼状图以及箱线图进行简

  • 【网站】IIS配置/搭建PHP环境的网站教程

    原文 xff1a https www fujieace com php iis html 今天一位群友想新搞一个万能门店小程序网站 xff0c 由于他的服务器上面已经存在了很多的老站 xff0c 用的IIS6 0服务 xff0c 现在这个网

  • MySQL数据库事务处理

    事务处理用于有效记录某机构感兴趣的业务活动 xff08 称为事务 xff09 的数据处理 xff08 例如销售 供货的定购或货币传输 xff09 通常 xff0c 联机事务处理 OLTP 系统执行大量的相对较小的事务 百度百科 事务处理是将

热门标签