SQL 管理事务处理

最新推荐文章于 2024-08-04 02:23:47 发布

Fe_cow丿

最新推荐文章于 2024-08-04 02:23:47 发布

阅读量1.3k

点赞数 3

分类专栏： SQL

本文链接：https://blog.csdn.net/Fe_cow/article/details/87776351

版权

SQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

SQL 管理事务处理

一、事务处理：

使用事务处理(transaction processing)，通过确保成批的SQL操作，要么完全执行，要么完全不执行，来维护数据库的完整性。
事务处理是一种机制，用来管理必须批量执行的SQL操作，保证数据库不包含不完整的操作结果。
利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。
使用事务时，出现的术语：
- 事务(transaction) 指一组SQL语句；
- 回退(rollback)指撤销指定SQL语句的过程；
- 提交(commit)指将未存储的SQL语句结果写入数据库表；
- 保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，可以对它发布回退（与回退整个事务处理不同）；
提示：可以回退哪些语句
- 事务处理用来管理，INSERT、UPDATE、DELETE语句。
- 不会回退SELECT语句(回退SELECT语句没有必要，因为是检索数据)，也不能回退CREATE、DROP操作。

二、简单介绍Mysql事务：

MySQL 事务主要用于处理操作量大，复杂度高的数据。
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提（Readuncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
事务控制语句：
- BEGIN或START TRANSACTION：显式地开启一个事务；
- COMMIT：也可以使用COMMIT WORK，不过二者是等价的。
  - COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK：有可以使用ROLLBACK WORK，不过二者是等价的。
  - 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier[保存点名]允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT ；
- RELEASE SAVEPOINT identifier[保存点名]：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier[保存点名]；把事务回滚到标记点；

下面进行事务的栗子：

事务的提交：

mysql> CREATE TABLE transaction_table(id int(5)) engine=innodb;  # 创建表 并且引擎是innodb
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;  # 查看当前表
+-------------------+
| Tables_in_fe_cow  |
+-------------------+
| transaction_table |   # 新创建的表
+-------------------+
9 rows in set (0.00 sec)

mysql> BEGIN;   #  开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Transaction_table VALUE(5);   # 往表中插入数据
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Transaction_table VALUE(6);   # 往表中插入数据
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;   # 进行事务的提交
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM Transaction_table;   #  查询该表的信息
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)

事务的回滚：

mysql> BEGIN;  #  开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO transaction_table VALUES(7);   #  插入数据
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Transaction_table;  #  查看表，可以看到 7已经插入到表中
+------+
| id   |
+------+
|    5 |
|    6 |
|    7 |
+------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;  #  进行回滚
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM Transaction_table;  # 因为回滚 所以数据没有真正插入
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)

使用保留点：

mysql> BEGIN;    #  开启事务
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT * FROM Transaction_table;   # 查看表当前的数据
Empty set (0.00 sec)

mysql> INSERT INTO Transaction_table VALUES(1);   # 添加第一条数据
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Transaction_table VALUES(2);   # 添加第二条数据
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT start_01;   #  创建第一个占位符(保存点名为start_01)
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Transaction_table VALUES(3);  # 添加第三条数据
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Transaction_table VALUES(4);  # 添加第四条数据
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT start_02;   # 创建第二个占位符(保存点名为start_02)
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Transaction_table VALUES(5);   # 添加第五条数据
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Transaction_table VALUES(6);    #  添加第六条数据
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Transaction_table;  # 查看当前表中的数据
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

mysql> ROLLBACK TO start_02;   #  回到第二个保留点start_02
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Transaction_table;  # 可以看到数据已经回到设置第二个保存点时候的数据
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> ROLLBACK TO start_01;  # 回到第一个保留点start_01的为止
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Transaction_table;  # 表中的数据
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

# 如果想从先设置的保留点，再回到之后设置的保留点，是不可以的。
mysql> ROLLBACK TO start_02;  # 当前已经回到到start_01保留点，想回到start_02的错误
ERROR 1305 (42000): SAVEPOINT start_02 does not exist

mysql> ROLLBACK;   # 进行回滚
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Transaction_table;  # 回滚到了，BEGIN前的数据
Empty set (0.00 sec)

mysql> COMMIT;  # 提交
Query OK, 0 rows affected (0.00 sec)