Transaction processing

Mac安装使用Mysql教程(从零开始)

• 第一章 Mac安装MySQL
  • 1.1 过程记录
  • 1.2 参考
• 第二章 安装数据库管理软件DBeaver
  • 2.1 过程记录
  • 2.2 参考
• 第三章 DBeaver创建MySQL数据库
  • 3.1 过程记录
  • 3.2 报错及解决
  • 3.3 参考
• 第四章 终端管理MySQL
  • 4.1 开启MySQL服务
  • 4.2 登录MySQL（复杂）
  • 4.3 登录MySQL（命令简化）
  • 4.4 登录MySQL（全局设置）
  • 4.5 操作数据库
  • 4.6 参考
• 第五章 MySQL基本操作之查询
  • 5.1 检索数据排序
  • 5.2 数据过滤
  • 5.3 汇总数据
  • 5.4 数据分组
  • 5.5 使用子查询
  • 5.6 联结表
  • 5.7 高级联结
  • 5.8 组合查询
  • 5.9 操作多个表
  • 5.10 参考
• 第六章 MySQL基本操作之插入
  • 6.1 插入整行数据
  • 6.2 插入行的一部分
  • 6.3 插入查询结果
  • 6.4 从一个表复制到另一个表
  • 6.5 参考
• 第七章 MySQL基本操作之更新与删除
  • 7.1 更新数据
  • 7.2 删除数据
  • 7.3 更新和删除的指导原则
  • 7.4 参考
• 第八章 创建和操纵表
  • 8.1 创建表
  • 8.2 更新表
  • 8.3 删除表
  • 8.4 重命名表
  • 8.5 参考
• 第九章 使用视图
  • 9.1 视图简介
  • 9.2 创建及使用视图
  • 9.3 参考
• 第十章 使用存储过程
  • 10.1 存储过程简介
  • 10.2 存储过程优缺点
  • 10.3 执行存储过程
  • 10.4 创建存储过程
  • 10.5 参考
• 第十一章 管理事务处理
  • 11.1 事务处理简介
  • 11.2 控制事务处理
  • 11.3 撤销和提交
  • 11.4 使用保留点
• 第十二章 游标
  • 12.1 游标简介
  • 12.2 游标使用
    • 实例一
    • 实例二
    • 实例三
  • 12.3 参考
• 第十三章 MySQL学习问题及解决记录
  • 13.1 DBeaver无法创建存储过程
  • 13.2 总结中…

第十一章 管理事务处理

11.1 事务处理简介

       事务处理是确保数据库完整性的机制，它用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行(除非明确指示)。参考下面的例子可以更好地理解事务处理的作用。

情景：向数据库中添加一个客户订单
检索数据库库是否存在相应客户，不存在则添加用户 —> 检索顾客ID —>
将Order表中新添加的行与顾客ID关联 —> 为订购的每一个物品在Order_Item中
新加一行，通过检索出来的ID将将它与Orders表关联（并且通过产品ID与Products表关联）

假设数据库出现如下故障：
1.在向Orders表添加数据之前（没有影响）；
2.在向Orders表添加数据之后，Order_Item表添加数据之前（导致两个表数据不一样）；
3.在向Order_Item表添加数据之时（插入的数据不完整）；
解决办法：
将向Orders表和Order_Item表添加数据的操作都包含于事务中，如果出现故障，撤销一切操作。

       事务处理中的重要术语如下：

1. 事务(transaction)指一组 SQL 语句;
2. 回退(rollback)指撤销指定 SQL 语句的过程;
3. 提交(commit)指将未存储的 SQL 语句结果写入数据库表;
4. 保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，可以对它发布回退(与回退整个事务处理不同)。

       事务处理可以管理INSERT,UPDATE和DELETE语句，但不能回退SELECT语句和DROP,CREATE操作。

11.2 控制事务处理

       不同DBMS用于实现事务处理的语法有所不同，使用事务处理时要参考其对应的参考文档。管理事务的关键是将SQL语句组分解为逻辑块，并明确规定数据何时应该回退。

SQL Server

BEGIN TRANSACTION
…
COMMIT TRANSACTION

MySQL & MariaDB –

START TRANCATION/BEGIN
…

Oracle –

SET TRANCTION
…

PostgreSQL –

BEGIN
…

       大多数DBMS中没有明确标识事务在何处结束。事务是一直存在的，直到被中断。通常COMMIT表示保存更改，ROLLBACK表示撤销。

11.3 撤销和提交

       命令ROLLBACK用于回退（撤销）SQL 语句。
       一般的SQL语句都是直接对数据库表操作编写的，这被称为隐式提交，即提交（写或保存）操作是自动进行的。但对于事务处理块，不同DBMS有不同处理方法，有的不会隐式提交，有的则会。进行明确的提交时要使用COMMIT语句。
       以下为进行提交的例子，如果要回滚只需把COMMIT换为ROLLBACK即可。(MySQL的例子亲测可用，其它例子没试过)

SQL Server

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 20005
DELETE Orders WHERE order_num = 20005
COMMIT TRANSACTION

Oracle

SET TRANSACTION;
DELETE OrderItems WHERE order_num = 20005;
DELETE Orders WHERE order_num = 20005;
COMMIT;

MySQL

START TRANSACTION;
DELETE FROM OrderItems WHERE order_num=20005;
DELETE FROM Orders WHERE order_num=20005;
COMMIT;

注意：MySQL中的命令是输入一句并执行一句的，而非把上面代码全部输入完成后执行。

11.4 使用保留点

       由之前例子可知，使用ROLLBACK和COMMIT可以实现整个事务的撤销与写入。但面对复杂事务时，有时需要部分提交或撤回。需要在事务处理块中合适的位置添加占位符来实现部分提交或撤回的功能。在SQL中，占位符被称为保留点。占位符的具体使用如下：

Oracle/MySQL/MariaDB

SAVEPOINT delete1;
…
ROLLBACK TO delete1;

SQL Server

SAVE TRANSACTION delete1;
…
ROLLBACK TRANSACTION delete1;

注意：保留点需要有一个唯一标识它的名字。

       如下是一个完整的SQL Server控制事务处理的例子：

START TRANSACTION;
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000011', 'Toys Emporium');
SAVEPOINT StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000011');
IF @@ERROR <>0 ROLLBACK TO StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT;