一、 实验目的和要求

(1) 巩固事务的概念
(2) 正确理解并发的概念及锁机制
(3) 熟悉DBMS的安全机制

二、实验环境(实验设备)

硬件：微机
软件：MySQL 5.6及以上版本

实验前言

这次实验是建立在第一次实验的数据库及表的基础上：【数据库系统设计】SQL语言实验

如果做过第一次实验的话，直接跳过往下看即可。

如果没有做过第一次实验，请用以下SQL快速完成建表与插入数据的操作。

---

创建 SPJ 数据库：

CREATE DATABASE SPJ210224;

USE SPJ210224; #进入刚创建的数据库

创建四个基表 S、P、J、SPJ：这里就直接把插入信息后的表的SQL语句放出来了。

创建S表的SQL：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `s210224`;
CREATE TABLE `s210224` (
  `SNO` char(20) NOT NULL,
  `SNAME` char(20) DEFAULT NULL,
  `STATUS` int(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `s210224` VALUES ('S1', '精益', '20', '天津');
INSERT INTO `s210224` VALUES ('S2', '盛锡', '10', '北京');
INSERT INTO `s210224` VALUES ('S3', '东方红', '30', '北京');
INSERT INTO `s210224` VALUES ('S4', '丰泰盛', '20', '天津');
INSERT INTO `s210224` VALUES ('S5', '为民', '30', '上海');

创建P表的SQL：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `p210224`;
CREATE TABLE `p210224` (
  `PNO` char(20) NOT NULL,
  `PNAME` char(20) DEFAULT NULL,
  `COLOR` char(20) DEFAULT NULL,
  `WEIGHT` int(20) DEFAULT NULL,
  PRIMARY KEY (`PNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `p210224` VALUES ('P1', '螺母', '红', '12');
INSERT INTO `p210224` VALUES ('P2', '螺母', '绿', '17');
INSERT INTO `p210224` VALUES ('P3', '螺丝刀', '蓝', '14');
INSERT INTO `p210224` VALUES ('P4', '螺丝刀', '红', '14');
INSERT INTO `p210224` VALUES ('P5', '凸轮', '蓝', '40');
INSERT INTO `p210224` VALUES ('P6', '齿轮', '红', '30');

创建J表的SQL：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `j210224`;
CREATE TABLE `j210224` (
  `JNO` char(20) NOT NULL,
  `JNAME` char(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `j210224` VALUES ('J1', '三建', '北京');
INSERT INTO `j210224` VALUES ('J2', '一汽', '长春');
INSERT INTO `j210224` VALUES ('J3', '弹簧厂', '天津');
INSERT INTO `j210224` VALUES ('J4', '造船厂', '天津');
INSERT INTO `j210224` VALUES ('J5', '机车厂', '唐山');
INSERT INTO `j210224` VALUES ('J6', '无线电厂', '常州');
INSERT INTO `j210224` VALUES ('J7', '半导体厂', '南京');

创建SPJ表的SQL:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `spj210224`;
CREATE TABLE `spj210224` (
  `SNO` char(20) NOT NULL,
  `PNO` char(20) NOT NULL,
  `JNO` char(20) NOT NULL,
  `QTY` int(20) NOT NULL,
  KEY `SNO` (`SNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj210224_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s210224` (`SNO`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `spj210224_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p210224` (`PNO`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `spj210224_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j210224` (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J3', '100');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J4', '700');
INSERT INTO `spj210224` VALUES ('S1', 'P2', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J1', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J4', '500');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S3', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S3', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S4', 'P5', 'J1', '100');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J3', '300');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J4', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P2', 'J4', '100');
INSERT INTO `spj210224` VALUES ('S5', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J4', '500');

三、 实验原理及内容

实验原理基于第10章、第11章的相关内容。

简单的前置知识

---

简单了解一下创建用户的SQL语句：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符 %。
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。

但是对我们本次实验来说只需要创建一个用户即可，因此命令就像下面这么简单。

CREATE USER 'U1210224';

TIPS：删除用户的SQL。

DROP USER 'U1210224';

---

然后再教大家一个很实用的指令：如何查看当前用户信息。

用户信息是存储在 mysql 数据库下的 user 表中的，我们只需要查询这个表的信息即可。但是这个表中的字段特别多，如果直接 SELECT * FROM mysql.user 有惊喜。。。。

我们只需要查看用户名，用以下语句即可：

SELECT user FROM mysql.user;

可以看见当前只有3个用户，都是 mysql 中默认创建的。

---

登录 MySQL 开始做实验：WIN + R调出运行，输入cmd，调出命令行。

mysql -u root -p
Enter password: 1234

1.创建用户U1和U2

了解了上面的知识以后，我们只需要创建一个用户，密码为空即可。

CREATE USER U1210224;
CREATE USER U2210224;

创建完以后可以查询一下当前用户：

SELECT user FROM mysql.user;

2. 对实验1的SPJ表，用授权机制完成以下存取控制

具体原理可以参见：【数据库系统设计】数据库安全性

(1) 使用户U1具有对供应商代码sno、供应数量qty的查询权限。

注意，要先进入SPJ数据库才能授权：

use SPJ210224;

使用户U1具有对供应商代码sno、供应数量qty的查询权限。

GRANT SELECT(sno, qty)
ON TABLE spj210224
TO U1210224;

(2) 使用户U1拥有对表SPJ的查询、删除权限和对供应数量qty的修改权限，并具备转授这些权限的权力。

GRANT SELECT, DELETE, UPDATE(qty)
ON TABLE spj210224
TO U1210224
WITH GRANT OPTION; #允许他再将此权限授予其他用户

(3) 用户U1授予用户U2对表SPJ的删除权限。

在（2）中我们的SQL语句使得U1具备转授权限的能力，来尝试一下。

首先要切换到 U1 用户：由于之前没有设置密码，直接登陆即可。
可以用 exit 退出当前用户，建议新打开一个命令行窗口，同时操作两个用户。

登录 U1用户：

mysql -u U1210224 # 这里不要加分号

进入 spj 数据库。

use SPJ210224;

用户U1授予用户U2对表SPJ的删除权限。

GRANT DELETE
ON TABLE spj210224
TO U2210224;

(4) 回收用户U1和用户U2对SPJ的删除权限。

重新登录回 root 用户：

mysql -u root -p1234

进入 spj 数据库。

use SPJ210224;

回收用户U1和用户U2对SPJ的删除权限。

REVOKE DELETE
ON TABLE spj210224
FROM U1210224, U2210224;

3. 观察多事务并发时的数据库保护

简单了解 autocommit

set autocommit 1|0;

1：mysql 默认为1，表示开启自动提交。
0：表示没有开启自动提交

如果没有开启自动提交，当前 user1 所连接的 mysql 的所有操作都会当成一个事务直到你输入rollback 或者 commit；当前事务才算结束。

当前事务结束前新的 mysql 连接时无法读取到任何 user1 的操作的结果的。

如果开起了，mysql 会把每个 sql 语句当成一个事务然后自动的 commit。

当然无论开启与否，start transaction commit|rollback 都是独立的事务。

(1)分别以root和自己的用户连接MySQL服务器，并分别输入命令SET AUTOCOMMIT=0;

以 root 连接 MySQL 服务器：

mysql -u root -p1234
########################
SET AUTOCOMMIT = 0;

以 U1 连接 MySQL 服务器：

mysql -u U1210224
########################
SET AUTOCOMMIT = 0;

(2)root将表SPJ中供应商s1所供应的零件数量(qty)都增加50，此即事务1。

UPDATE spj210224
SET qty = qty + 50;

(3)自己的用户将表SPJ中供应商s1所供应的零件数量(qty)都增加30，此即事务2。

# 进入数据库
use spj210224;

UPDATE spj210224
SET qty = qty + 30;

遇到问题了？不要慌，继续往下看。。。

(4)观察发生什么现象，分析为什么，找出解决办法。

两个用户分别查询 spj 表 的 qty 字段。

SELECT qty FROM spj210224;

很明显的发现，两个用户查询结果不同。

为什么：由于我们设置了 set autocommit 0;，因此数据库的事务不会自动提交，因此我们先执行事务1后，事务1会给 spj 表加 X锁，此时事务2若想对数据进行操作将处于等待状态。

解决方案：要么提交 commit、要么回滚 rollback：

• commit：意味着事务1将会提交，并且表中的数据成功 + 50；
• rollback：意味着事务1将会回滚，表中数据不会有任何变化。

具体看下面的（5）。

(5)root分别输入commit和rollback命令时，观察两个用户界面的信息及变化，并用select语句查看表SPJ中的qty属性值的变化，注意观察在root事务commit（rollback）前后以及自己的用户事务commit后qty属性的值的变化情况。

commimt

root 用户执行提交操作：

commit;

rollback

root 用户执行回滚操作：

rollback;

root 用户执行查询，可以发现 spj 的数据回滚了！！！

在 U1 用户执行查询的话，会发现数据从未变过。。。因为ROOT用户的事务1从未提交过，而是直接回滚，因此对U1用户来说，ROOT用户相当于没有执行过任何操作。