数据库事务实验
- 一.实验目的
- 二.实验要求
- 三.实验内容和结果
- (1)显式事务的编写
- (2)显式的事务编写(带回滚)
- 四.实验总结及体会
一.实验目的
掌握数据库事务管理的基本原理以及事务的编程方法。
二.实验要求
针对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;