《数据库原理》实验报告DB7——事务与并发控制

一. 实验内容、步骤以及结果

假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号：

icbc_card(studcardid, icbcid, balance) //校园卡ID，工行卡ID，银行卡余额
campus_card(studcardid, balance)    //校园卡ID，校园卡余额

本实验中的部分示例数据

create table icbc_card(
icbcid int,
sno varchar(8),
balance int
);
create table campus_card(
sno varchar(8),
balance int
);
insert into campus_card values ('20200032', 1);
insert into campus_card values ('20200033', 100);
insert into icbc_card values (1, '20200032', 300);
 insert into icbc_card values (2, '20200033', 400);  

针对以上数据库按照要求完成下列实验：

1. 编写一个事务处理实现如下的操作：某学号为20200032的学生从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。（10分）

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `zhuanzhang`()
BEGIN
	declare bank_card_balance int default 0;
	select balance from icbc_card where sno = '20200032' into @bank_card_balance;
	if @bank_card_balance>200 then
		update icbc_card set balance = balance + 400 where sno = '20200032';
		update campus_card set balance = balance + 200 where sno = '20200032';
		commit;
	else
		rollback;
	end if;	
END$$
   
Call zhuanzhang;

可以看到学号为20200032的学生银行卡账户少了200，且对应的校园卡账户增加了200.

2. 针对本题的数据库和表，分别用具体的例子展现几种数据不一致问题：如丢失修改、读脏数据、不可重复读和幻读（删除和插入），注意如有无法展现的情况，请说明原因。（20分，每种数据不一致10分）

(1) 丢失修改

此时隔离级别为repeatable-read

(2) 读脏数据

此时隔离级别为read-uncommitted

(3) 不可重复读

此时隔离级别为read-committed

(4) 幻读（删除和插入）

此时隔离级别为read-committed

3. 利用数据库的隔离级别或者锁机制等，设计方案分别解决上述丢失修改、读脏数据、不可重复读、幻读的数据不一致问题。（20分，每种数据不一致5分）

(1) 丢失修改

set transaction isolation level serializable;
show variables like 'transaction_isolation';

此时隔离级别为repeatable-read

(2) 读脏数据

set transaction isolation level read-commited;
show variables like 'transaction_isolation';

此时隔离级别为read-committed

(3) 不可重复读

此时隔离级别为repeatable-read

(4) 幻读（删除和插入）

此时隔离级别为repeatable-read

4. 构造两个事务同时更新一条数据，尝试使用以下SQL命令查看和理解当前系统中事务以及锁的状态等信息。（10分）

show engine innodb status (MySQL 8.0 or 5.7)
     
select * from information_schema.innodb_trx  (MySQL 8.0 or 5.7)
 
select * from performance_schema.data_locks; (MySQL 8.0)
 
select * from sys.innodb_lock_waits; (MySQL 8.0)
 
select * from information_schema.innodb_lock_waits (MySQL 5.7)

select * from information_schema.innodb_locks (MySQL 5.7)

5. 构造一个出现死锁的情形。（10分）

6. 构造含有savepoint的事物，并在某时刻回滚到某个savepoint。（10分）

start transaction;
insert into icbc_card values(1,'20200034',200);
insert into icbc_card values(2,'20200035',500);
savepoint s1;
insert into icbc_card values(3,'20200036',200);
insert into icbc_card values(4,'20200037',500);
savepoint s2;
insert into icbc_card values(5,'20200038',200);
insert into icbc_card values(6,'20200039',500);
select * from icbc_card;

完成所有的插入icbc_card中的数据：

rollback to savepoint s2;
select * from icbc_card; 

回滚到s2后icbc_card中的数据：

rollback to savepoint s1;
select * from icbc_card;
 

回滚到s1后icbc_card中的数据：

rollback;
select * from icbc_card; 
 

回滚所有的后icbc_card中的数据：

7. 通过实验查看mysql中的各种日志：查询日志、错误日志、慢查询日志。（10分）

1) 查询日志：

默认关闭，需要自行打开

Set global general_log = on;
Show variables like ‘%general_log%’

2) 错误日志：

错误日志的位置在C:\ProgramData\MySQL\MySQL Server 8.0\Data，默认以 $HOSTNAME.err 为文件名称。

3) 慢查询日志：

错误日志的位置在C:\ProgramData\MySQL\MySQL Server 8.0\Data，默认以 $HOSTNAME-slow.log 为文件名称。

8. 用mysqlbinlog查看数据库的事务日志，并尝试按照以下场景进行数据恢复。（10分）

• 操作步骤：

• 1）建立db1，建立t1、t2表。t1、t2表结构：create table t1(id int);

• 2）向t1插入数据：11,12, 13 3）向t2插入数据：21, 22, 23

• 3）drop table t1;

• 4）t2还可以正常使用，给t2插入数据24

在以上第4步的操作之后，利用mysqlbinlog恢复t1的数据。

以上4个步骤的SQL如下：

	create database db1;
	create table t1(id int);
	create table t2(id int);
	insert into t1 values(11);
	insert into t1 values(12);
	insert into t1 values(13);
	insert into t2 values(21);
	insert into t2 values(22);
	insert into t2 values(23);
	drop table t1;
	insert into t2 values(24);

1. 首先创建新的binlog日志文件

flush logs;
show master status;

2. 然后执行上方的代码

3. 然后查看binlog文件

show binlog events in 'DESKTOP-LMI9M9U-bin.000012';

可以看到start position = 156，stop position = 1278

4. 使用命令行工具将binlog文件中的sql语句导出（需要使用管理员权限）

mysqlbinlog --start-position=156 --stop-position=1278 DESKTOP-LMI9M9U-bin.000012>C:\test000022.sql  

5. 导入sql文件
   经查询，已恢复

二. 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)

在备份数据库时出现了命令行中无法实施mysqlbinlog工具，最后将环境变量进行添加，并且使用管理员权限打开cmd后成功运行，得以完成实验。