实验11.2　隔离级别和锁的使用

实验11.2　隔离级别和锁的使用

【实验目的】

①理解MySQL的隔离级别。

②理解MySQL的锁机制。

③理解事务加锁的机制。

【实验内容】

①创建事务1和事务2，设置事务隔离级别为“READ-UNCOMMITTED”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

②创建事务1和事务2，设置事务隔离级别为“READ-COMMITTED”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

③创建事务1和事务2，设置事务隔离级别为“REPEATABLE-READ”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

④创建事务1和事务2，对事务设置表级别的S锁和X锁，比较事务1和事务2的执行结果。对事务1和事务2模拟死锁，查看死锁和解除锁。

【实验步骤】

（1）设置事务隔离级别为“READ⁃UNCOMMITTED”

创建事务1和事务2，设置事务隔离级别为“READ⁃UNCOMMITTED”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

①单击“WIN+R”键，输入“cmd”，回车即可打开命令提示符窗口；或者在屏幕左下方搜索框输入“cmd”，可以找到cmd，单击即可，如图11.8所示。

②输入“title 事务1”的命令，回车后，设置会话窗口名称为“事务1”。

③输入“mysql -uroot -p”的命令，然后回车，会提示输入密码，输入密码后即可连接MySQL，如图11.9所示。

④重复步骤②和③，建立名称为“事务2”的会话窗口，并连接MySQL，如图11.10所示。

⑤分别在两个会话窗口中，查看和修改隔离级别。输入下面的SQL语句并回车执行，查看当前设置的事务隔离级别。

show variables like 'transaction_isolation';

或者

select @@transaction_isolation;

然后输入下面SQL语句并回车执行，修改会话窗口的隔离级别为“读未提交”，如图11.11和图11.12所示。

set session transaction_isolation  = 'read-uncommitted';

⑥在事务1会话窗口，依次输入下面命令并执行：

use sales;
select * from products;
begin;
update products set price=1 where pid ='p01';
select * from products;

事务1修改了products表中p01的价格，但未提交，如图11.13所示。

⑦在事务2会话窗口，依次输入下面命令并执行：

use sales;
select * from products;

查询products表，发现产品p01的price为1，如图11.14所示，事务2读的是事务1未提交的数据，即事务2发生了脏读现象。

⑧再回到事务1会话窗口，输入下面命令并执行：

rollback;
select * from products;

事务1进行了回滚操作，然后再查询products表，发现p01的price又变成0.5，如图11.15所示。

（2）设置事务隔离级别为“READ⁃COMMITTED”

创建事务1和事务2，设置事务隔离级别为“READ⁃COMMITTED”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

①分别在两个会话窗口中，输入下面的SQL语句并回车执行，修改隔离级别为“读已提交”。

SET SESSION TRANSACTION_ISOLATION  = 'READ-COMMITTED';

②重复前一个实验的步骤⑥—⑧，即在事务1会话窗口，依次输入下面命令并执行：

use sales;
select * from products;
begin;
update products set price=1 where pid ='p01';
select * from products;

事务1修改了products表中p01的价格，但未提交，如图11.16所示。

然后在事务2会话窗口，依次输入下面命令并执行：

use sales;
select * from products;

查询products表，发现读出的products表的产品p01的price为0.5，如图11.17所示，事务2读的是事务1提交的数据，即事务2未发生脏读现象。

最后回到事务1会话窗口，输入下面命令并执行：

rollback;
select * from products;

事务1进行了回滚操作，然后再查询products表，发现p01的price又变成0.5，如图11.18所示。

（3）设置事务隔离级别为“REPEATABLE⁃READ”

创建事务1和事务2，设置事务隔离级别为“REPEATABLE⁃READ”。在事务1中执行更新语句，在事务2中执行查询语句，比较执行结果。

①分别在两个会话窗口中，输入下面的SQL语句并回车执行，修改隔离级别为“可重复读”，如图11.19和图11.20所示。

set session transaction_isolation  = 'repeatable-read';
select @@transaction_isolation;

②在事务1会话窗口，依次输入下面命令并执行：

use sales;
begin;
select * from products;
update products set price= price-0.5 where pid ='p06';
select * from products;

事务1先查询了products表，p06的price为2，然后修改了products表中p06的价格，但未提交，再查询products表的p06的price为1.5，如图11.21所示。

③在事务2会话窗口，依次输入下面命令并执行：

use sales;
begin;
select * from products;(https://www.daowen.com)

查询products表，发现产品p06的price仍然为2，如图11.22所示。

④再回到事务1会话窗口，输入下面命令并执行：

commit;
select * from products;

事务1进行了提交操作，然后再查询事务1的products，发现p06的price仍然为1.5，如图11.23所示。

⑤再回到事务2会话窗口，输入下面命令并执行：

select * from products;commit;select * from products;

事务2在事务1进行提交操作前，查询products表的p06的price是2，事务2在事务1进行提交操作后，再次查询products表，发现p06的price仍然是2，证明事务2的隔离级别是可重复读。事务2进行提交操作后，再次查询products表，这时候p06的price为1.5，如图11.24所示。

（4）对事务设置表级别的S锁和X锁

创建事务1和事务2，对事务设置表级别的S锁和X锁，比较事务1和事务2的执行结果。对事务1和事务2模拟死锁，查看死锁和解除锁。

①创建事务1会话窗口，并连接MySQL。依次输入下面命令并执行：

use sales;begin;lock tables agents read;select * from agents;update agents set percent=5 where aid='a01';select * from  orders;

对agents加表级别的读锁（S锁），加入S锁后可以查询agents表，但不能对agents表进行更新操作，也不能对其他表进行查询操作，因为其他表没有加锁，如图11.25所示。

②创建事务2会话窗口，并连接MySQL。依次输入下面命令并执行：

show open tables where in_use > 0;
use sales;
select * from agents;

在事务1没有通过“unlock tables”命令解锁表之前，输入SQL语句：“show open tables where in_use > 0”，显示加锁的表，然后设置当前数据库，并查询agents表，发现可以访问该表，说明不同事务会话窗口可以共享表的读锁，如图11.26所示。

③回到事务1会话窗口，依次输入下面命令并执行：

unlock tables;
lock tables agents write;
show open tables where in_use > 0;
update agents set percent=5 where aid='a01';
select * from  agents;
select * from  orders;

对于事务1，对agents 加表级别的X锁，加了X锁后可以对agents 表进行更新和读操作，但不能对其他表查询，因为其他表没有加锁，如图11.27所示。

④回到事务2会话窗口，依次输入下面命令并执行：

show open tables where in_use > 0;
select * from  agents;

在事务1没有通过“unlock tables”命令解锁表之前，显示加锁的agents 表，然后查询agents 表，发现不能访问该表，只能等待agents表解锁，如图11.28所示。

⑤回到事务1会话窗口，依次输入下面命令并执行：

unlock tables;
use sales;
select * from  products;
begin;
update products set price=1 where pid='p01';

事务1更新products表中的p01的price数值。

⑥回到事务2会话窗口，依次输入下面命令并执行：

use sales;
select * from  products;
begin;
update products set price=2 where pid='p02';

事务2更新products表中的p02的price数值。

⑦回到事务1会话窗口，输入下面命令并执行：

update products set price=1.5 where pid='p02';

事务1又更新products表中p02的price数值，事务1进入等待状态。

⑧回到事务2会话窗口，输入下面命令并执行：

update products set price=2 where pid='p01';

事务2更新products表中p01的price数值，事务1封锁了p01，又需要封锁p02，事务2封锁了p02，又需要封锁p01，这样就形成了死锁。当出现死锁以后，事务1直接进入等待，事务2检测到死锁，然后中断事务2后，事务1最后一条更新语句完成，耗时11.87秒，如图11.29和图11.30所示。