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。

相关文章

宏的优缺点？C++有哪些技术替代宏？（const）权限的平移、缩小

宏的优缺点？C++有哪些技术替代宏？（const）权限的平移、缩小

宏的优缺点？ 优点： 1.增强代码的复用性。【减少冗余代码】 2.提高性能，提升代码运行效率。 缺点： 1.不方便调试宏。（因为预编译阶段进行了替换） 2.导致代码可读性差，可维护性差，容易误用。 3.没有类型安全的检查 。没有安全的检查，具有副作用。 C++有哪些技术替代宏？ 1. 常量定义 换用const enum 宏常量：用const定义常量来替代宏常量。 2. 短
阅读更多...
mysql简单存储过程范例之遍历数据库生成视图

mysql简单存储过程范例之遍历数据库生成视图

范例： BEGIN-- 定义参数declare done int(10);declare p_feeCode varchar(50);declare abc varchar(50);declare sqlstr varchar(500);-- 定义游标declare feeCodeCursor cursor for select TABLE_NAME FROM INFOR
阅读更多...
mysql简单存储过程范例

mysql简单存储过程范例

范例： BEGIN-- 定义参数 declare carId int(10);declare v_count int;declare done int default 1;-- 一个游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)declare logisticCarInfoCursor cursor for select id from
阅读更多...
案例： 模拟登陆，获取Druid SQL监控数据

案例： 模拟登陆，获取Druid SQL监控数据

本文重在编写模拟登陆的代码；Druid SQL监控 admin 平台，重在辅助验证； 本案例采用springboot框架； 1.jar包版本信息： <!-- alibaba的druid数据库连接池 --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><ve
阅读更多...
SQLSERVER CPU占用过高的优化

SQLSERVER CPU占用过高的优化

有同事反应服务器CPU过高，一看截图基本都是100%了，my god，这可是大问题，赶紧先看看。 让同事查看系统进程，发现是SQLServer的CPU占用比较高。首先想到的是不是报表生成的时候高，因为这块之前出现过问题，关掉服务程序，还是高。难道是客户端程序引发的？但是这么多的客户端连接，难不成每个都叫人关闭，很简单，把网络断开即可。网络断开之后，CPU立马下降。那么问题到底在哪里呢，是
阅读更多...
windows系统API学习

windows系统API学习

win代码 1.代码： GetLogicalDriveStrings(1024,(LPWSTR)DStr); 解释 DWORD GetLogicalDriveStringsW([in] DWORD nBufferLength,[out] LPWSTR lpBuffer);[in] nBufferLengthTCHAR 中由 lpBuffer 指向的缓冲区的最大大小。 此大小不包括终
阅读更多...
【大学物理】双语笔记

【大学物理】双语笔记

7.5 angular momentu(角动量)_哔哩哔哩_bilibili 6.4Energy in Rotation Motion 有质量有速度的物体有动能，是不是很有道理 international system（from French systeme international，acronym，SI）of ineria kg*m^2 转动动能：物体由于运动所具有的能量
阅读更多...
C++面向对象学习笔记三

C++面向对象学习笔记三

本文学习大佬的文章，所摘录和整理的一些知识《C++面向对象程序设计》✍千处细节、万字总结（建议收藏）_c++面向对象程序设计千处细节-CSDN博客 文章目录  文章目录  前言 正文 const修饰符 void型指针 内联函数 带有默认参数值的函数  new和delete运算符 总 前言 本文补充了C++的一些基础知识点 正文 const修饰符 在C语言中，我们常用
阅读更多...
从零开始的软件测试学习之旅(九)jmeter直连数据库及jmeter断言,关联

从零开始的软件测试学习之旅(九)jmeter直连数据库及jmeter断言,关联

jmeter直连数据库及断言,关联 jmeter直连数据库步骤jmeter断言jmeter逻辑控制器if控制器ForEach控制器循环控制器 Jmeter关联Jmeter关联XPath提取器Jmeter关联正则表达式提取器二者比较跨线程组关联 每日复习 jmeter直连数据库 概念 这不叫直连:Jmeter -> java/python 提供的接口 -> 访问数据库 这才叫直连:
阅读更多...
关于linux的进阶配置（mysql）你需要知道（2）-数据库和表的管理

关于linux的进阶配置（mysql）你需要知道（2）-数据库和表的管理

1、文件类型 (1)数据文件 主数据文件(.mdf):有且只有一个 次要数据文件(.ndf):可有可无，有可以多个 (2)事务日志文件(.ldf):至少有一个 记录所有事务的SQL语句 用于恢复数据库 2、快照数据库的方法: 方法一:扩展现有文件的自动增长设置 方法二：添加新文件(次要数据文件) 3、收缩数据库的方法: 方法一:手动收缩(针对数据库和文件) 方法二:自动收缩(针对数据库) 4
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2