实验10 并发控制

营养快线好喝~

于 2024-07-17 09:45:14 发布

阅读量375

点赞数 17

分类专栏：数据库文章标签：数据库 java

本文链接：https://blog.csdn.net/weixin_46186903/article/details/140484680

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、实验目的

了解数据库系统中各类数据库事务的定义机制和基于锁的并发控制机制，掌握Mysql数据库系统的事务控制和加锁机制。

二、实验环境

操作系统：Microsoft Windows 7旗舰版（32&64位）/Linux。
硬件：容量足以满足MySQL 5.7（8.0）安装及后续实验的使用。
软件：数据库版本：MySQL 5.7（8.0）。

三、实验内容

创建一个Mysql数据库，命名为“test”。然后运行提供的“实验10(数据).txt”文件在test库内生成course2实验表和数据，完成下列实验内容。

1. 事务和并发控制实验

（1）定义三种模式的数据库事务

事务是由相关操作构成的一个完整的操作单元。两次连续成功的COMMIT或ROLLBACK之间的操作，称为一个事务。
对数据库所做的一系列修改，在修改过程中，暂时不写入数据库，而是缓存起来，用户在自己的终端可以预览变化，直到全部修改完成，并经过检查确认无误后，一次性提交并写入数据库，在提交之前，必要的话所做的修改都可以取消。提交之后，就不能撤销，提交成功后其他用户才可以通过查询浏览数据的变化。
事务的特点
ACID:原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。一个有效的事务处理系统必须满足相关标准。

原子性：一个事务必须被视为一个单独的内部“不可分”的工作单元，以确保整个事务要么全部执行，要么全部回滚。
一致性：数据库总是从一种一致性状态转换到另一种一致性状态。
隔离性：某个事务的结果只有在完成之后才对其他事务可见。
持久性：一旦一个事务提交，事务所做的数据改变将是永久的。

① 显式事务
显式事务，由用户指定，允许用户决定哪批工作必须成功完成，否则所有部分都不完成。操作包括start transaction（或begin）,rollback, commit。
我们先利用给定查询文件创建了一个innodb类型course2数据表。其主要结构如下：

CREATETABLE`course2`(
	`Cno`CHAR(3)NOTNULLCOMMENT'课程号',
	`Cname`VARCHAR(12)NULLDEFAULTNULLCOMMENT'课程名',
	`Lhour`INT(10)NULLDEFAULTNULLCOMMENT'先修课号',
	`Credit`INT(10)NULLDEFAULTNULLCOMMENT'学分',
	`Semester`VARCHAR(2)NULLDEFAULTNULLCOMMENT'季节',
	PRIMARYKEY(`Cno`)USINGBTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql默认采用autocommit模式运行。故为了创建显式事务，我们需要修改autocommit变量。先查看此变量的默认值，主要有两种方法：

show variables like 'autocommit';

MySQL默认autocommit=ON，意味着每个SQL语句都会自动提交，无需手动执行commit操作，但可能导致事务无法回滚，影响数据的一致性。

修改变量autocommit，禁止自动提交，并创建一个显式事务。（set @@autocommit=0;）

set autocommit=0;
select from course2;

执行第一条插入语句并设置第一个回滚点p1。

insert into course2 values('C06','abc',30,3,'a');
select from course2;

执行第二个插入语句并设置第二个回滚点p2。

insert into course2 values('C07','aaa',30,3,'b');
select from course2;
savepoint p2;

回滚到p1，则p2自动被丢弃。

	rollback to p1;
		select from course2;

回滚到原始点，即事务开始的点。发现操作都被回滚。显式事务执行成功。

rollback;
select from course2;

显式事务即事务没有自动提交，可以回滚到原始点，在rollback和commit之前对数据库的修改都可以挽回，而不是永久写入。

② 自动提交事务
mysql默认采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）表的语句之后，MySQL立刻把更新存储到磁盘中。默认隔离级别为不可重复读。
设置自动提交为ON。（set @@autocommit=1;）
再次按照①操作，发现rollback to p1出错，虽然设置保存点成功，但是实际上每一个语句已经自动提交了，也就是说已经永久写入了。所以事务无法回滚。

savepoint p1;
insert into course2 values('C07','aaa',30,3,'b');
select from course2;
rollback to p1;

③ 隐式事务
虽然我们设置自动提交为OFF，但是在事务中如果有create table，alter funciton，drop index等等语句，则隐含地结束一个事务，似乎是在执行本语句前，你已经进行了一个commit。
如下图所示，create table就隐含了一个事务的结束。

set autocommit=0;
show tables;
create table t(i INT) ENGINE=INNODB;
show tables;

正是这个隐式事务导致了回滚的失败。虽然rollback执行看似成功，但是实际却没能发挥作用。因为隐式事务已经无法挽回。

	rollback;
	show tables;

新建的t表依然存在！！！

（2）查看事务的锁信息和隔离级别

① 查看事务的锁信息
查看系统上表锁定争夺：

show status like 'table%';

查看系统上的行锁的争夺情况：

show status like 'innodb_row_lock%';

InnoDB以Oracle的风格，对行级进行锁定，并且默认运行查询作为非锁定持续读。
下面对innodb的行锁定做个小测试。

我们对innodb引擎的表A进行i=1的行锁定。

create table test(i INT)ENGINE=INNODB;
insert into test values(1);
set autocommit=0;
start transaction;
select from test where i=1 lock in share mode;

再另开一个登录会话B，并且在B中也开始一个事务，对i=1行进行操作。但是由于i=1行已经被锁定，所以只有先执行的A 端commit后，B端才能执行事务。当A未提交时，B只能等待，若是等待过久，则会超时。

use db;	--选择当前数据库（根据实际情况处理）
start transaction;
delete from test where i=1;

② 查看事务的隔离级别
查看innodb系统级别的全局或会话两类事务隔离级别：

-- MySQL5.x
SELECT @@global.tx_isolation;		--查询全局事务隔离级别
SELECT @@session.tx_isolation;		--查询会话事务隔离级别
SELECT @@tx_isolation;			--查询事务隔离级别，一般同会话级别

-- MySQL8.0
SELECT @@global.transaction_isolation;	--查询全局事务隔离级别
SELECT @@session.transaction_isolation;	--查询会话事务隔离级别
SELECT @@transaction_isolation;		--查询事务隔离级别，一般同会话级别

③ 了解并掌握事务的四种隔离级别
SQL标准定义了4类隔离级别。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销，但并发异常突出。

Read Uncommitted（RU，读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（RC，读取提交内容）：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（RR，可重读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
Serializable（可串行化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：
在这里插入图片描述

（3） MySQL隔离级别操作实践

原始数据：

CREATE TABLE IF NOT EXISTS `user` (
`userId` int(3) NOT NULL,
`userAge` int(3) default '0',
PRIMARY KEY (`userId`)
) ENGINE=InnoDB;
Insert into `user` Values(1,1),(2,2),(3,3);

初始状态：
①设置会话窗口：set autocommit=0;取消事物的自动提交！
②会话窗口内事务操作均采用start transaction/commit/rollback的手动形式！
操作分析：

READ-UNCOMMITTED (读取未提交内容)级别
①A修改事务级别并开始事务，对user表做一次查询

set session transaction isolation level read uncommitted;
select @@transaction_isolation;
start transaction;
select* from user;

② B更新一条记录

start transaction;
update user set userAge =10 where userId =3;
select* from user;

③此时B事务还未提交，A在事务内做一次查询，发现查询结果已经改变

select* from user;

④B进行事务回滚

rollback;
select* from user;

⑤A再做一次查询，查询结果又变回去了

select* from user;

⑥ A表对user表数据进行修改

update user set userAge =100 where userId =1;

⑦ B表重新开始事务后，对user表记录进行修改，修改被挂起，直至超时，但是对另一条数据的修改成功，说明A的修改对user表的数据行加行共享锁(因为可以使用select)

update user set userAge =111 where userId =1;
update user set userAge =111 where userId =2;

可以看出READ-UNCOMMITTED隔离级别，当两个事务同时进行时，即使事务没有提交，所做的修改也会对事务内的查询做出影响，这种级别显然很不安全。但是在表对某行进行修改时，会对该行加上行共享锁。

READ-COMMITTED（读取提交内容）
①设置A的事务隔离级别，并进入事务做一次查询

set session transaction isolation level read committed;
select @@transaction_isolation;
start transaction;
select* from user;

② B开始事务，并对记录进行修改

start transaction;
update user set userAge =10 where userId =3;
select* from user;

③A再对user表进行查询，发现记录没有受到影响

select* from user;

④ B提交事务

commit;
select* from user;

⑤ A再对user表查询，发现记录被修改

select* from user;

⑤ A对user表进行修改

update user set userAge =100 where userId =3;

⑦ B重新开始事务，并对user表同一条进行修改，发现修改被挂起，直到超时，但对另一条记录修改，却是成功，说明A的修改对user表加上了行共享锁(因为可以select)

start transaction;
update user set userAge =100 where userId =3;
update user set userAge =100 where userId =1;

READ-COMMITTED事务隔离级别，只有在事务提交后，才会对另一个事务产生影响，并且在对表进行修改时，会对表数据行加上行共享锁

REPEATABLE-READ (可重读)

① A设置事务隔离级别，进入事务后查询一次

set session transaction isolation level repeatable read;
select @@transaction_isolation;
start transaction;
select* from user;

② B开始事务，并对user表进行修改

start transaction;
select* from user;
update user set userAge =10 where userId =3;
select* from user;

③ A查看user表数据，数据未发生改变

select* from user;

④B提交事务

commit;
select* from user;

⑤ A再进行一次查询，结果还是没有变化

select* from user;

⑥ A提交事务后，再查看结果，结果已经更新

commit;
select* from user;

⑦A重新开始事务，并对user表进行修改

start transaction;
update user set userAge =100 where userId =3;

⑧ B表重新开始事务，并对user表进行修改，修改被挂起，直到超时，对另一条记录修改却成功，说明A对表进行修改时加了行共享锁(可以select)

update user set userAge =100 where userId =3;
update user set userAge =100 where userId =1;

REPEATABLE-READ事务隔离级别，当两个事务同时进行时，其中一个事务修改数据对另一个事务不会造成影响，即使修改的事务已经提交也不会对另一个事务造成影响。
　　在事务中对某条记录修改，会对记录加上行共享锁，直到事务结束才会释放。

- SERIERLIZED (可串行化)
①修改A的事务隔离级别，并作一次查询

set session transaction isolation level serializable;
select @@transaction_isolation;
start transaction;
select* from user;

② B对表进行查询，正常得出结果，可知对user表的查询是可以进行的

select* from user;

③ B开始事务，并对记录做修改，因为A事务未提交，所以B的修改处于等待状态，等待A事务结束，最后超时，说明A在对user表做查询操作后，对表加上了共享锁

start transaction;
update user set userAge =100 where userId =3;

SERIALIZABLE事务隔离级别最严厉，在进行查询时就会对表或行加上共享锁，其他事务对该表将只能进行读操作，而不能进行写操作。

营养快线好喝~

关注

17
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
实验10 并发控制

事务是由相关操作构成的一个完整的操作单元。两次连续成功的COMMIT或ROLLBACK之间的操作，称为一个事务。对数据库所做的一系列修改，在修改过程中，暂时不写入数据库，而是缓存起来，用户在自己的终端可以预览变化，直到全部修改完成，并经过检查确认无误后，一次性提交并写入数据库，在提交之前，必要的话所做的修改都可以取消。提交之后，就不能撤销，提交成功后其他用户才可以通过查询浏览数据的变化。事务的特点。
复制链接

扫一扫