(1)首先关闭数据库系统事务自动提交功能(@@autocommit),再创建一个存储过程auto_delete,利用事务删除课程号为‘c05109’的表记录,然后回滚。
delimiter //
set @@autocommit =0;
create procedure auto_delete()
begin
start transaction;
delete from course where courseno='c05109';
select * from course where courseno='c05109';
rollback;
select * from course where courseno='c05109';
end //
delimiter ;
call auto_delete();
set @@autocommit=1;
说明:使用MySQL命令“set @@autocommit=0;”,可以显式地关闭MySQL自动提交。
(2)创建存储过程tran_update,启用事务将课程表course中课程号为c05109的课程名称改为‘MySQL数据库’,并提交事务。
delimiter //
create procedure tran_update()
begin
start transaction;
update course set cname='MySQL数据库'
where courseno='c05109';
commit;
select * from course where courseno='c05109';
end //
delimiter ;
call tran_update();
(3)创建一个存储过程tran_save,启用事务向course表中添加一条记录(‘c05179’,‘计算机科学’,‘必修’,48,12,7),并设置保存点。然后再删除该记录,并回滚到事务的保存点,提交事务。
delimiter //
create procedure tran_save()
begin
start transaction;
insert into course
values('c05179','计算机科学','必修',48,12,7);
savepoint spcno1;
delete from course
where courseno='c05179';
rollback work to savepoint spcno1;
select * from course where courseno='c05179';
commit ;
end //
delimiter ;
call tran_save();
(4)创建一个存储过程tran_student,启用事务实现在student表上进行查询、插入、更新和删除数据操作。
delimiter //
create procedure tran_student()
begin
start transaction;
select studentno,sname from student where studentno = '18122210010';
insert into student values('1912001098','梁伟豪','男','2000-08-11',786,'15912356445','lwh@qq.com');
update student set birthdate = '2000-01-01' where studentno = '1912000001';
delete from student where sname = '郑月';
commit;
end //
delimiter ;
call tran_student();
select * from student;
(5)为student表设置只读锁,为course表设置只写锁,并进行相应的读写测试
lock tables student read;
lock tables course write;
测试: 由于student设置了只读属性,所以无法打开。
(6)解除所有表格的锁限制
unlock tables;