MySQL技术neimu InnoDB存储引擎 学习笔记 第七章 事务

2024-04-27 13:48
文章标签 mysql neimu innodb 第七章 笔记 存储 引擎 事务 学习 技术 database

本文主要是介绍MySQL技术neimu InnoDB存储引擎 学习笔记 第七章 事务，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

事务可确保把数据库从一种一致状态转换为另一种一致状态，在事务提交时，可确保要么所有修改都被保存了，要么所有修改都不被保存。

InnoDB引擎事务完全符合ACID特性：
1.原子性（atomicity），指整个事务是不可分割的工作单位。
2.一致性（consistency），指事务前后数据库的完整性约束没有被破坏。
3.隔离性（isolation），指一个事务的影响在该事务提交前对其他事务都不可见，通过锁来实现。
4.持久性（durability），事务提交后，结果是永久性的，即使发生了宕机，数据库数据也能恢复。

原子性、一致性、持久性通过redo和undo完成。

InnoDB的事务日志通过重做（redo）日志文件和InnoDB引擎的日志缓冲实现，当开始一个事务时，会记录该事务的一个LSN（Log Sequence Number，日志序列号），事务执行时，会往InnoDB引擎的日志缓冲里插入事务日志，当事务提交时，必须将InnoDB引擎的日志缓冲写入磁盘（默认实现，即innodb_flush_log_at_trx_commit=1）。即在写数据前，需要先写日志，这种方式称为预写日志方式（Write-Ahead Logging，WAL）。

InnoDB使用WAL方式保证事务完整性，意味着磁盘上的数据页和内存缓冲池中的页是不同步的，对于内存缓冲池中的页的修改，先将其写入重做日志文件，然后再写入磁盘，是一种异步的方式。查看当前磁盘和日志的差距的命令：

SHOW ENGINE INNODB STATUS;。

磁盘内容与日志内容差距的例子：

CREATE TABLE z (a      INT,PRIMARY KEY(a)
) ENGINE = InnoDB;DELIMITER $$CREATE PROCEDURE load_test(count INT)
BEGIN
DECLARE i INT UNSIGNED DEFAULT 0;
START TRANSACTION;
WHILE i < count DO
INSERT INTO z SELECT i;
SET i = i + 1;
END WHILE;
COMMIT;
END; $$DELIMITER ;

先查看当前重做日志情况：
在这里插入图片描述
上图中的Log sequence number表示当前LSN，Log flushed up to表示刷新到重做日志文件的LSN，Last checkpoint at表示刷新到磁盘的LSN，接下来调用存储过程插入数据：
在这里插入图片描述
此次的Log flushed up to和Last flushed up to值不相等了。虽然上例中Log sequence number和Log flushed up to的值相等，但生产环境中可能是不相等的，因为一个事务从日志缓冲刷新到重做日志文件并不只是在事务提交时发生，还可能在一个重做日志文件组中的一个重做日志文件满后切换重做日志后发生。

对数据库修改时，除了会产生redo，还会产生undo，当执行的事务或语句失败了，或用ROLLBACK命令时会发生回滚，可利用这些undo信息将数据回滚到修改之前的样子。undo存放在共享表空间内的undo段中。

undo用于将数据库逻辑地恢复到原来的样子，并不是物理地恢复，如一个事务在修改一个页中某几条记录，但同时还有别的事务修改同一个页中的另外几条记录，因此不能将一个页回滚到事务开始的样子，这样会影响其他事务。当我们的事务插入了大量数据，造成新的段分配，即表空间会增大，此时再ROLLBACK，会将插入的事务回滚，但表空间大小不会收缩。

即使对修改数据的事务进行了提交，一段时间内undo页也会存在，这是因为undo页的回收是在master thread中进行的，master thread也不是一次回收所有undo页的。

MySQL命令行默认配置下，事务是自动提交的，命令行中开始一个事务要使用BEGIN或START TRANSACTION或SET AUTOCOMMIT=0，这点和SQL server相同，而Oracle默认是不自动commit的。

MySQL事务控制语句：
1.START TRANSACTION | BEGIN：显式开启一个事务。
2.COMMIT：几乎等价于COMMIT WORK，提交事务。当参数completion_type为0时（默认设置），COMMIT和COMMIT WORK完全一致；当该参数为1时，COMMIT WORK等价于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务；当该参数为2时，COMMIT WORK等价于COMMIT AND RELEASE，表示当事务提交后自动断开与服务器的连接。
3.ROLLBACK：几乎等价于ROLLBACK WORK，不同点与2相同，回滚事务，撤销事务中的修改。
4.SAVEPOINT identifier：在事务中创建一个保存点，一个事务中可以有多个保存点。
5.RELEASE SAVEPOINT identifier：删除一个事务保存点，当identifier不存在时，执行这条语句会抛出一个异常。
6.ROLLBACK TO [SAVEPOINT] identifier：与SAVEPOINT命令一起使用，可把事务回滚到指定保存点。如identifier不存在会抛出异常。此命令不会像ROLLBACK一样结束事务，之后需要显式结束事务。
7.SET TRANSACTION：设置事务的隔离级别。

在存储过程中，不能使用BEGIN显式地开启一个事务，因为MySQL存储过程中的BEGIN已有特殊含义，因此存储过程中只能用START TRANSACTION开启一个事务。

将completion_type设为1的测试：
在这里插入图片描述
在这里插入图片描述
从上图可见两次插入2的操作是在一个事务中，但并没有使用BEGIN等命令显式开始一个事务。

将completion_type设为2的测试：
在这里插入图片描述
在这里插入图片描述
事务中如果一条语句失败，这条语句会自动回滚，但整个事务不会回滚，这条语句之前的同事务中的语句不受影响，还需用户手动COMMIT或ROLLBACK。

以下SQL语句会产生隐式的COMMIT操作：
1.DDL语句：ALTER DATABASE … UPGRADE DATA DIRECTORY NAME（更新与数据库关联的目录名称）、ALTER EVENT（修改事件，事件可以执行定时任务）、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE DATABASE、CREATE EVENT、CREATE INDEX、CREATE PROCEDURE、CREATE TABLE、CREATE TRIGGER、CREATE VIEW、DROP DATABASE、DROP EVENT、DROP INDEX、DROP PROCEDURE、DROP TABLE、DROP TRIGGER、DROP VIEW、RENAME TABLE、TRUNCATE TABLE。
2.隐式修改mysql库的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。
3.管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX、INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE。

SQL server中，即使是DDL语句也能回滚。

TRUNCATE TABLE与DELETE整张表结果相同，但前者是DDL语句，不能被回滚。

InnoDB引擎支持事务，因此在考虑每秒请求数（QPS，Question Per Second）的同时，更应关注每秒事务处理能力（TPS，Transaction Per Second），TPS计算方法是(com_commit + com_rollback) / time，其中com_commit和com_rollback是MySQL的变量，只有显式提交的事务才会被计算到两个变量中：
在这里插入图片描述
参数handler_commit、handler_rollback在MySQL 5.1中可很好地用来统计InnoDB引擎显式和隐式的事务提交操作，但在InnoDB Plugin中有问题。

ISO C和ANSI SQL指定了四种事务隔离级别标准，但很少有数据库开发商遵循这些标准，Oracle就不支持read uncommitted和repeatable read事务隔离级别。SQL标准定义的四个隔离级别为：
1.READ UNCOMMITTED
2.READ COMMITTED
3.REPEATABLE READ
4.SERIALIZABLE

READ UNCOMMITTED称为浏览访问。READ COMMITTED称为游标稳定。REPEATABLE READ没有幻读（同一个事务两次做同一条查询时，后一次查看到了前一次没看到的行）保护，在这种事务隔离级别下，快照读读取的是MVCC中事务开始时的快照，因此，普通的SELECT操作是读不到当前事务过程中其他事务做出的更改的，而如果当前事务中执行的语句是要修改数据的语句或加锁的语句，如UPDATE、DELETE、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE，此时的读称为当前读，因为这些语句读的不是快照，而是真正的数据，此时会出现SELECT不到数据但能删除数据的情况，即出现了幻读，因此为了防止当前读出现幻读，需要手动对SELECT语句加锁，这样对于范围搜索，MySQL会使用Next-Key Lock对搜索的范围加锁，其他事务就不能修改这些语句了，这样对于同一个事务即使当前读也不会产生幻读了。因此，InnoDB引擎在REPEATABLE READ下已经能达到SQL标准的SERIALIZABLE隔离级别了，因此SERIALIZABLE事务隔离级别主要用于InnoDB引擎的分布式事务。

事务隔离级别越低，事务请求的锁越少或保持锁的时间越短。

设置当前会话或全局的事务隔离级别：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

如果想在MySQL启动时就设置事务的默认隔离级别，需要修改MySQL配置文件：

[mysqld]
transaction-isolation = READ-COMMITTED

查看当前会话的事务隔离级别：

SELECT @@tx_isolation;

查看全局事务隔离级别：

SELECT @@global.tx_isolation;

在SERIALIZABLE事务隔离级别下，InnoDB引擎在每个SELECT操作后自动添加LOCK IN SHARE MODE，因此此事务隔离级别下不再支持一致性的非锁定读。

READ COMMITTED事务隔离级别下，只有在唯一性的约束检查和外键约束检查时需要Gap Lock。此事务隔离级别在MySQL 5.1中只能工作在二进制日志为ROW的格式下，如果二进制日志格式为默认的STATEMENT，会出现以下错误：
在这里插入图片描述
在这里插入图片描述
MySQL 5.0前不支持ROW格式的二进制日志，此时可将参数innodb_locks_unsafe_for_binlog设置为1，从而允许在二进制日志格式为STATEMENT下使用READ COMMITTED事务隔离级别，但它是unsafe的，某些情况下会导致master和slave之间数据不一致，如有以下表：
在这里插入图片描述
在master上开始一个事务A，先不提交：
在这里插入图片描述
再在master上开始一个事务B，这次提交：
在这里插入图片描述
之后提交事务A：
在这里插入图片描述
查看master上的数据：
在这里插入图片描述
查看slave上的数据：
在这里插入图片描述
数据产生了不一致，产生此问题的原因有两点，首先，在READ COMMITTED事务隔离级别下，事务没有Gap Lock锁，因此事务B可以在小于等于5的范围内再插入一条数据；其次，STATEMENT格式的二进制日志文件中记录的是master上产生的SQL语句（写入时机是事务提交时，sync_binlog参数控制的只是提交几次事务（write几次）再进行fsync），因此在master上实际执行是先删后插，而在二进制日志文件中记录的是先插后删，逻辑上产生了不一致。而REPEATABLE READ可以避免第一种情况的发生。在MySQL 5.1版本后，支持了ROW格式的二进制日志记录格式，避免了第二种情况的发生。

建议使用ROW格式的二进制日志，因为它记录的是行的变更情况，而不是简单的SQL语句，可以避免一些不同步现象的产生。

InnoDB引擎支持XA事务，通过XA事务可支持分布式事务的实现。分布式事务指允许多个独立的事务资源参与一个全局事务，事务资源通常是关系型数据库系统，但也可以是其他资源，全局事务要求在其中所有参与的事务要么都提交，要么都回滚。使用分布式事务时，InnoDB引擎的事务隔离级别必须是SERIALIZABLE。

XA事务允许不同数据库间的分布式事务，只要参与全局事务的每个节点都支持XA事务。

分布式事务由一个或多个资源管理器、一个事务管理器、一个应用程序组成：
1.资源管理器：提供访问事务资源的方法，通常一个数据库就是一个资源管理器。
2.事务管理器：协调参与全局事务中的各个事务，需要和参与全局事务的所有资源管理器通信。
3.应用程序：定义事务边界，指定全局事务中的操作。
在这里插入图片描述
分布式事务使用两段式提交方式，第一个阶段，所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了；第二个阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT，如果任何一个节点显示不能提交，则所有节点都被告知需要回滚。

Java的JTA（Java Transaction API）可以很好地支持MySQL的分布式事务。

innodb_support_xa参数可以查看是否启用了XA事务支持，默认为ON。

对于XA事务的支持，是在MySQL的引擎层，因此，即使不参与外部的XA事务，MySQL内部不同引擎层也会使用XA事务，假设用START TRANSACTION开启了一个本地事务，往NDB Cluster引擎的表t1插入一条记录，往InnoDB引擎的表t2插入一条记录，然后COMMIT，在MySQL内部也是通过XA事务协调的，这样才能保证两张表操作的原子性。

不好的事务习惯：
1.在循环中提交，一个不好的存储过程：

CREATE PROCEDURE load1(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80)     DEFAULT REPEAT('a', 80);
WHILE s <= count DOINSERT INTO t1 SELECT NULL, c;COMMIT;SET s = s + 1;
END WHILE;
END;

在以上例子中，COMMIT可去掉，因为InnoDB引擎默认自动提交，即使去掉了COMMIT，也存在一个问题，当发生错误时，数据库会停留在一个中间的位置，如我们想插入10000条数据，但在插入了5000条数据时出现了错误，此时已经有5000条数据被插入了。还有个问题是上面的存储过程性能低，没有以下这种将整个过程放入一个事务中快：

CREATE PROCEDURE load1(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80)     DEFAULT REPEAT('a', 80);
START TRANSACTION;
WHILE s <= count DOINSERT INTO t1 SELECT NULL, c;SET s = s + 1;
END WHILE;
COMMIT;
END;

以上将插入过程放入同一个事务的方法快得多，这是因为每一次提交都要写一次重做日志。第一个存储过程可将COMMIT去掉，并按以下方式调用，也可以提高性能：

BEGIN;
CALL load2(10000);
COMMIT;

MySQL的InnoDB引擎没有其他数据库中对于事务应尽快地释放，不能长时间地占有事务的要求，也不存在Oracle数据库中由于没有足够UNDO产生Snapshot Too Old的经典问题（由于undo段大小限制，导致修改大量数据后，undo段被覆盖，一个事务中的查询找不到事务开始时的数据版本），因此不应在一个循环中反复进行提交操作。

2.使用自动提交。在显式开启事务后，在默认设置下（completion_type参数为0），MySQL会自动执行SET AUTOCOMMIT=0的命令，并在COMMIT或ROLLBACK后执行SET AUTOCOMMIT=1。

不同语言的API的自动提交情况不同。MySQL C API默认自动提交，而MySQL Python API会自动执行SET AUTOCOMMIT=0。

3.使用自动回滚。InnoDB引擎支持通过定义一个HANDLER来进行事务的自动回滚操作，如果存储过程中发生了错误，会自动对其进行回滚，如以下存储过程：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
END;

以上存储过程先定义了一个exit类型的handler，当捕获到错误时进行回滚。表b中只有一列INT主键列，因此插入第二个1时会报错，因此会进行自动回滚操作：
在这里插入图片描述
但这样不知道存储过程是否出错了，以下方式可以知道存储过程是否出错：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; END;    -- HANDLER后可加一个BEGIN END语句块，发生异常时会调用此语句块
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
END;

运行以上存储过程：
在这里插入图片描述
但以上方法只能知道发生了异常，但不知道发生了什么样的错误。SQL server数据库中可使用SET XACT_ABORT ON来使得发生异常时回滚一个事务，并且还会抛出一个异常，开发人员可以捕获这个异常来获取详细信息。在MySQL中，我们应使用程序控制事务而非在存储过程中控制事务，这样可以在程序中捕获异常：
在这里插入图片描述
在这里插入图片描述

这篇关于MySQL技术neimu InnoDB存储引擎 学习笔记 第七章 事务的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/940673。

相关文章

纹理学习总结

纹理学习总结

纹理 每个定点都需要绑定纹理坐标。什么是纹理坐标？纹理坐标有什么特点 纹理坐标的范围是0到1，不论是x轴还是y轴。用纹理坐标获取纹理颜色的过程叫做采样。 纹理过滤 1. 由于纹理坐标不依赖与分辨率，也就是说OpenGL如果将一张照片映射到一个很大的面上则会清晰地看到像素点，这时候过滤就显得很重要了。 2. OpenGL默认的过滤方式是邻近过滤，即选择纹理部分最接近需要映射的纹理坐标的
阅读更多...
Linux 内核工作队列之work_struct 学习总结

Linux 内核工作队列之work_struct 学习总结

前言 编写Linux驱动的时候对于work_struct的使用还是很普遍的，很早之前就在阅读驱动源码的时候就看到了它的踪影，根据其命名大概知道了它的具体作用，但是仍然不知所以，同时，伴随出现的还有delayed_work以及workqueue_struct，抱着知其然并知其所以然的态度，在这里归纳总结一下work_struct，以及如何在驱动中使用，因为工作队列相对来说比较复杂，篇幅和能力有限，
阅读更多...
Linux内核驱动学习（五）KThread学习总结

Linux内核驱动学习（五）KThread学习总结

文章目录 简介例程运行结果参考 简介 使用内核线程需要包含头文件#include <linux/kthread.h>，下面整理了一下常用的api接口，如下表格所示； 函数功能struct task_struct * kthread_create(threadfn, data, namefmt, arg...)创建一个线程struct task_struct * kthread
阅读更多...
Linux内核驱动学习（四）Platform设备驱动模型

Linux内核驱动学习（四）Platform设备驱动模型

Linux platform设备驱动模型 文章目录 Linux platform设备驱动模型前言框架设备与驱动的分离设备（device）驱动（driver）匹配（match） 参考 前言 为什么要往平台设备驱动迁移？这里需要引入设备，总线，驱动这三个概念。上一篇字符型设备驱动的实现实际将设备和驱动集成到同一个文件中实现，如果这里有硬件A的驱动，硬件B的驱动，硬件C的驱动，然后
阅读更多...
Linux内核驱动学习（三）字符型设备驱动之初体验

Linux内核驱动学习（三）字符型设备驱动之初体验

Linux字符型设备驱动之初体验 文章目录 Linux字符型设备驱动之初体验前言框架字符型设备程序实现cdevkobjownerfile_operationsdev_t 设备注册过程申请设备号注册设备register_device 如何构建模块编译内核编译MakefileKconfig 总结参考 前言 驱动总共分为字符型设备驱动，块设备驱动，网络设备驱动。对于字符型设备驱
阅读更多...
Linux内核驱动学习（二）添加自定义菜单到内核源码menuconfig

Linux内核驱动学习（二）添加自定义菜单到内核源码menuconfig

文章目录 目标drivers/Kconfigdemo下的Kconfig 和 MakefileKconfigMakefiledemo_gpio.c 目标 Kernel：Linux 4.4 我编写一个简单的hello worldLinux 内核模块后，已经可以通过insmod动态加载到系统内核中，并通过rmmod卸载模块。但是出于学习的目的，我想把这个内核添加到Linux源码中
阅读更多...
有感FOC算法学习与实现总结

有感FOC算法学习与实现总结

文章目录 基于STM32的有感FOC算法学习与实现总结1 前言2 FOC算法架构3 坐标变换3.1 Clark变换3.2 Park变换3.3 Park反变换 4 SVPWM5 反馈部分5.1 相电流5.2 电角度和转速 6 闭环控制6.1 电流环6.2 速度环6.3 位置环 写在最
阅读更多...
我这两年收藏的嵌入式AI资源，并做了学习笔记

我这两年收藏的嵌入式AI资源，并做了学习笔记

有粉丝问我：“当前乃至未来5-10年，嵌入式开发者还有哪些风口？” 画外音：风口的本质，其实就是一段时间的人才供需不平衡。说白了就是由于行业突变，敏锐的资本快速进入，导致短时间内行业大量扩张，需要大量开发者。 目前的嵌入式开发越来越倾向于智能化，也就是我们所说的智能硬件（硬件+软件）。 以百度机器人为例，机器人的核心是大脑，即是“数据和算法” ，但机器人大脑想机器人身躯能够像人类一样活动，能说会
阅读更多...
嵌入式学习真的这么烧钱吗？

嵌入式学习真的这么烧钱吗？

大家好，我是小麦，从一开始接触嵌入式到现在，已经已经有十年了。 在这期间走过很多弯路，踩过很多坑。 平时和搞技术朋友聊到嵌入式的各种坑的时候，都会不约而同地提到这几点，  硬件的成本高，比较氪金，技术面比较杂，比较多，软硬件联调很麻烦等等； 偶尔在知乎上看到一篇不错的文章，文中大部分观点都不错，在这里分享出来。 作者：匿名大佬 转自：https://www.zhihu.com/question/
阅读更多...
IDEA +maven git tomcat database数据库 调试 插件 log4j Spring junit

IDEA +maven git tomcat database数据库 调试 插件 log4j Spring junit

前言 idea优化配置、常规配置、配置maven、git、tomcat、database数据库、调试配置、插件配置、log4j配置、Spring配置等等，稍后一一更新！ 优化配置(#item1 “item1”) 打开文件 ：“idea – > bin – >idea64.exe.vmoptions” -Xms： 初始内存；-Xmx ： 最大内存；-ReservedCodeCache
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2