MySQL（十八）MySQL事务(一)：事务的概述与使用

MySQL事务(一)：事务的概述与使用

1 数据库事务概述

​ 事务是数据库区别于文件系统的重要特性之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

1.1 存储引擎支持情况

​ show engines可以查看数据库支持的存储引擎以及对事务的支持，可以看到只有InnoDB支持事务：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
1.2 事务的基本概念

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

​ 事务处理的原则：保证所有的事务都作为一个工作单元来执行，当一个事务中执行多个操作的时候，要么所有的操作都被提交(commit)，这些修改也会被永久地保存下载；要么数据库放弃所有的修改，整个事务被回滚(roll back)到最初的状态。

1.3 事务的ACID特性

  • 原子性（atomicity）

    ​ 原子性是指所有的事务都作为一个工作单元来执行，当一个事务中执行多个操作的时候，要么所有的操作都被提交(commit)，这些修改也会被永久地保存下载；要么数据库放弃所有的修改，整个事务被回滚(roll back)到最初的状态。没有中间状态一说。

  • 一致性（consistency）

    ​ 一致性是指事务执行前后，数据从一个合法性状态转移到另一个合法性状态。这个状态是自定义的、满足业务的约束，满足这个预定的约束就是合法的状态，数据就是一致的；不满足则数据就是不一致的。

    image-20230503144040895

  • 隔离性（isolation）

    ​ 事务的隔离性是指一个事务的执行不能被其他事务所干扰，即一个事务内部的操作以及数据对并发的其他事务是隔离的，并发执行的事务之间互不干扰

  • 持久性（durability）

    ​ 持久性是指一个事务一旦被提交过，它对数据库的改变就是永久性的，接下来数据库的其他操作和故障不应该对它有任何的影响。

    ​ 持久性是通过事务日志来解决的，日志包括重做日志(redo log)和回滚日志(undo log)，当通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后才对数据库中对应的行做修改。这样做的好处是，即是数据库崩溃，数据库重启后找到没有更新到数据库系统中的重做日志，重新执行从而使事务更具有持久性。

总结：

  • ACID是数据库事务的四大特性，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是目标。
  • 数据库事务，其实就是数据库设计者为了方便起见，把满足原子性、一致性、隔离性、持久性的一个或者多个数据库操作统称为一个事务
1.4 事务的状态

​ mysql事务是一个抽象的概念，本质是满足ACID特性的一个或多个数据库操作。MySQL根据这些操作的执行阶段将事务分为几个状态：

  • 活动的（active）：事务对应的数据库操作正在执行过程中
  • 部分提交的（partially committed）：事务对应的数据库操作都执行完成，但是造成影响的数据都在内存中还没有刷回磁盘的状态，就说该事务处在部分提交的状态。
  • 失败的（failed）：当事务处在活动的 或者 部分提交的状态的时候，可能遇到了某些错误（数据库错误、操作系统错误、断电）或者直接人为地停止事务的执行而无法继续执行，就说事务处在失败的状态。
  • 中止的（aborted）：事务执行了一部分而变成了失败的状态，为了保持数据的一致性，需要把已经执行的数据库操作影响的数据还原到事务执行之前的状态，撤销事务执行失败对数据库的影响，就把这个撤销的过程成为回滚，当回滚操作执行完毕后，事务就处于中止状态。
  • 提交的（committed）：当一个处在部分提交的数据将所有的操作数据都刷回磁盘，就说该事务处于提交状态。
image-20230503152619939
1.5 事务的分类

  • 扁平事务：使用最简单也是最频繁的事务，所有的操作都属于同一层次，由begin work开始，rollback work或者commit work结束，中间操作是原子性的，要么都执行要么都回滚

  • 带有保存点的扁平事务：上面讲的SAVE POINT

  • 链事务：由一个或者多个子事务链式组成，可以看做是保存点模式的变种，带有保存点的扁平事务在系统发生崩溃的时候，所有的保存点都会消失，这意味着恢复的时候只能从事务开始出进行执行，而不能从最近的一个保存点执行。

    ​ 链式事务的思想就是：在提交一个事务的时候，释放不需要的数据对象，将必要的处理上下文隐式地传递给下一个要开始的事务，前一个事务的提交工作和下一个事务的开始操作合并成一个原子操作，这意味着下一个事务能够看到上一个事务的结果，就好像在一个事务中进行一样。这样就能在提交子事务的时候释放不需要的数据对象，而不必等到整个事务完成后才释放。

    image-20230503165436943

    ​ 链式事务和带有保存点的扁平事务的不同之处体现在：

    • 带有保存点的扁平事务能够回滚到任意正确的保存点，而链式事务只能回滚到附近的一个
    • 在对于锁的处理上，链式事务在commit的时候会自动释放当前锁，而带有保存点的扁平事务不影响所持有的锁
    • 嵌套事务
    • 分布式事务

2 事务的使用

​ 事务的使用有两种：显示事务和隐式事务

2.1 显示事务

  1. start transaction 或者 begin开启一个事务

    start transaction的不同之处在于后面可以添加一些修饰符，其中1、2是设置事务访问模式的，只能选择一种：

    1. READ ONLY：表名当前事务是一个只读事务，也就是属于改事务的操作执行读取数据而不能修改数据。

      补充：只读事务不能修改的数据也就是哪些其他事务也能访问到的数据，而对于临时表这种只能当前事务访问的数据，是也能够进行修改的

    2. READ WRITE（默认）：读写事务，既能读取也能修改数据

    3. WITH CONSISTENT SNAPSHOT：启用一致性读。

  2. 一系列事务中的操作，主要是DML不包含DDL

  3. 提交事务或者中止事务（或回滚事务）

回滚事务

​ 即撤销正在进行的所有没有提交的修改，并且可以回滚到某个保存点SAVE POINT。

ROLLBACK;

ROLLBACK TO SAVEPOINT;

​ 保存点SAVE POINT的操作有：

-- 在事务某处创建保存点
SAVEPOINT <保存点名称>;
-- 删除保存点
RELEASE <保存点名称>;
2.2 隐式事务
  • 关键字：autocommit，默认是开启的，效果是每个DML操作都是一个独立的事务
  • 针对DML操作有效而DDL无效
  • 在autocommit关闭的情况下，需要手动使用commit提交事务
  • 在autocommit开启的情况下，使用start transaction 或者 begin开启一个事务就不会自动提交
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
SET autocommit = false;
SET autocommit = true;
2.3 隐式提交事务的情况

​ 以下情形，即使在autocommit为false的情形下，也会隐式提交事务：

  • 数据定义语言（DDL）：数据库对象，指的就是数据库、表、视图、存储过程等结构，当使用CREATE、DROP、ALTER语句的时候，就会自动提交前面的事务
  • 隐式使用或修改数据库中的表：使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句也会提交前边语句所属的事务。
  • 事务控制或者关于锁定的语句：
    • 当前一个事务还没有提交或者回滚，就使用START TRANSACTION或者BEGIN开启新的事务的时候，就会自动提交上一个事务
    • 当前的auto commit为false，手动调成开启状态就会隐式自动提交前面语句所属的事务
    • 使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句的时候也会隐式自动提交前面语句所属的事务
  • 加载数据的语句：使用LOAD DATA来批量往数据库中导入数据的时候，会隐式自动提交前面语句所属的事务
  • 关于MYSQL复制的一些语句：使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句会隐式提交前面的语句
2.4 使用案例：提交与回滚
-- 情况一
SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); # 不会自动提交事务
SELECT * FROM user3; # 因为同处于一个事务所以能够查到，但是没有刷盘
COMMIT;

BEGIN;
INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); # [Err] 1062 - Duplicate entry '李四' for key 'user3.PRIMARY'
ROLLBACK;
SELECT * FROM user3; # 回滚到上次显示提交的状态，结果只有一个李四

-- 情况二
TRUNCATE TABLE user3; # DDL语句自动提交
SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); # 不会自动提交事务
SELECT * FROM user3; # 因为同处于一个事务所以能够查到，但是没有刷盘
COMMIT;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); #[Err] 1062 - Duplicate entry '李四' for key 'user3.PRIMARY'

ROLLBACK;

SELECT * FROM user3; # 回滚到上次提交成功的地方（隐式提交成功），结果为张三+李四

-- 情况三
TRUNCATE TABLE user3; # DDL语句自动提交
SELECT * FROM user3;
SELECT @@completion_type; # NO_CHAIN(0)
SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUES('张三'); # 不会自动提交事务
SELECT * FROM user3; # 因为同处于一个事务所以能够查到，但是没有刷盘
COMMIT;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); #[Err] 1062 - Duplicate entry '李四' for key 'user3.PRIMARY'

ROLLBACK;

SELECT * FROM user3; # 结果为张三

​ 情况三和情况二的区别只是completion_type变量的不同，当值为1的时候提交事务，相当于执行COMMIT AND CHAIN，即我们提交10行代码的事务的时候，会又自动开启一个链式事务，所以下面的两个插入操作是同处于一个事务的，因此在事务失败的时候，会rollback到10行的状态。

2.5 使用案例：InnoDB和MyISAM的区别

​ InnoDB支持事务而MyISAM不支持。

CREATE TABLE test1 (
	i INT
)ENGINE=INNODB;

CREATE TABLE test2 (
	i INT
)ENGINE=MYISAM;

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

SELECT * FROM test1; # 没有数据

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

SELECT * FROM test2; # 存在数据i=1
2.6 使用案例：SavePoint
INSERT INTO `user4` VALUES('张三', 1000);

BEGIN;
UPDATE `user4` SET balance = balance - 100;
UPDATE `user4` SET balance = balance - 100;

SAVEPOINT point1;

UPDATE `user4` SET balance = balance + 1;
ROLLBACK TO point1;

SELECT * FROM `user4`; # 800
posted @ 2023-05-04 18:05  Tod4  阅读(52)  评论(0)  编辑  收藏  举报