实验七:事务与并发控制

许安
734 阅读9分钟

实验七:事务与并发控制

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

DELIMITER $$
CREATE PROCEDURE Transfer_bank2campus(in sno VARCHAR(8), in amount int)
BEGIN
	declare bank_card_balance int default 0;
	select balance from icbc_card where icbc_card.sno = sno into @bank_card_balance;
	if @bank_card_balance >= amount then
		update icbc_card set icbc_card.balance =  icbc_card.balance - amount where icbc_card.sno = sno;
		update campus_card set campus_card.balance = campus_card.balance + amount where campus_card.sno = sno;
		commit;
	else
		rollback;
	end if;	
END$$
DELIMITER ;

执行：call Transfer_bank2campus('20200032', 200);

转账完成：

image-20211105223735383

image-20211105223755231

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

丢失修改

首先创建stu_test用户进行事务并发处理操作：

image-20211106000507612

在两个用户中分别进行并发事务编写：

root

begin;
	set @balance = (select balance from campus_card where sno = '20200033');
    set @balance = @balance + 50;
    update campus_card set balance = @balance where sno = '20200033';
    select @balance;
commit;

对campus_card中的balance加50。

stu_test

begin;
	set @balance = (select balance from campus_card where sno = '20200033');
	select sleep(5);
    set @balance = @balance - 50;
    update campus_card set balance = @balance where sno = '20200033';
    select @balance;
commit;

对campus_card中的balance减50，并等待5秒钟再进行提交。

执行结果为150，意味着stu_test修改丢失，否则应为100。

image-20211106000749058

读脏数据

设置全局隔离级别：READ UNCOMMITTED;

在两个用户中分别进行并发事务编写：

root

begin;
    update campus_card set balance = balance + 50 where sno = '20200033';
    select balance from campus_card where sno = '20200033';

    select sleep(5);
rollback;

对campus_card中的balance加50，等待5秒回滚。

stu_test

begin;
    select balance from campus_card where sno = '20200033';
commit;

查询相应balance

stu_test中查询到的结果为250，但实际balance仍为200，读到脏数据。

image-20211106120109101

image-20211106120122900

不可重复读

在两个用户中分别进行并发事务编写：

root

begin;
    select balance from campus_card where sno = '20200033';
    select sleep(5);
    
    select balance from campus_card where sno = '20200033';
commit;

查询balance后等待5秒，随后再次查询。

stu_test

begin;
    update campus_card set balance = balance + 50 where sno = '20200033';
    select balance from campus_card where sno = '20200033';
commit;

对campus_card中的balance加50。

root中两次铲鲟结果不一致：

image-20211106122929938

image-20211106122935733

即为不可重复读。

幻读（删除和插入）

删除

root

begin;
    select * from campus_card;
    select sleep(5);
    
    select * from campus_card;
commit;

查询全部信息后等待5秒，随后再次查询。

stu_test

begin;
    delete from campus_card where sno = '20200033';
commit;

删除记录

root两次查询结果不一致，删除幻读。

image-20211106123320756

image-20211106123329640

插入

root

begin;
    select * from campus_card;
    select sleep(5);
    
    select * from campus_card;
commit;

查询全部信息后等待5秒，随后再次查询。

stu_test

begin;
    insert into campus_card values('20200033', 100);
commit;

插入记录

root两次查询结果不一致，插入幻读。

image-20211106123611231

image-20211106123618192

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

解决丢失修改

设置全局隔离等级：READ COMMITTED

set global transaction isolation level READ COMMITTED;
set session transaction isolation level READ COMMITTED;

再次执行第二题相应代码，发现结果正常，则解决丢失修改问题。

解决读脏数据

设置全局隔离等级：READ COMMITTED

set global transaction isolation level READ COMMITTED;
set session transaction isolation level READ COMMITTED;

再次执行第二题相应代码，发现结果正常，则解决丢失修改问题。

解决不可重复读

设置全局隔离等级：REPEATABLE READ

set global transaction isolation level REPEATABLE READ;
set session transaction isolation level REPEATABLE READ;

再次执行第二题相应代码，两次读取结果一致，则解决丢失修改问题。

image-20211106125450803

image-20211106125457414

解决幻读

设置全局隔离等级：SERIALIZABLE

set global transaction isolation level SERIALIZABLE;
set session transaction isolation level SERIALIZABLE;

删除幻读

root中两次读取结果一致，但实际数据已经删除，问题解决。

image-20211106125656868

image-20211106125742194

image-20211106125754518

插入幻读

root中两次读取结果一致，但实际数据已经插入，问题解决。

image-20211106125853570

image-20211106125859586

image-20211106125911852

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

show engine innodb status (MySQL 8.0 or 5.7)

root

begin;
	select sleep(5);
    update campus_card set balance = balance + 1 where sno = '20200033';
commit;
SELECT * FROM icbc.campus_card;
show engine innodb status;

stu_test

begin;
    update campus_card set balance = balance + 2 where sno = '20200033';
commit;

从输出结果读出以下数据：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-06 12:47:30 0x1f9c
*** (1) TRANSACTION:
TRANSACTION 29815, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 21, OS thread handle 2084, query id 1231 localhost ::1 root updating
update campus_card set balance = @balance where sno = ''20200033''

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 122 page no 4 n bits 72 index PRIMARY of table `icbc`.`campus_card` trx id 29815 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 3230323030303333; asc 20200033;;
 1: len 6; hex 000000007475; asc     tu;;
 2: len 7; hex 020000011c1317; asc        ;;
 3: len 4; hex 80000064; asc    d;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 122 page no 4 n bits 72 index PRIMARY of table `icbc`.`campus_card` trx id 29815 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 3230323030303333; asc 20200033;;
 1: len 6; hex 000000007475; asc     tu;;
 2: len 7; hex 020000011c1317; asc        ;;
 3: len 4; hex 80000064; asc    d;;


*** (2) TRANSACTION:
TRANSACTION 29816, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 23, OS thread handle 2608, query id 1233 localhost 127.0.0.1 stu_test updating
update campus_card set balance = @balance where sno = ''20200033''

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 122 page no 4 n bits 72 index PRIMARY of table `icbc`.`campus_card` trx id 29816 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 3230323030303333; asc 20200033;;
 1: len 6; hex 000000007475; asc     tu;;
 2: len 7; hex 020000011c1317; asc        ;;
 3: len 4; hex 80000064; asc    d;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 122 page no 4 n bits 72 index PRIMARY of table `icbc`.`campus_card` trx id 29816 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 3230323030303333; asc 20200033;;
 1: len 6; hex 000000007475; asc     tu;;
 2: len 7; hex 020000011c1317; asc        ;;
 3: len 4; hex 80000064; asc    d;;

*** WE ROLL BACK TRANSACTION (2)

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)

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

root

begin;
	select * from icbc_card FOR update;
	select sleep(5);
	select * from campus_card FOR update;
commit;

stu_test

begin;
	select * from campus_card FOR update;
	select sleep(5);
	select * from icbc_card FOR update;
commit;

先执行root，再执行stu_test，结果stu_test出现死锁现象：

image-20211106140707835

Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction

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

begin;
	update campus_card set balance = 0 where sno = '20200033';
    savepoint s;
    update campus_card set balance = 50 where sno = '20200033';
    
    select * from campus_card where sno = '20200033';
    rollback to s;
    select * from campus_card where sno = '20200033';
commit;

创建事务，在两次update之间加入savepoint，之后进行两次select，并插入rollback。

image-20211106141453352

image-20211106141458988

可以看到第一次查询得到了最后一次更新的结果，而第二次查询仅得到第一次结果，正是因为savepoint的使用导致了这样的结果。

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

查询日志

Set global general_log = on;
Show variables like '%general_log%';

打开并显示查询日志，结果：

image-20211106142103212

在DESKTOP-BHMVJNB.log文件中可以看到以下记录：

Time                 Id Command    Argument
2021-11-06T06:19:17.686023Z	   25 Query	Set global general_log = on
2021-11-06T06:19:17.687831Z	   25 Query	Show variables like '%general_log%'
2021-11-06T06:20:59.421916Z	   25 Query	Set global general_log = on
2021-11-06T06:20:59.423816Z	   25 Query	Show variables like '%general_log%'

错误日志

在DESKTOP-BHMVJNB.err文件中可以看到类似以下记录：

2021-11-06T02:56:15.102159Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.21'  socket: ''  port: 3306  MySQL Community Server - GPL.

image-20211106144014457

慢查询日志

在DESKTOP-BHMVJNB-slow.log文件中可以看到类似以下记录：

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.21 (MySQL Community Server - GPL). started with:
TCP Port: 0, Named Pipe: MySQL
Time                 Id Command    Argument

​ image-20211106144137899

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的数据。

1 创建新的binlog文件

flush logs;
show master status;

image-20211106145517470

2 执行创建代码

image-20211106145631186

3 查看binlog

show binlog events in 'DESKTOP-BHMVJNB-bin.000046';

查看导出结果

image-20211106150510899

4 导出sql语句

mysqlbinlog --base64-output=decode-rows -v --start-position=156 --stop-position=2336 DESKTOP-BHMVJNB-bin.000046>D:\test.sql

image-20211106154045947

得到sql文件image-20211106154827049

执行后数据恢复。

image-20211106161235368image-20211106161258479

实验中出现的问题及解决方案

1

进行第二题时，我在同一用户下进行了两个事务的修改操作，导致总是无法实现丢失修改。上网查阅大量资料后发现应该使用两个不同的用户进行事务并发操作才能够实现丢失修改。

于是创建新的用户连接后实验成功！

image-20211106000420685

2

进行第二题读脏数据时，发现两个事务并不能产生读脏数据的效果，更改了事务隔离级别后也没有效果。后来想到可能是由于更改未生效，因此重新连接后再次尝试，实验成功。

avatar
文章
阅读
粉丝