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

水深无声 2022-10-29 10:23 30阅读 0赞

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

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 ;

先查看当前重做日志情况：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]  
上图中的Log sequence number表示当前LSN，Log flushed up to表示刷新到重做日志文件的LSN，Last checkpoint at表示刷新到磁盘的LSN，接下来调用存储过程插入数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]  
此次的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的测试：  
![在这里插入图片描述][20210214153650316.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]  
从上图可见两次插入2的操作是在一个事务中，但并没有使用BEGIN等命令显式开始一个事务。

将completion\_type设为2的测试：  
![在这里插入图片描述][20210214154000492.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]  
事务中如果一条语句失败，这条语句会自动回滚，但整个事务不会回滚，这条语句之前的同事务中的语句不受影响，还需用户手动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的变量，只有显式提交的事务才会被计算到两个变量中：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]  
参数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，会出现以下错误：  
![在这里插入图片描述][23891e00a63c4b15b82416dca0aeb1b0.png]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZCD552A54Gr6ZSFeOWUseedgOatjA_size_16_color_FFFFFF_t_70_g_se_x_16]  
MySQL 5.0前不支持ROW格式的二进制日志，此时可将参数innodb\_locks\_unsafe\_for\_binlog设置为1，从而允许在二进制日志格式为STATEMENT下使用READ COMMITTED事务隔离级别，但它是unsafe的，某些情况下会导致master和slave之间数据不一致，如有以下表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]  
在master上开始一个事务A，先不提交：  
![在这里插入图片描述][20210216160243466.png]  
再在master上开始一个事务B，这次提交：  
![在这里插入图片描述][20210216160321683.png]  
之后提交事务A：  
![在这里插入图片描述][20210216160355127.png]  
查看master上的数据：  
![在这里插入图片描述][2021021616041750.png]  
查看slave上的数据：  
![在这里插入图片描述][20210216160431696.png]  
数据产生了不一致，产生此问题的原因有两点，首先，在READ COMMITTED事务隔离级别下，事务没有Gap Lock锁，因此事务B可以在小于等于5的范围内再插入一条数据；其次，STATEMENT格式的二进制日志文件中记录的是master上产生的SQL语句，因此在master上实际执行是先删后插，而在二进制日志文件中记录的是先插后删，逻辑上产生了不一致。而REPEATABLE READ可以避免第一种情况的发生。在MySQL 5.1版本后，支持了ROW格式的二进制日志记录格式，避免了第二种情况的发生。

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

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

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

分布式事务由一个或多个资源管理器、一个事务管理器、一个应用程序组成：  
1.资源管理器：提供访问事务资源的方法，通常一个数据库就是一个资源管理器。  
2.事务管理器：协调参与全局事务中的各个事务，需要和参与全局事务的所有资源管理器通信。  
3.应用程序：定义事务边界，指定全局事务中的操作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]  
分布式事务使用两段式提交方式，第一个阶段，所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了；第二个阶段，事务管理器告诉资源管理器执行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 DO
    	INSERT 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 DO
    	INSERT 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的经典问题，因此不应在一个循环中反复进行提交操作。

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时会报错，因此会进行自动回滚操作：  
![在这里插入图片描述][20210217154510766.png]  
但这样不知道存储过程是否出错了，以下方式可以知道存储过程是否出错：

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;

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]: /images/20221024/fc384d5274d94ff7ba7b736395a4a041.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]: /images/20221024/d14f464c23c546f8b7f2105693787ce0.png
[20210214153650316.png]: /images/20221024/94f52d5bb8de4605b49e96abf3e25a92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]: /images/20221024/21b4d9b06d9c4dc68814ec5100999837.png
[20210214154000492.png]: /images/20221024/84f55a3d7d1047dd83a3dff03c06cef8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]: /images/20221024/b950ce00703c46c5b72c63c7d266b776.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]: /images/20221024/8068e92715ce4c188d416a37fc801ded.png
[23891e00a63c4b15b82416dca0aeb1b0.png]: /images/20221024/0a43788cecf44b6e9c0b803621c710ef.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZCD552A54Gr6ZSFeOWUseedgOatjA_size_16_color_FFFFFF_t_70_g_se_x_16]: /images/20221024/800c519228df4abab3b9a875271b64fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]: /images/20221024/f9a3b9c56b3e4a31a578b4d482b69012.png
[20210216160243466.png]: /images/20221024/b843414ba0a24248b9760d0efde953b7.png
[20210216160321683.png]: /images/20221024/bff6e82187c94aa889fc8ef6d32809e5.png
[20210216160355127.png]: /images/20221024/52e3f657b9d84f1799f8203aa02ecd2e.png
[2021021616041750.png]: /images/20221024/9c5c7a94b22d4b7086c6177fb6edfaea.png
[20210216160431696.png]: /images/20221024/4b087ca1fdaa4d94b5e93bf358c7aef4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]: /images/20221024/3512e5aebfd941598c530c7dcf6d250a.png
[20210217154510766.png]: /images/20221024/6957416c64bf4d5da9e575485917be90.png
[20210217160829914.png]: /images/20221024/fb831d85ea274a3d9738d42aabec9d84.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 7]: /images/20221024/83c8178cdb964c76bb95b1daf85bbcbb.png
[20210217161402882.png]: /images/20221024/9730327322464796bcb01b5791431412.png