实验5 并发控制

一、实验项目：

并发控制。

二、实验目的

1、会使用SQL语句进行事务的开启、提交和回滚。

2、会使用SQL语句进行锁定和解锁。

3、会使用SQL语句进行事务隔离等级的修改。

三、实验内容

使用SQL语句完成下列题目：

1、创建存储过程P_usr_del，在存储过程中使用事务，实现同时删除给定客户（userid）在account表和orders表中的数据。要求userid作为该存储过程的输入参数，调用存储过程时输入参数为u0002。

2、创建存储过程P_ord_upd，在存储过程中使用事务，实现当向lineitem表中插入一行数据时，根据订单号对orders表的订单总价进行修改，订单总价加上该商品明细的金额。要求该存储过程有4个输入参数，分别为lineitem表新增记录的列值，调用存储过程时各参数的值分别为20230414,0040003,3,30。

3、创建存储过程P_pro_upd，在存储过程中使用事务，实现当修改给定商品（productid）的市价（listprice）时，同时修改lineitem表中对应商品的成交价格unitprice。要求productid和listprice作为该存储过程的输入参数，调用存储过程时两个参数的值分别为0010001,52。

4、为product表加上读锁,然后执行select * from product;语句，再执行delete from product;语句，最后再解锁。

5、将当前事务的隔离级别设置为读取提交的数据（READ COMMITED）。

四、实验参考代码

1、delimiter $$

create procedure P_usr_del(in user_id char(6))

begin

declare exit handler for sqlexception rollback;

start transaction;

delete from account where userid=user_id;

delete from orders where userid=user_id;

commit;

end $$

delimiter ;

call P_usr_del('u0002');

调用存储过程前：select * from account where userid='u0002';

select * from orders where userid='u0002';

调用存储过程后：select * from account where userid='u0002';

select * from orders where userid='u0002';

2、delimiter $$

create procedure P_ord_upd(in o_orderid int(11),in o_itemid char(10),in o_quantity int(11),in o_unitprice decimal(10,2))

begin

declare a int;

declare exit handler for sqlexception rollback;

start transaction;

select count(*) into a from orders where orderid=o_orderid;

insert into lineitem values(o_orderid,o_itemid,o_quantity,o_unitprice);

update orders set totalprice=totalprice+o_quantity*o_unitprice where orderid=o_orderid;

if a>0 then

commit;

        else

            rollback;

end if;

end $$

delimiter ;

call P_ord_upd(20230414,'0040003',3,30);

调用存储过程前：select * from orders where orderid=20230414;

select * from lineitem where orderid=20230414;

调用存储过程后：select * from orders where orderid=20230414;

select * from lineitem where orderid=20230414;

3、delimiter $$

create procedure P_pro_upd(in o_productid char(10),in o_listprice decimal(10,2))

begin

declare exit handler for sqlexception rollback;

start transaction;

update product set listprice=o_listprice where productid=o_productid;

update lineitem set unitprice=o_listprice where productid =o_productid;

commit;

end $$

delimiter ;

call P_pro_upd('0010001',52);

调用存储过程前：select * from product where productid='0010001';

select * from lineitem where productid='0010001';

调用存储过程后：select * from product where productid='0010001';

select * from lineitem where productid='0010001';

4、lock tables product read;

   select * from product;

   delete from product;

   unlock tables;

5、set session transaction isolation level read committed;

SELECT @@transaction_isolation;