mysql事务修改孤立级_MySQL事务的应用

野性酷女 2022-10-26 14:26 41阅读 0赞

1、MySQL事务概述

在MySQL中，事务由单独单元的一个或多个SQL语句组成。在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分隔的整体，如果单元中一旦某条SQL语句执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有SQL语句均执行成功。则事务被顺利执行。

ACID指出每个事务型RDBMS(Relational Database Management System，关系数据库管理系统)必须遵守的4个属性，即原子性、一致性、孤立性和持久性。

原子性：原子性意味着事物的整体性和不可分割性，这就类似化学中的原子，事务就具备这样的属性，其被认为是一个不可分割的单元。

一致性：在MySQL 事务处理过程中，无论事务是完全成功或是在中途因某些环节失败而导致失败，但事务使系统处于一致的状态时，其必须保证一致性。

孤立性：孤立性是指每个事务在自己的空间发生，和其他发生在系统中的事务隔离，而且事务的结果只在它完全被执行时才能看到。即使这样的一个系统中同时发生多个事务，孤立性也可以保证特定的事务在完成之前，其结果是不被公布的。

持久性：在MySQL中，即便是数据库系统崩溃，一个提交的事务仍然在坚持。MySQL的持久性是通过一条记录事务过程中系统变化的二进制事务日志文件来实现的。

2、MySQL事务的创建

在MySQL中，MyISAM存储引擎是不支持事务的，而InnoBD和BDB存储引擎是支持事务的。

创建事务的一般过程是：初始化事务、创建事务、提交事务或者撤销事务。

2.1 初始化事务

初始化MySQL事务，首先声明初始化MySQL事务后所有的SQL语句为一个单元。在MySQL中，应用START TRANSACTION命令来标记一个事务的开始。初始化事务的结构如下：

START TRANSACTION;

另外，用户也可以使用BEGIN或者BEGIN WORK命令初始化事务。

2.2 创建事务

初始化事务成功后，可以创建事务，通常使用START TRANSACTION命令后面跟随的是组成事务的SQL语句。

2.3 提交事务

在用户没有提交事务之前，当其他用户连接MySQL服务器时，应用SELECT语句查询结果，则不会显示没有提交的事务。当且仅当用户成功提交事务后，其他用户才可能通过SELECT语句查询事务结果，由事务的特性可知，事务具有孤立性，当事务处在处理过程中，其实MySQL并未将结果写入磁盘中，这样以来，这些正在处理的事务相对其他用户是不可见的。一旦数据被正确插入，用户可以使用COMMIT命令提交事务。提交事务的命令结构如下：

COMMIT;

一旦当前执行事务的用户提交当前事务，则其他用户就可以通过会话查询结果。

2.4 撤销事务(事务回滚)

撤销事务，又被称作事务回滚。即事务被用户开启、用户输入的SQL语句被执行后，如果用户想要撤销刚才的数据库操作，可使用ROLLBACK命令撤销数据库中的所有变化。ROLLBACK命令结构如下：

ROLLBACK;

示例：创建存储过程，使用事务往tb\_user(用户信息表)中插入一条记录。

(1)创建用户信息表。

\-- 创建用户信息表

CREATE TABLE IF NOT EXISTS tb\_user

(

id INT AUTO\_INCREMENT PRIMARY KEY COMMENT '用户编号',

name VARCHAR(50) NOT NULL COMMENT '用户姓名'

) COMMENT = '用户信息表';

(2)创建存储过程，使用事务。

\-- 创建存储过程

DROP PROCEDURE IF EXISTS porc\_user;

CREATE PROCEDURE porc\_user(IN in\_name VARCHAR(50))

BEGIN

DECLARE error INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 1;

\-- 初始化事务

START TRANSACTION;

\-- 创建事务过程

INSERT INTO tb\_user(name) VALUES(in\_name);

IF error = 1 THEN

\-- 撤销事务

ROLLBACK;

ELSE

\-- 提交事务

COMMIT;

END IF;

END;

(3)调用存储过程。

\-- 调用存储过程

CALL porc\_user('pan\_junbiao的博客\_01');

CALL porc\_user('pan\_junbiao的博客\_02');

CALL porc\_user('pan\_junbiao的博客\_03');

CALL porc\_user('pan\_junbiao的博客\_04');

CALL porc\_user('pan\_junbiao的博客\_05');

注意：事务不支持嵌套功能，当用户在未结束第一个事务又重写打开一个事务时，则前一个事务会自动提交。

执行结果：

![315a811d9c728740fdbd363388a2cee2.png][]

3、MySQL事务行为

在MySQL中，存在两个可以控制行为的变量，它们分别是AUTOCOMMIT变量和TRANSACTION ISOLACTION LEVEL变量。

3.1 自动提交

在MySQL中，如果不更改其自动提交变量，则系统会自动向数据库提交结果，用户在执行数据库操作过程中，不需要使用START TRANSACTION语句开始事务，应用COMMIT或者ROLLBACK提交事务或执行回滚操作。如果用户希望通过控制MySQL自动提交参数，可以更改提交模式，这一更改过程是通过设置AUTOCOMMIT变量来实现的。

示例：如何关闭自动提交功能。

SET AUTOCOMMIT = 0;

关闭自动提交功能后，只用当执行COMMIT命令后，MySQL才将数据表中的资料提交到数据库中，如果不提交事务，而终止MySQL会话，数据库将会自动执行回滚操作。

3.2 事务的孤立级(隔离级别)

事务具有独立的空间，在MySQL服务器中，用户通过不同的会话执行不同的事务，在多用户环境中，许多RDBMS会话在任意指定时刻都是活动的。为了使这些事务互不影响，保证数据库性能不受到影响，采用事务的孤立级是十分有必要的。

孤立级在整个事务中起到了很重要的作用，如果没有这些事务的孤立性，不同的SELECT语句将会在同一事务的环境中检索到不同的结果，这将导致数据的不一致性。给不同的用户造成困扰，这样来，用户就不能将查询的结果集作为计算基础。所以孤立性强制保持每个事务的独立性，以此来保证事务中看到一致的数据。

基于ANSI/ISO SQL规范， MySQL提供以下4种孤立级。

(1)SERIALIZABLE(序列化)：顾名思义，以序列的形式对事务进行处理，该孤立级的特点是只有当事务提交后，用户才能从数据库中查看数据的变化。该孤立级运行会影响MySQL的性能，因为需要占用大量资源，以保证使大量事务在任意时间不被用户看到。

(2)REPEATABLE READ(可重读)：MySQL的默认孤立级。对于应用程序的安全性作出部分妥协，以提高其性能。事务在该孤立级上不会被看成一个序列，不过当前在执行事务的过程中，用户仍然看不到事务的过程。直到事务提交为止，用户才能够看到事务的变化结果。

(3)READ COMMITTED(提交后读)：提交后读孤立级的安全性比重复读安全性要低。在这级的事务，用户可以看到其他事务添加的新记录。在事务处理时，如果存在其他用户同时对事务的相应表进行修改，那么在同一事务中不同时间内，应用 SELECT语句可能返回不同的结果集。

(4)READ UNCOMMITTED(未提交读)：该孤立级提供事务之间的最小程度间隔，该孤立级易产生虚幻读操作。其他用户可以在该孤立级上看到未提交的事务。

3.3 修改事务的孤立级

在MySQL中，可以使用TRANSACTION ISOLATION LEVEL变量修改事务孤立级，其中，MySQL的默认孤立级为REPEATABLE READ(可重读)，用户可以使用SELECT命令获取当前事务孤立级变量的值，其命令如下：

SELECT @@tx\_isolation;

如果用户希望修改事务的孤立级，可以通过SET命令设置其不同值来修改事务的孤立级。

示例：将事务的孤立级修改为：READ COMMITTED(提交后读)。

SET GLOBAL TRANSACTION ISOLATION LEVEL

READ COMMITTED;

4、事务的性能

应用不同孤立级的事务可能会对系统造成一系列影响，采用不同孤立级处理事务，可能会对系统稳定性和安全性等诸多因素造成影响。另外，有些数据库操作中，不需要应用事务处理，则用户在选择数据表类型时，需要选择合适的数据表类型。所以，在选择表类型时，应该考虑数据表具有完善的功能，且高效的执行前提下，也不会对系统增加额外的负担。

4.1 应用小事务

应用小事务的意义在于：保证每个事务不会在执行前等待很长时间，从而避免各个事务因为互相等待而导致系统的性能大幅度下降。

4.2 选择合适的孤立级

因为事务的性能与其对服务器产生的负载成反比，即当事务孤立级越高，其性能越低，但是其安全性也越高。如下图：

![e828db048de5f56d4794907efd14bcd6.png][]

4.3 死锁的概念与避免

死锁，即当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态。在实际应用中，两个不同序列的客户打算同时对数据执行操作，极有可能产生死锁。更具体的讲，当两个事务相互等待操作对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称为死锁。

不过，MySQL的InnoDB表处理程序具有检查死锁这一功能，如果该处理程序发现用户在操作过程中产生死锁，该处理程序立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样就可以使另一个事务获取对方所占有的资源而执行逻辑操作。

[315a811d9c728740fdbd363388a2cee2.png]: /images/20221024/3e023b6b75ff45db81d007c1b34e3f73.png
[e828db048de5f56d4794907efd14bcd6.png]: /images/20221024/bb41580a8f914e258d094f43e0bd0894.png