临川
文章
33
分类
6
评论
0

事务基础知识

| MySQL •
字数总计 14046 | 阅读时长 35分钟 | 阅读量 260

1. 数据库事务概念

事务是让数据库始终保持一致性

1.1 存储引擎支持情况

SHOW ENGINES 查看 MySQL 支持的存储引擎有哪些，以及这些存储引擎是否支持事务

Snipaste_2022-07-27_09-24-31

1.2 基本概念

**事务：**一组逻辑操作单元，使数据从一种状态变为另一种状态

事务处理的原则：保证所有事务都是作为一个工作单位执行，即使出现错误。当事务中执行了多个操作时，要么事务中所有操作提交(commit)，则这些操作就会永久生效；要么数据库不进行所有的操 作，整个事务回滚(rollback)到最初状态

1.3 事务的ACID 特性

  • 原子性(atomicity)：

原子性是指事物是一个不可分割的工作单元，要么其中所有操作都执行，要么都回滚。

  • 一致性（consistency）：

一致性是指事物执行前后，数据从一个合法性状态变换为另外一个合法性状态。这种状态是语义上的，而不是语法上的，与具体的业务有关。（满足预定的约束的状态）

举例：A账户有 200 元，转账 300 元出去，此时A账户余额为 -100元。此时数据是不一致的，因为定义了一个状态，余额必须 >= 0

举例：在表中设置了姓名字段为唯一性约束，此时事务进行提交时，若出现表中姓名不唯一，则就破坏了事务的一致性

  • 隔离性（isolation）：

隔离性是指一个事物执行不能被其他事务干扰。并发执行的各个事务之间不能互相干扰

  • 持久性（durability）：

持久性是指一个事务一旦提交，则对数据的改变是永久的。

持久性是通过事务日志保证的。日志包括了重做日志和回滚日志。

总结：

ACID 是事务的四大特性，原子性是基础，隔离性是手段，一致性是约束条件，持久性是目的

1.4 事务的状态

  • 活动的（active）

事务对应的数据库操作正在执行的过程中

  • 部分提交（partially committed）

事务中最后一个操作执行完成，但是操作都是在内存中执行的，还没有刷新到磁盘中

  • 失败的（failed）

事务处于活动的或部分提交时，遇到某些错误（数据库自身的错误、操作系统错误或者间接断电等）导致无法继续执行，或是人为停止当前事务的执行

  • 中止的（aborted）

若事务执行了一部分变为失败的状态，则需要进行回滚。回滚执行完成后，则该事务处于 中止的状态

  • 提交的（committed）

事务操作执行完成后，将修改后的数据同步到磁盘中

状态转换图

注意：回滚到保存点（savepoint），此时事务并不是处于中止状态

2. 如何使用事务

事务分为：显式事务和隐式事务

2.1 显式事务

步骤1：显式开启事务。START TRANSACTION 或 BEGIN

BEGIN;
或
START TRANSACTION;

START TRANSACTION 语句与 BEGIN 不同之处在于：后面可以跟修饰词

  • READ ONLY ：当前事务是一个只读事务，该事务的数据库操作只能读取数据，不能修改数据

只读事务只是针对不允许修改那些其他事务也可以访问到的表的数据，临时表(CREATE TMEPORARY TABLE)中，是可以对临时表进行增、删、改操作的

  • READ WRITE：当前事务是一个读写事务
  • WITH CONSISTENT SNAPSHOT：启动一致性读。

注意点： WITH CONSISTENT SNAPSHOT 可以和READ ONLY / READ WRITE 搭配使用，但是READ ONLY 和 READ WRITE 不可搭配使用

步骤2：事务中的一系列操作(主要是 DML)

步骤3：提交事务 或 中止事务（即回滚事务）

COMMIT; 提交事务
ROLLBACK;  回滚事务
ROLLBACK TO [SAVEPOINT] 事务回滚到某个保存点

其中关于 SAVEPOINT 相关操作：

在事务中创建保存点
SAVEPOINT 保持点名称;
删除某个保存点
RELEASE SAVEPOINT 保存点名称;

2.2 隐式事务

表示每个 DML 操作都是一个隐式独立的事务(DDL 操作会自动提交数据，不受 autocommit影响)

关键字 autocommit

查看自动提交是否开启

show variables like 'autocommit'; 默认为ON

关闭自动提交

方式一：

SET autocommit = FALSE;

方式二：显式开启事务(则自动提交会暂时失效)

START TRANSACTION;

update account set balance = balance - 10 where id = 1;

update account set balance = balance + 10 where id = 2;

COMMIT; 或 rollback

2.3 隐式提交数据的情况

  • 数据定义语言（DDL）
    • 创建修改数据库、表、视图、存储过程等结构
  • 隐式使用或修改 mysql 数据库中的表
    • 使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句会隐式提交
  • 事务控制或关于锁定的语句
    • 当一个事务还没提交或回滚时，又开启一个事务则上一个事务会隐式自动提交
BEGIN:

SELECT ... 事务中的一条语句
UPDATE ... 
....

BEGIN: 会隐式提交上面语句所属的事务

当前的 autocommit 系统变量值为 OFF，手动调成 ON时，也会隐式提交前边语句所属的事务

使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式提交 前边语句所属的事务

  • 加载数据的语句

    • 使用 LOAD DATA 语句批量导入数据时，也会隐式提交前边语句所属的事务

  • MySQL 复制的一些语句

    • 使用 START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO 等语句也会 隐式提交

  • 其它语句

    • 使用 ANALYZE TABLE 、CACHE INDEX、CHECK TABLE、FLUSH 等也会隐式提交

2.4 举例1：提交与回滚

情况1：

USE atguigudb2;

CREATE TABLE user3(name VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUE('张三'); #不会自动提交数据
COMMIT;

BEGIN;
INSERT INTO user3 VALUE('李四'); 
INSERT INTO user3 VALUE('李四'); 
ROLLBACK;

此时表中只有张三一条数据

情况2：

TRUNCATE TABLE user3; DDL操作会自动提交数据，不受 autocommit 变量的影响

BEGIN;
INSERT INTO user3 VALUE('张三');
COMMIT;

SELECT * FROM user3;

INSERT INTO user3 VALUE('李四'); 
INSERT INTO user3 VALUE('李四'); 

ROLLBACK;

SELECT * FROM user3;

此时表中有张三和李四两条记录

情况3：

TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUE('张三');
COMMIT;

SELECT * FROM user3;

INSERT INTO user3 VALUE('李四'); 
INSERT INTO user3 VALUE('李四'); 

ROLLBACK;

此时表中只有一条张三的记录

因为在事务开启之前设置了 SET @@completion_type = 1;

  • completion = 0，此时为默认情况。执行 COMMIT 时会提交事务，执行下个事务时，还是需要使用 START TRANSACTION 或者 BEGIN 开启
  • completion = 1 ，这种情况下，当我们提交事务时，就相当于执行了COMMIT AND CHAIN ，也就是开启了一个链式事务。即当我们提交事务后会开启一个相同隔离级别的事务
  • completion = 2，这种情况下，COMMIT = COMMIT AND RELEASE 。也就是当我们提交后，会自动与服务器断开连接

2.5 举例2：测试 MyISAM

在 MyISAM 中不支持事务

BEGIN;
INSERT INTO test2 VALUES (1);
ROLLBACK;

SELECT * FROM test2;
结果有数据

2.6 举例3：SAVEPOINT

CREATE TABLE user4(name varchar(15),balance DECIMAL(10,2));

BEGIN;
INSERT INTO user4(name,balance) VALUES('张三',1000);
COMMIT;

SELECT * FROM user4;

BEGIN;
UPDATE user4 SET balance = balance - 100 WHERE name = '张三';

UPDATE user4 SET balance = balance - 100 WHERE name = '张三';

SAVEPOINT s1;

UPDATE user4 SET balance = balance + 1 WHERE name = '张三';
此时 balance = 801

ROLLBACK TO s1;

SELECT * FROM user4;
此时 balance = 800

ROLLBACK; 由于回滚到保存点事务并没有结束，所以此时回滚整个事务仍然有效

SELECT * FROM user4;
此时 balance = 1000

3. 事务隔离级别

服务器可能会同时处理多个事务。事务具有隔离性，从理论上来说 某个事务对某个数据进行访问时，其他事务应该排序，等该事务提交之后，其他事务才可以访问该数据，但是对性能影响过大，所以出现了隔离级别

3.2 数据并发问题

同时执行需要访问相同数据的事务

1. 脏写（Dirty Write）
两个事务，若事务 Session A 修改了 另一个 未提交事务Session B 修改过的数据，则就意味着发生了脏写

Session B 事务先将 studentno 值为 1 的记录的 name 值改为 李四，Session A 事务又将该记录列值改为 张三。Session A 提交了事务，Session B 回滚了事务，则Session A 的修改就没有生效

2、脏读（Dirty Read）
两个事务，Session A 读取了已经被 Session B 更新但是还没有提交的事务的字段。若之后 Session B 回滚，则Session A 读取的内容就是临时且无效的

3. 不可重复读（Non-Repeatable Read）
Session A 读取了一个字段，后 Session B 更新了该字段。之后 Session A 再次读取同一字段。此时值就不同了。这就是发生了不可重复读


注意：Session B 中的操作都是 隐式事务（也就是会自动提交)

4. 幻读（Phantom）
Session A 中从表读取了一个字段，后在 Session B 对该表插入了新的数据。Session A 再次读取 同一张表，则数据就会多几行

注意：Session B 中的操作都是 隐式事务（也就是会自动提交）。新插入的数据被称为 幻影记录

3.3 SQL 中的四种隔离级别

问题的严重性排序

脏写 > 脏读 > 不可重复读 > 幻读

SQL标准设立了4个 隔离级别

  • READ UNCOMMITTED：读未提交。在该隔离级别下，所有事务都可以读取到其他还没有提交事务的执行结果。无法避免脏读、不可重复读、幻读。
  • READ COMMITTED：读已提交。事务只能看到已经提交事务所修改后的结果。可以避免脏读，但是无法避免 不可重复读、幻读
  • REPEATABLE READ：可重复读。事务A读取到一条数据后，事务 B 对该数据进行了修改并提交，则事务A 再读该条数据，还是原来的内容。可以避免脏读、不可重复读。幻读的问题还存在。是 MySQL 的默认隔离级别
  • SEPIALIZABLE：可串行化。可以保证事务读取相同的数据，但是在这个事务持续期间，其他事务无法对该表执行插入、更新和删除操作。


YES：表示是否会发生这种情况

3.4 MySQL 支持的四种隔离级别

查看隔离级别

MySQL 5.7.20 之前
SHOW VARIABLES LIKE 'tx_isolation';

之后
SHOW VARIABLES LIKE 'transaction_isolation';

或者
所有版本都可以使用
SELECT @@transaction_isolation;

3.5 如何设置事务的隔离级别


SET (GLOBAL / SESSION) TRANSACTION ISOLATION LEVEL 隔离级别;
隔离级别格式：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

或者

SET (GLOBAL / SESSION) TRANSACTION_ISOLATION = '隔离级别';
隔离级别格式：
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

关于设置时使用 GLOBAL 或者 SESSION 的影响

  • 使用 GLOBAL 关键字（在全局范围影响）：
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
或 
SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';

则：

  • 对当前已经存在的会话无效
  • 对执行该语句后，新产生的会话生效

使用 SESSION 关键字（在会话范围影响）：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
或 
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

则：

  • 对当前会话的后续所有事务有效
  • 若在事务之间执行，则对后续事务有效
  • 该语句可以在已开启的事务中间执行，但不会影响当前正在执行的事务

以上操作(无论是 GLOBAL还是 SESSION)重启服务器后都会恢复默认值，若想要改变默认值则需要在 my.ini 配置文件中 添加 transaction_isolation = 隔离级别，

3.6 不同隔离级别举例

演示1：读未提交之脏读

设置隔离级别为未提交读

执行流程如下：

演示2：读已提交

演示3：可重复读

演示4：幻读

隔离级别：REPEATABLE READ 下，可以避免幻读，通过对 select 操作手动添加 行X锁(独占锁) 。同时若当查询的记录不存在，则当前事务也会获得一把记录锁（InnoDB 的行锁锁定的是索引，与记录实体是否存在没有关系，存在就加 行X锁，不存在就加间隙锁），其他事务则无法插入此索引的记录，所以避免了幻读

在 SERIALIZABLE 隔离级别下，step 1 执行时会隐式添加 行(X)锁 / gap(X)锁

4. 事务的常见分类

  • 扁平事务（Flat Transactions）
  • 带有保存点的扁平事务（Flat Transactions with Savepoints）
  • 链事务（Chained Transactions）
  • 嵌套事务（Nested Transactions）
  • 分布式事务（Distributed Transactions）

1）扁平事务是最常见、频繁使用的事务

2）带有保存点的扁平事务 允许事务执行过程中回滚到同一个事务中较早的操作。

保存点(Savepoint) 用来保存事务当前状态，方便发生错误时，事务可以回滚到保存点的状态

3）链事务：指一个事务由多个子事务链式组成

4）嵌套事务 是一个层次结构的事务，一个顶层事务嵌套控制着各个层次的事务。

5）分布式事务：在分布式环境下运行的扁平事务

文章作者： 临川
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 临川羡鱼！
MySQL MySQL
喜欢就支持一下吧