事务、备份与恢复、并发控制

【实验目的】理解事务 ACID 特性， 掌握数据库备份与恢复方法，理解数据库中锁和并发

控制机制。

【实验要求】掌握 MySQL 的数据库和日志备份与恢复，创建和执行事务,了解 MySQL 各种

锁类型，理解事务的隔离级别与并发控制，掌握事务应用编程。

【实验内容】

一、事务应用编程

1．构建学生课程学分登记表 StuCredit，属性如下：

Sno char(4)  学号

Totalcredit tinyint  总学分

PassCredit tinyint   已通过的所学课程学分

NoPassCredit tinyint -- 未通过的课程总学分

\

使用 SQL 命令统计当前 sc 表中学生获得的学分和未通过课程的学分，填入到 stuCredit 中。

SQL语句如下：

先将所有的学号写进该表，再通过两个sql语句计算总学问和已获得的总学分，最后未通过学分为总学分减去以获得学分：

insert into stucredit(sno)

select sno from stu;

    

update stucredit,(

select sno,ifnull(sum(credit),0) sum

from(

    select stu.sno sno,

    case when sc.grade>=60 then credit else null end credit

    from stu left outer join sc on stu.sno=sc.sno

             left outer join cou on cou.cno=sc.cno

    group by stu.sno,credit,grade

) a          

    group by sno) b

    set stucredit.passcredit =b.sum

    where stucredit.sno=b.sno;

    

    update stucredit,(

    select stu.sno sno,ifnull(sum(cou.credit),0) sum

    from stu

    left outer join sc on sc.sno=stu.sno

    left outer join cou on cou.cno=sc.cno

    group by stu.sno) a

    set stucredit.totalcredit=a.sum

    where stucredit.sno=a.sno;

    

    update stucredit

    set Nopasscredit=totalcredit-passcredit;

2．创建一个存储过程 SumCredit，添加学生课程成绩，统计学分。

输入参数：学号 sno、课程号 cno、成绩 grade

功能：

在 sc 表中插入一条学生课程成绩记录，如果成绩>=60 分，在 StuCredit 表中登记该学

生新增的学分，包括增加已学课程的总学分和已通过的学分。否则，增加已学课程的总

学分和未通过的课程学分。

要求：

1）存储过程中要求使用事务编程：定义事务，要求在 SC 表插入成绩记录和修改

StuCredit 表的学分记录要一起完成，不能只完成部分的数据更新。

 代码如下：

drop procedure if exists sumcredit;

delimiter $$

create  procedure sumcredit(stusno  char(4),coucno char(4),cougrade decimal(4.1))

begin

    start transaction;-- 开始一个事务

    insert into sc values(stusno,coucno,cougrade);

    update stucredit set stucredit.passcredit=

    case

    when (cougrade>=60) then stucredit.passcredit+(select credit from cou where cou.cno=coucno)

    else stucredit.passcredit

    end

    where sno=stusno;

update stucredit set stucredit.nopasscredit=

    case

    when (cougrade<60) then stucredit.nopasscredit+(select credit from cou where cou.cno=coucno)

    else stucredit.nopasscredit

    end

    where sno=stusno;

    update stucredit set stucredit.totalcredit=stucredit.totalcredit+(select credit from cou where cou.cno=coucno)

    where sno=stusno;

    commit;  -- 提交事务

    end $$

    delimiter ;

    

执行两次存储过程（对应及格与不及格两种情况）

    call sumcredit('S009','C003','86');

    call sumcredit('S009','C002','56');

2、执行存储过程 SumCredit 插入一条成绩记录，测试执行结果。

执行之后在sc表中插入了两条记录，且改写的stucredit表，由于插入的成绩一条及格，一条没有及格，所以S009对应的三个属性均发生改变。

3）设 SC 表已经设置主键为（

sno,cno），当执行存储过程 SumCredit，插入一条重复的

成绩记录时，观察并分析执行结果

插入重复成绩时，显示插入失败，而stucredit表也没有改变

二、MySQL 备份及恢复

1.在本地数据库，利用 mysqldump 备份数据库及表，完成以下备份要求：

提示：以管理员身份运行 DOS 命令提示符窗口，执行 mysqldump 命令。

1）备份xscj数据库。

2)备份xscj数据库的stu表。

3）备份xscj数据库的所有表结构。备份命令示例：

删除stu表（截图中stu表又出现是因为在命令提示符窗口利用备份对stu表复原）

可以看出stu表中所有的信息都得到了恢复

3.使用日志文件备份和恢复

1）查看、处理日志文件

Windows 命令行窗口下，在 MySQL 安装目录\bin 下，使用 mysqlbinlog 实用工具查看和处理

二进制日志文件；

在 MySQL 查询窗口查看 binlog 日志文件。

查看 binlog 是否开启：show variables like 'log_%';

查看 binlog 文件：

show binary logs -- 查看 binlog 日志文件信息

【示例】使用 mysqlbinlog 实用工具查看日志文件

需注意：以管理员身份打开 DOS 命令行窗口，最好进入到日志文件所在的文件夹里执行

mysqlbinlog 命令。

实验总结：

注意：每取一个时刻，都要启用一个新的日志，不然可能会出现时刻2和时刻3恢复的状态一样的情况

    本次实验内容较简单，遇到的较大的问题是在模拟系统崩溃时，在时间点T3时忘记了flush logs，使第三次的回复结果和第二次相同。