数据库系统原理实验——事务实验

一.实验目的

  掌握数据库事务管理的基本原理以及事务的编程方法。

二.实验要求

  针对TPC-H数据库，设计几个典型的事务应用，包括显式事务、事务提交、事务回滚等。

• （1）显式事务的编写
  • ①创建一个事务，当用户购买零件时，插入订单明细和订单记录，修改供应基本表以保持数据一致性。
  • ②创建一个事务，当用户撤销某个用户购买记录时，删除订单明细（假设只有一项订单明细）和订单记录，然后修改供应基本表以保持数据一致性。
• （2）显式事务的编写（带有回滚）
    创建一个事务，当用户购买零件时，插入订单明细（假设只购买一项明细）和订单记录，然后修改供应基本表，以保持数据一致性。

三.实验内容和结果

（1）显式事务的编写

①创建事务，购买零件时插入订单明细、订单记录，修改供应基本表。

#关闭事务自动提交
SET autocommit = 0;
#事务开始
START TRANSACTION;
#插入订单表内容
INSERT INTO order_form(`Order_number`, `Customer_number`, `Order_state`, `Order_date`, `Order_priority`, `Bookkeeper`, `Remark`) VALUES ('00003', '00001', '插入测试', '2020-10-27', 1, '小明', '小明的备注');
#插入订单详情内容
INSERT INTO order_form_detail
(`Order_number`, `Order_Detail_number`, `Component_number`, `Provider_number`, `Amount`, `Order_Detail_Price`, `Discount`, `Tax`, `Sales_Return`, `Ship_Date`, `Entrust_Date`, `Sign_Date`, `Ship_Remark`, `Ship_state`, `Remark`)
VALUES ('00003', '00005', '00001', '00001', 100, 10000, 0.5, 0.02, '0', '2020-10-27', '2020-10-27', '2020-10-27', '事务测试', '事务测试', '事务测试');
#更新订单表中的总价
UPDATE order_form,
(SELECT Order_number,SUM(Order_Detail_Price*(1 - Discount)*(1 + Tax)) AS sum_account
FROM order_form_detail
GROUP BY Order_number) AS temp
SET order_form.Order_account = sum_account 
WHERE order_form.Order_number = temp.Order_number;
#更新订单供应表中对应的零件数目
UPDATE component_supply
SET component_supply.Amount = Amount - 100;
COMMIT;

订单表与订单详细表出现了刚插入新的条目：


供应表的Amount减少了100：

②创建一个事务，当用户撤销某个用户购买记录时，删除订单明细（假设只有一项订单明细）和订单记录，然后修改供应基本表以保持数据一致性：

#关闭事务自动提交
SET autocommit = 0;
#事务开始
START TRANSACTION;
#订单取消，零件数目增加
UPDATE component_supply,order_form_detail
SET component_supply.Amount =
component_supply.Amount + order_form_detail.Amount
WHERE Order_Detail_number = '00005' AND component_supply.Component_number = order_form_detail.Component_number AND component_supply.Provider_number = order_form_detail.Provider_number;
#删除订单详情表内容
DELETE FROM order_form_detail WHERE `Order_Detail_number` = '00005';
#插入订单详情内容
DELETE FROM order_form WHERE `Order_number` = '00003';
COMMIT;

在①的基础上：
删除了订单表中编号00003的数据：

删除了订单详细表中00005的数据：

零件供应表中的对应零件数量返回了799：

（2）显式的事务编写（带回滚）

#关闭事务自动提交
SET autocommit = 0;
CREATE PROCEDURE ins_order(
	number char(20),
	detail_number char(20),
	comp_number char(20),
	pro_number char(20),
	num INTEGER,
	price INTEGER)
BEGIN
	DECLARE temp_num INTEGER;
	#事务开始
	START TRANSACTION;
	#插入订单表内容
	INSERT INTO order_form(`Order_number`, `Customer_number`, `Order_state`, `Order_date`, `Order_priority`, `Bookkeeper`, `Remark`) VALUES (number, '00001', '回滚事务测试', '2020-10-27', 1, '小明', '小明的备注');
	#插入订单详情内容
	INSERT INTO order_form_detail
	(`Order_number`, `Order_Detail_number`, `Component_number`, `Provider_number`, `Amount`, `Order_Detail_Price`, `Discount`, `Tax`, `Sales_Return`, `Ship_Date`, `Entrust_Date`, `Sign_Date`, `Ship_Remark`, `Ship_state`, `Remark`)
	VALUES (number, detail_number, comp_number, pro_number, num, price, 0.5, 0.02, '0', '2020-10-27', '2020-10-27', '2020-10-27', '回滚事务测试', '回滚事务测试', '回滚事务测试');
	#更新订单表中的总价
	UPDATE order_form,
	(SELECT Order_number,SUM(Order_Detail_Price*(1 - Discount)*(1 + Tax)) AS sum_account
	FROM order_form_detail
	GROUP BY Order_number) AS temp
	SET order_form.Order_account = sum_account 
	WHERE order_form.Order_number = temp.Order_number;
	
	#获得对应零件编号与供应商的零件数目
	SELECT Amount INTO temp_num
	FROM component_supply
	WHERE component_supply.Component_number = comp_number AND component_supply.Provider_number = pro_number;
	#如果当前零件数目大于等于本次插入的数目，代表数目足够,修改数目并提交,否则回滚
	IF(temp_num >= num) THEN
		UPDATE component_supply
		SET component_supply.Amount = Amount - num
		WHERE component_supply.Component_number = comp_number AND component_supply.Provider_number = pro_number;
		COMMIT;
	ELSE 
		ROLLBACK;
	END IF;
END;

CALL ins_order('00005','00005','00001','00001',1,1000);
#删除了存储过程
DROP PROCEDURE ins_order;

订单表中增加了对应的数据：

订单详情表中增加了对应的数据：

零件供应表中对应的零件数量修改了：

如果当前两件数目小于本次插入的数目，数目不足以支持订单，则取消插入，数据回滚。

CALL ins_order('00006','00006','00001','00001',100000,1000);

零件供应表中对应的零件数量未修改：

订单表中数据未被修改：

订单详情表中数据未被修改：

四.实验总结及体会

  本次实验有难度，如何使用正确的语句对数据进行处理是需要思考的难点。
  还好参考了一位大佬写的：
  数据的备份与恢复和事物处理
  学习到了很多，总之靠我自己想恐怕要想好久……

  对于事务的声明，需要语句START TRANSACTION;以此来声明事务的开始，直到COMMIT或ROLLBACK终止事务。
  在Mysql中，如果通过事务进行修改，需要关闭事务的自动提交：
  SET autocommit = 0;
  在实验（2）①中，需要使用存储过程来对表进行操作，在存储过程中可以使用事务。但是需要注意的是，通过DECLARE声明局部变量时，需要在START TRANSACTION与COMMIT/ROLLBACK语句外进行声明，如果在语句内声明会报错。
  在实验（2）①中使用到的IF语句，语句格式为：
  IF(条件) THEN
  ELSE IF(条件)
  ELSE
  END IF;